Because SQLite is a relational database, you can specify relationships between objects. While most object-relational mapping libraries allow entity objects to refer to each other, Room explicitly forbids this use. See Understand Why Room Doesn’t Allow Object References for the technical reasons behind this.

Define a one-to-many association

While you can’t use associations directly in Room, it does allow you to define foreign key constraints between entities. For example, if another entity is called Book, you can use the @foreignKey annotation to define its relationship to the User entity

@Entity(foreignKeys = arrayOf(ForeignKey(
            entity = User::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("user_id")) ) ) data class Book( @PrimaryKey val bookId: Int, val title: String? , @ColumnInfo(name ="user_id") val userId: Int
)
Copy the code

Here, User and Book are modeled one-to-many, with the user_id foreign key used to establish an association between the two objects.

Foreign keys are very powerful and can help you when the entity you care about changes. For example, when you set onDelete = CASCADE in the @foreignKey annotation, it will tell SQLite to delete the Book to which the User belongs when a User is deleted.

Note:SQLite breaks @INSERT (onConflict = REPLACE) into REMOVE and REPLACE instead of a single UPDATE operation. This way of replacing conflicting values can affect your foreign key constraints. See SQLite Documentation for more details

Create nested objects

Sometimes, entities also contain nested entities inside them, and if you want to treat them as a cohesive whole in your database, you can use the @Embedded annotation to decompose the entities into fields in a data table. You then query the embedded field just like any other field.

For example, the User class can contain a member variable of type Address, which can be a field combination for Street, City,state, and postCode. To store these combined fields separately in a table, use the @Embedded annotation on the Address member variable of the User class.

data class Address( val street: String? , val state: String? , val city: String? , @ColumnInfo(name ="post_code") val postCode: Int ) @Entity data class User( @PrimaryKey val id: Int, val firstName: String? , @Embedded val address: Address? )Copy the code

The User table contains the following fields: ID, firstName, street, state, city, and post_code.

Note: @embedded member variables can also contain @embedded annotations inside, that is, @embedded is nested.

If multiple nested entities in an entity have member variables with the same name, you can use the prefix attribute of the @Embedded annotation to ensure that the fields are unique. Room prefixes each field of the nested entity.

Define many-to-many associations

This is another type of relationship that needs to be built frequently in relational databases. A many-to-many relationship between two entities, where an instance of any entity can correspond to zero to multiple instances of another entity. For example, users need to create playlists in a music streaming app. Each list can contain multiple songs, and each song can be included in multiple playlists.

To build this relationship, you need to create three objects:

  1. Entities corresponding to playlists
  2. The entity corresponding to the song
  3. Middle class, which holds which songs are included in which playlist
@Entity data class Playlist( @PrimaryKey var id: Int, val name: String? , val description: String? ) @Entity data class Song( @PrimaryKey var id: Int, val songName: String? , val artistName: String? )Copy the code

Then, define the middle class as an entity containing foreign key references to Song and Playlist.

@Entity(tableName = "playlist_song_join",
        primaryKeys = arrayOf("playlistId"."songId"),
        foreignKeys = arrayOf(
                         ForeignKey(entity = Playlist::class,
                                    parentColumns = arrayOf("id"),
                                    childColumns = arrayOf("playlistId")),
                         ForeignKey(entity = Song::class,
                                    parentColumns = arrayOf("id"),
                                    childColumns = arrayOf("songId"))
                              )
        )
data class PlaylistSongJoin(
    val playlistId: Int,
    val songId: Int
)
Copy the code

This results in a many-to-many relationship model that allows you to use daOs to query, either through song to query the Playlist; You can also use playlist to query songs

@Dao
interface PlaylistSongJoinDao {
    @Insert
    fun insert(playlistSongJoin: PlaylistSongJoin)

    @Query(""" SELECT * FROM playlist INNER JOIN playlist_song_join ON playlist.id=playlist_song_join.playlistId WHERE playlist_song_join.songId=:songId """)
    fun getPlaylistsForSong(songId: Int): Array<Playlist>

    @Query(""" SELECT * FROM song INNER JOIN playlist_song_join ON song.id=playlist_song_join.songId WHERE playlist_song_join.playlistId=:playlistId """)
    fun getSongsForPlaylist(playlistId: Int): Array<Song>
}
Copy the code

0. Overview

1. Use Room entities to define data

2. Define relationships between objects

3. Create a view in the database

4. Use Room DAOs to access data

5. Migrate the database

6. Test the database

7. Reference complex data using Room