An important part of database design is to divide data into related tables and pull data together in meaningful ways.
With Room 2.2 database, we will support all possible relationships between tables: one-to-one, one-to-many and many-to-many, with an annotation: Relation .
One-to-one relationship
An easy example in this case is as follows: a person may own a dog and a dog may have only one owner. This is a one-to-one relationship.
To show this relationship in the database, we create two tables: Dog and Owner , where Dog table has reference to owner id , Owner table has reference to dog id .
In Room, we create 2 models as follows:
1 2 3 4 5 6 7 8 9 10 11 12 | @Entity data class Dog( @PrimaryKey val dogId: Long, val dogOwnerId: Long, val name: String, val cuteness: Int, val barkVolume: Int, val breed: String ) @Entity data class Owner(@PrimaryKey val ownerId: Long, val name: String) |
To display all the dogs and their owners on the screen, we create a DogAndOwner class as follows:
1 2 3 4 5 | data class DogAndOwner( val owner: Owner, val dog: Dog ) |
To query using SQLite, we need to do two things:
- Run 2 queries: one is to retrieve all owners, the other is to retrieve all dogs based on owner ids.
- Handling object mapping.
1 2 3 4 | SELECT * FROM Owner SELECT * FROM Dog WHERE dogOwnerId IN (ownerId1, ownerId2, …) |
To query List <DogAndOwner> using Room, we do not need to do the above two things, instead, we only need to use the Relation annotation
In this example, when Dog has owner information, we add the Relation annotation to the Dog variable, specifying that the ownerId column corresponds to dogOwnerId :
1 2 3 4 5 6 7 8 9 | data class DogAndOwner( @Embedded val owner: Owner, @Relation( parentColumn = "ownerId", entityColumn = "dogOwnerId" ) val dog: Dog ) |
The DAO query class will look like this:
1 2 3 4 | @Transaction @Query("SELECT * FROM Owner") fun getDogsAndOwners(): List<DogAndOwner> |
One-to-many relationship
Returning to the example mentioned, we now have a one-to-many relationship between Dog and Owner. The model of the database we defined earlier has not changed.
Now, to display the list of owners with dogs, we need to create a new class like this:
1 2 3 4 5 | data class OwnerWithDogs( val owner: Owner, val dogs: List<Dog> ) |
To avoid running two separate queries, we can define a one-to-many relationship between Dog and Owner, using the Relation annotation of List <Dog>:
1 2 3 4 5 6 7 8 9 | data class OwnerWithDogs( @Embedded val owner: Owner, @Relation( parentColumn = "ownerId", entityColumn = "dogOwnerId" ) val dogs: List<Dog> ) |
The DAO class will look like this:
1 2 3 4 | @Transaction @Query("SELECT * FROM Owner") fun getDogsAndOwners(): List<OwnerWithDogs> |
Many-to-many relationships
Now, let’s consider the case where one owner may own multiple dogs, and one dog may have multiple owners. To show this relationship, the Dog table and the Owner table we defined earlier are not enough.
A dog can have many owners, we need to have multiple dog ids, corresponding to different owner ids. Since dogId is the primary key in the Dog table, we cannot insert multiple dogs with the same id.
To solve this problem, we need to create a combined table (also known as a cross reference table) holding pairs (dogId, ownId):
1 2 3 4 5 6 | @Entity(primaryKeys = ["dogId", "ownerId"]) data class DogOwnerCrossRef( val dogId: Long, val ownerId: Long ) |
If we want to get all the owners and dogs: List <OwnerWithDogs>, using only SQLite query, we need to write 2 queries: one takes all owners and one joins the Dog and DogOwnerCrossRef tables:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT * FROM Owner SELECT Dog.dogId AS dogId, Dog.dogOwnerId AS dogOwnerId, Dog.name AS name, _junction.ownerId FROM DogOwnerCrossRef AS _junction INNER JOIN Dog ON (_junction.dogId = Dog.dogId) WHERE _junction.ownerId IN (ownerId1, ownerId2, …) |
To do this in Room, we need to update the OwnerWithDogs class and tell Room that to get Dog data, it needs to use the DogOwnerCrossRef linked table:
1 2 3 4 5 6 7 8 9 10 | data class OwnerWithDogs( @Embedded val owner: Owner, @Relation( parentColumn = "ownerId", entityColumn = "dogId", associateBy = Junction(DogOwnerCrossRef::class) ) val dogs: List<Dog> ) |
In the DAO class, we need to select from Owners and return the correct class:
1 2 3 4 | @Transaction @Query("SELECT * FROM Owner") fun getOwnersWithDogs(): List<OwnerWithDogs> |