Query 1:many relation in Android Room

user_11477844 0
Viewed 49

I am using Room in my Android App (Java) and there I have two entities with a 1:many relation.

Lens entity One lens can have multiple wears.

@Entity(tableName = "lens_table")
public class Lens {

    @PrimaryKey(autoGenerate = true)
    private int lensId;
    private String name;
}

Wear entity One wear can only relate to one lens.

@Entity(tableName = "wear_table",
        foreignKeys = {@ForeignKey(
                entity = Lens.class,
                parentColumns = "lensId",
                childColumns = "fk_lensId",
                onDelete = ForeignKey.CASCADE)},
        indices = {@Index("fk_lensId")})
public class Wear {

    @PrimaryKey(autoGenerate = true)
    private int wearId;
    private String name;
    private int fk_lensId;
}

So far so good. I am fine with the "standard" queries (create, get all, update, delete,...) so far, where a lot of documentation is around. I also was successful implementing the query to get all lenses with their wears based on below relation.

public class LensWithWears {

    @Embedded
    public Lens lens;

    @Relation(
            parentColumn = "lensId",
            entityColumn = "fk_lensId"
    )
    public List<Wear> wears;
}

But now I need to query the following information:

Get a single wear with the associated lens by looking up the wearId

The relationship class I am currently using looks like following:

public class WearWithLens {
    @Embedded
    public Wear wear;

    @Relation(
            parentColumn = "wearId",
            entityColumn = "lensId"
    )
    public Lens lens;
}

And the Dao Query looks like that:

@Query("SELECT * FROM wear_table WHERE wearId = :wearId LIMIT 1")
LiveData<WearWithLens> getWearWithLensByWearId(int wearId);

My code obviously does not work, otherwise I would not ask... The problem is, that an object WearWithLens is returned, but the lens object in it is always null.

In other words, I would like to query a Wear, which has a 1:1 relation to a lens and get both objects together in the class WearWithLens.

Can somebody tell me how the query should look like?

Thanks!

android-room foreign-keys sqlite android java
1 Answers

The parent column needs to be the column that forms the relationship between the two. That is it should be the fk_lensId column.

So :-

public class WearWithLens {
    @Embedded
    public Wear wear;

    @Relation(
            parentColumn = "fk_lensId",
            entityColumn = "lensId"
    )
    public Lens lens;
}

As an example

  • not using LiveData<WearWithLens> getWearWithLensByWearId(int wearId); but instead WearWithLens getWearWithLensByWearId(int wearId); for convenience and brevity.
  • using your classes/entities with getters and setters added and also additional constructors to reduce the coding
  • and obviously the @Relationship as above.

With the following :-

    db = TheDatabase.getInstance(this);
    dao = db.getAllDao();

    int l1id = (int) dao.insert(new Lens("Lens1"));
    int l2id = (int) dao.insert(new Lens("Lens2"));
    int l3id = (int) dao.insert(new Lens("Lens3"));

    dao.insert(new Wear("Wear1 child of Lens1",l1id));
    dao.insert(new Wear("Wear2 child of Lens1",l1id));
    dao.insert(new Wear("Wear3 child of Lens1",l1id));
    dao.insert(new Wear("Wear4 child of Lens2",l2id));
    dao.insert(new Wear("Wear5 child of Lens2",l2id));
    dao.insert(new Wear("Wear6 child of Lens2",l2id));
    dao.insert(new Wear("Wear7 child of Lens3",l3id));

    for (Wear wear: dao.getAllWears()) {
        WearWithLens currentWearWithLens = dao.getWearWithLensByWearId(wear.getWearId());
        Log.d("DBINFO","Current Wear is " + currentWearWithLens.wear.getName() + " parent Lens is " + currentWearWithLens.lens.getName());
    }

The Result is:-

2021-12-09 06:34:58.105 D/DBINFO: Current Wear is Wear1 child of Lens1 parent Lens is Lens1
2021-12-09 06:34:58.110 D/DBINFO: Current Wear is Wear2 child of Lens1 parent Lens is Lens1
2021-12-09 06:34:58.112 D/DBINFO: Current Wear is Wear3 child of Lens1 parent Lens is Lens1
2021-12-09 06:34:58.114 D/DBINFO: Current Wear is Wear4 child of Lens2 parent Lens is Lens2
2021-12-09 06:34:58.115 D/DBINFO: Current Wear is Wear5 child of Lens2 parent Lens is Lens2
2021-12-09 06:34:58.116 D/DBINFO: Current Wear is Wear6 child of Lens2 parent Lens is Lens2
2021-12-09 06:34:58.120 D/DBINFO: Current Wear is Wear7 child of Lens3 parent Lens is Lens3
user_47445140
Related