By Florina Muntenescu at medium.com/androiddeve… Translator: Bingxin said

Recently, I was working on an Rss reader, using Room to store feeds and articles in it. This is a typical one-to-many relationship. Learn more about the use of @relation annotations in this article.

Breaking down data into related tables and grouping it together in a meaningful way is an important part of designing a relational database. Starting with Room 2.2 (now stable), with the @relation annotation, we support all possible relationships between tables: one-to-one, one-to-many, many-to-many.

One to one

If we lived in a world where each person could only have one dog, and each dog could only have one owner. That’s a one-to-one relationship. To represent this relationship in a relational database, we create two tables, Dog and Owner. The OWNER table holds a reference to the owner ID, and the OWNER table holds a reference to the Dog ID. In Room, we create two entity classes:

@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)
Copy the code

To display all the dogs and their owners on the page, we created a data class called DogAndOwner:

data class DogAndOwner(
    val owner: Owner,
    val dog: Dog
)
Copy the code

To complete this query with Sqlite, we need:

  1. Query all dogs based on their owner ID, then query all dogs based on their owner ID
  2. Working with object maps
SELECT * FROM Owner
SELECT * FROM Dog
    WHERE dogOwnerId IN(ownerId1 ownerId2,...).Copy the code

To get List

from Room, we don’t need to implement double query and object mapping ourselves, just via @relation annotation.

In the example above, since Dog has owner information, add @relation to the Dog variable: specify that the ownerId in the owner table corresponds to the dogOwnerId in the Dog table.

data class DogAndOwner(
    @Embedded val owner: Owner,
    @Relation(
         parentColumn = "ownerId",
         entityColumn = "dogOwnerId"
    )
    val dog: Dog
)
Copy the code

Dao can be simplified as follows:

@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(a): List<DogAndOwner>
Copy the code

Note: Since Room will automatically perform these two queries for us in the background, add the @Transaction annotation to ensure atomicity.

More than a pair of

Suppose an owner could have more than one dog (Yeah!). The relationship between Owner and Dog is one-to-many. There is no need for any changes to the previously defined database structure, and we still use the previous table because the associated keys are already in the table.

Now, to show the owner and his dogs, we need to create a new data class:

data class OwnerWithDogs(
    val owner: Owner,
    val dogs: List<Dog>
)
Copy the code

To avoid double queries, we add the @relation annotation to List

to define a one-to-many relationship between Dog and Owner.

data class OwnerWithDogs(
     @Embedded val owner: Owner,
     @Relation(
          parentColumn = "ownerId",
          entityColumn = "dogOwnerId"
     )
     val dogs: List<Dog>
)
Copy the code

The Dao looks like this.

@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(a): List<OwnerWithDogs>
Copy the code

Many to many

Now suppose we lived in a perfect world where each owner could have multiple dogs, and each dog could have multiple owners. To model this relationship, the Dog and Owner tables are not sufficient. Because a dog can have multiple owners, the same dogId may require multiple pieces of data to match different owners. However, in the Dog table, dogId is the primary key, and we cannot insert multiple dogs with the same ID and different owners. To solve this problem, we need to create an additional associated table (also known as a cross-reference table) that stores (dogId,ownerId).

@Entity(primaryKeys = ["dogId"."ownerId"])
data class DogOwnerCrossRef(
    val dogId: Long.val ownerId: Long
)
Copy the code

Suppose we now only get all owners and their dogs through Sqlite: List

, we need two queries: get all owners, query Dog table and DogOwnerCrossRef table.

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)
Copy the code

With Room, we need to update the OwnerWithDogs instance class to tell Room to associate the table DogOwnerCrossRef in order to get all the corresponding dogs. Refer to tables through Junction.

In the Dao, the correct data class is returned by querying the Owner.

@Transactionhttps://youtu.be/_aJsh6P00c0
@Query("SELECT * FROM Owner")
fun getOwnersWithDogs(a): List<OwnerWithDogs>
Copy the code

Advanced Usage Examples

When using the @relation annotation, Room deduces which entity class to use based on the type of attribute being annotated. For example, so far we have annotated Dog or List

, which tells Room which class to use and which fields to query for.

If we want to return another object, such as Pup, which is not an entity but contains some fields. We can specify the entity to use via the @relation annotation.

data class Pup(
     val name: String,
     val cuteness: Int = 11
)
data class OwnerWithPups(
     @Embedded val owner: Owner,
     @Relation(
          parentColumn = "ownerId",
          entity = Dog::class,
          entityColumn = "dogOwnerId"
     )
     val dogs: List<Pup>
)
Copy the code

If we point to a specified field that returns an entity class, we need to specify this via the @Relation annotation’s projection attribute. For example, we point to get the names of all the dogs in OwnerWithDogs, so we need to return List

. However, Room cannot infer whether these strings represent names or varieties, so we need to specify them by projection.

data class OwnerWithDogs(
     @Embedded val owner: Owner,
     @Relation(
           parentColumn = "ownerId",
           entity = Dog::class,
           entityColumn = "dogOwnerId",
           projection = ["name"])
     val dogNames: List<String>
)
Copy the code

If you want to define a more strict relationship between dogOwnerId and ownerId, independent of any relationship you create, you can add a ForeignKey constraint between these fields. Remember that SQLite foreign keys define indexes and can have cascading triggers to update or delete entries in a table. Therefore, decide whether to use foreign keys based on whether you want to use this functionality in your database.

Whether you need one-to-one, one-to-many, or many-to-many support, Room can accommodate you with @relation annotations.

The first wechat official account: Bingxin said TM, focusing on Kotlin, Java, Android original knowledge sharing, AOSP source code analysis.

More latest original articles, scan code to pay attention to me!