# Room Persistence Library Introduction – part 3 *This article is the third and the last part of the three-part series that will smoothly introduce****Room Persistence Library****to you. In this part, I will show you how to add relations between tables/entities and how to query them properly.* Part 1 can be found [here](https://michalkonkel.dev/room-persistence-library-introduction-part-1) *– it was focused on configuring the project and explaining the basic structures.  Part 2 can be found* [*here*](https://michalkonkel.dev/room-persistence-library-introduction-part-2) *– it was focused on*`@Embedded`*entities and*`@TypeConverters`***.*** All sources can be found in related [***GitHub***](https://github.com/mkonkel/RoomBeginners) project. ### **The Room doesn’t allow object references** Because **SQLite** is a relational database, you can specify relationships between objects. Even though most object-relational mapping libraries allow entity objects to reference each other, Room explicitly forbids this. Working with **Room** relations is a bit tricky. Common ORMs allow you to use object references and thus ORMs implement **lazy loading** which was considered as potentially dangerous in Android apps. UI needs approximately **~16ms** to calculate and draw an updated Activity layout – so if you add to this a lazy loading call for some entity field that can take ~5ms you can run of out time to draw an updated frame for Activity. For example, let\`s assume that we can add object reference ```kotlin @Entity data class Book( @Id var id: Long, var title: String var authorId: Long, @ToOne(joinProperty="authorId") var author: Author ) ``` and ```kotlin @Entity data class Author( @Id var id: Long, var name: String ) ``` In such construction and lazy loading, Book will use ***getAuthor()*** method to return the author – the first call of this method will query DB for the corresponding **Author**. Now if you try to set a text on the **TextView** and you need an authors name you will write something like this: ```kotlin textView.text = book.getAuthor().getName() ``` This will invoke another query to get the author’s name – on the **main thread**! This can end up with glitches in your app UI. So, on the other hand instead of lazy loading we can use eager loading and load all necessary data earlier – but this will cause the overloading of the data and we will load something that isn’t needed in current point of the app. **To sum this up:** We can use **Lazy Loading** that will allow our app to query faster, load fewer data and improve the performance – but we can struggle with querying on UI thread and risk the glitches. We can also use Eager loading where we lose all the performance gain, and we will load big amounts of useless data. The Room comes across with something that mixes these two approaches. To reference multiple entities at the same time we need to create a **POJO** that contains each entity and write a query that will join corresponding tables. Even though you cannot use direct relationships, Room still allows you to define Foreign Key constraints between entities. Let’s add some code! We will add **Book** entity. ```kotlin @Entity(tableName = "books", foreignKeys = [ForeignKey( entity = User::class, parentColumns = ["id"], childColumns = ["user_id"], onDelete = ForeignKey.NO_ACTION )] ) data class Book( @PrimaryKey(autoGenerate = true) var id: Long, @ColumnInfo(name = "user_id") var userId: Long?, var title: String, @Embedded(prefix = "author_") var author: Author ) { data class Author( var firstName: String, var lastName: String ) } ``` Even though you cannot use direct relationships, Room still allows you to define **ForeignKey**constraints between entities. Using the **ForeingKey** allows you to decide what should happen with the entity when the corresponding parent is deleted. Don’t forget to add a migration to the DB (or you can just remove the existing app from the device or emulator and run the project again!) ```kotlin val MIGRATION_2_3: Migration = object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL( "CREATE TABLE 'books' ('id' INTEGER NOT NULL, 'user_id' INTEGER, 'title' TEXT, 'author_firstName' TEXT, 'author_lastName' TEXT, FOREIGN KEY('user_id') REFERENCES users('id'), PRIMARY KEY('id'))" ) } } ``` The database: ```kotlin @Database( entities = [User::class, Book::class], //... const val DB_VERSION = 3 //... .addMigrations(Migrations.MIGRATION_2_3) //... ``` Don\`t forget to create corresponding **DAO** and a prepopulating object. ### **One-to-many relation** First of all, let’s focus on one-to-many relation – this will require a special **POJO** (because Room doesn’t allow object relations) – so we should create the UserWithAllBooks class and simple query. Let\`s start with a **POJO**: ```kotlin class UserWithAllBooks { @Embedded lateinit var user: User @Transaction @Relation(parentColumn = "id", entityColumn = "user_id", entity = Book::class) lateinit var books: List<Book> } ``` The class above will use a @Relation annotation which can be used in a **POJO** to fetch relation entities automatically. When the POJO is returned from a query, all of its relations are also fetched by Room. This annotation must be a List or Set. `@Transaction` annotation should be used in two cases: * If the query is fairly big, it is better to run it inside a transaction to receive a consistent result. Otherwise, the query result does not fit into a single CursorWindow. When you use @Relation annotation in POJO class because then all fields are queried separately, so when you want to receive consistent results between these queries it’s a good idea to do them in the transaction. * If the result of the query is a POJO with Relation fields, these fields are queried separately. To receive consistent results between these queries, you probably want to run them in a single transaction. The UserWithAllBooks is just a common POJO but all of the fields are fetched from the entity defined in the `@Relation` (**Book**). The `@Embedded` annotation will give us a direct access to the fields of the embedded data type (**User**). Usage of `@Embedded` annotation is dictated by Kotlin. `@Relation` annotation can be used only on **POJO** classes – that means that you can\`t use it with `@Entity` (it’s a room design decision) – so to build a relation as above you can just simply extend the desired entity so it can look like this: ```kotlin class UserWithAllBooksJava : User() { @Relation(parentColumn = "id", entityColumn = "user_id", entity = Book::class) lateinit var books: List<Book> } ``` But in **Kotlin** you can’t inherit from data class – so usage of `@Embedded` annotation is a Kotlin workaround for `@Relation` limitation workaround… Now let’s construct a proper query. The Room allows you to pass parameters into queries to perform filtering operations, such as finding a user by Id or displaying only users live in a certain city, it is as simple as that: ```sql @Query("SELECT * FROM users WHERE id = :userId LIMIT 1") fun getUserWithBooksById(userId: Long): UserWithAllBooks ``` When this query is processed at compile time, Room matches the :***userId*** bind parameter with the userId method parameter. Room performs the match using the parameter names. If there is a mismatch, an error occurs as your app compiles. The keyword LIMIT will assure us that we will get only one user in this query. ## **One-to-one relation** Now we will add **Category** entity and create **one-to-one** relation – with an assumption that one book can have only one category. The process will be much similar to the one-to-many relation but instead of a List, we will add a relation to the single object. This is a rather rare DB relation type, but still, you might need it. ```kotlin @Entity(tableName = "categories") data class Category( @PrimaryKey(autoGenerate = true) var id: Long, @TypeConverters(CategoryNameTypeConverter::class) var name: Name ) { enum class Name(val code: Int) { COMEDY(0), DRAMA(1), FANTASY(2), ROMANCE(3), SCIENCE(4), UNKNOWN(-1) } } class CategoryNameTypeConverter { @TypeConverter fun fromInteger(value: Int): Category.Name { return when (value) { 0 -> Category.Name.COMEDY 1 -> Category.Name.DRAMA 2 -> Category.Name.FANTASY 3 -> Category.Name.ROMANCE 4 -> Category.Name.SCIENCE else -> Category.Name.UNKNOWN } } @TypeConverter fun statusToInteger(category: Category.Name): Int { return category.code } } ``` We also need to add another ForeignKey to Book entity: ```kotlin ForeignKey( entity = Category::class, parentColumns = arrayOf("id"), childColumns = arrayOf("category_id"), onDelete = ForeignKey.NO_ACTION ) //… @ColumnInfo(name = "category_id") var categoryId: Long?, ``` And a migration ```kotlin val MIGRATION_3_4: Migration = object : Migration(3, 4) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL( "CREATE TABLE 'categories' ('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'name' INTEGER NOT NULL)") database.execSQL("INSERT INTO categories (name) VALUES (0),(1),(2),(3),(4),(5),(-1)") //Because of https://www.sqlite.org/omitted.html database.execSQL( "CREATE TABLE 'books_new' ('id' INTEGER NOT NULL, 'user_id' INTEGER, 'category_id' INTEGER, 'title' TEXT, 'author_firstName' TEXT, 'author_lastName' TEXT, FOREIGN KEY('user_id') REFERENCES users('id'), FOREIGN KEY('category_id') REFERENCES categories('id'), PRIMARY KEY('id'))") database.execSQL("INSERT INTO books_new(id, user_id, category_id, title, author_firstName, author_lastName) SELECT id, user_id, -1, title, author_firstName, author_lastName FROM books") database.execSQL("DROP TABLE books") database.execSQL("ALTER TABLE books_new RENAME TO books") } } ``` We also need to add a POJO class for catching the query result: ```kotlin class BookWithCategory { @Embedded lateinit var book: Book @ColumnInfo(name = "name") @TypeConverters(CategoryNameTypeConverter::class) lateinit var category: Category.Name } ``` As you can see now we are not using the `@Relation` annotation, because this is a **one-to-one**relation and mentioned annotation can be applied only to List or Set. As you can easily notice we are using `@ColumnInfo` on non-entity class – this is for our convenience – without this annotation, Room wouldn’t know which column from returned query it should use, also thanks to this we can easily change the returning values names. We also need to use `@TypeConverter` with proper class. We can avoid adding it here when we move this converter to Database level, not Entity or POJO. Used query: ```kotlin @Query("SELECT * FROM books JOIN categories ON books.category_id = categories.id WHERE books.id = :bookID") fun getBookWithCategory(bookID: Long): BookWithCategory @Query("SELECT * FROM books JOIN categories ON books.category_id = categories.id") fun setBooksWithCategories(): List<BookWithCategory> ``` This case will also work fine without `@ColumnInfo` annotation, it just needs some small modifications: ```kotlin class BookWithCategorySimple { @Embedded lateinit var book: Book @TypeConverters(CategoryNameTypeConverter::class) lateinit var category: Category.Name } ``` And we also need to upgrade our query: ```kotlin @Query("SELECT *, categories.name AS category FROM books JOIN categories ON books.category_id = categories.id") fun setBooksWithCategoriesSimple(): List<BookWithCategorySimple> ``` The results in our TestActivity will be the same. It’s your choice which way you want to design your queries, and how to handle relations, but remember about `@Transaction`. Please don’t forget to update PrepopulateData and create the **CategoryDao** – it should be needed while prepopulating DB. ## **Many-to-many relation** In SQL, implementing M:N relations requires a join table of some form, where the join table has foreign keys back to the entities being related. Room, using SQL at its core, does not change this. And since Room does not model relations, but only foreign keys, to create an M:N relation, you have to create a “join entity” that winds up creating the associated join table. Regarding our simple App let’s assume that every user can attend a class (subjects) – we need to create a simple entity that will represent the subject, and joined entity **users\_with\_subjects.** ```kotlin @Entity(tableName = "subjects") data class Subject( @PrimaryKey(autoGenerate = true) var id: Long, var name: String ) ``` ```kotlin @Entity(tableName = "users_with_subjects", primaryKeys = ["user_id", "subject_id"], foreignKeys = [ ForeignKey( entity = User::class, parentColumns = arrayOf("id"), childColumns = arrayOf("user_id") ), ForeignKey( entity = Subject::class, parentColumns = arrayOf("id"), childColumns = arrayOf("subject_id") ) ] ) data class UsersWithSubjects( @ColumnInfo(name = "user_id") var userId: Long, @ColumnInfo(name = "subject_id") var subjectId: Long ) ``` It can look a bit scary but if you have a closer look everything will work well. We are using composite primary key that is created with the **user\_id** and **subject\_id.** This will make every pair of user\_id and subject\_id unique and we will avoid the duplicates. Foreign keys are straightforward. They represent both **User** and **Subject** entities ids. Now with the proper DB query, we can find for example all subjects for a given user: ```kotlin @Query("SELECT * FROM subjects INNER JOIN users_with_subjects AS uws ON uws.subject_id = subjects.id INNER JOIN users ON users.id = uws.user_id WHERE users.id = :userId") fun getSubjectsForUser(userId: Long): List<Subject> ``` or, a list of users that should attend a given subject: ```kotlin @Query("SELECT * FROM users INNER JOIN users_with_subjects AS uws ON uws.user_id = users.id INNER JOIN subjects ON subjects.id = uws.subject_id WHERE subjects.id = :subjectId") fun getUsersForGivenSubjects(subjectId: Long): List<User> ``` Please remember about adding proper migration, prepopulated data set, entities classes to DB and increasing the DB version. **To sum up the relations:** The Room provides the same relation types as in **SQL**, however, unlike the majority of ORM’s it will not allow you to create object relation – this makes you write additional **POJO** class that will aggregate query results into objects. Nevertheless, you can use `@Relation` annotation to avoid complicated **SELECT** queries. ## **Things you should also see** * First of all, you should always query things you really need. In most cases you won’t even need the object relation and simple “id” and “name” will be suitable to meet the business rules of the app. * If possible try to use `@Relation` annotation to simplify the one-to-many relation. * Always use the foreign keys constraints between entities. This will allow you to validate data consistency when you are modifying the database use onDelete and onUpdate methods with proper actions SET\_NULL, CASCADE, NO\_ACTION etc… ### **Returning subsets of columns:** We did it in previous queries but I want to show you how to work with **POJO’s** that are returned by the query where we don’t need the whole objects. Let’s think about users books. In most cases, we will need only the ***title***, ***author*** and ***category*** – to show on a list. All we need is a simple **POJO** and a good query: ```kotlin class UsersBooksSimple { @ColumnInfo(name = "title") lateinit var name: String lateinit var author: String @TypeConverters(CategoryNameTypeConverter::class) lateinit var category: Category.Name } ``` The first line is the book ***title***, the second line will represent the author as the combination of two columns ***author\_firstName*** and ***author\_lastName***, the last one is the book ***category***. If the returned column name matches the field name in **POJO** we don’t have to use the `@ColumnInfo(name= “…”)` annotation. In other cases we need to specify how we named the queried table, just like in the following query: ```kotlin @Query("SELECT categories.name AS category, books.title, (books.author_firstName || " " || books.author_lastName) AS author FROM books INNER JOIN categories ON books.category_id = categories.id INNER JOIN users ON users.id = books.user_id WHERE users.id = :userId") fun getUsersBooksSimple(userId: Long): List<UsersBooksSimple> ``` As you can see we didn’t need to create a new **TypeConverter** to creating the author full name field – we can use the SQLite functionalities and create one field from two queried columns. We can also do a similar trick with the `@Relation` annotation, let’s check how we can use projection field when defining **POJO**. Let’s create another class for **UserWithAllBooks** which contains only a book title. ```kotlin class UserWithAllBooksTitle { @Embedded lateinit var user: User @Relation(parentColumn = "id", entityColumn = "user_id", entity = Book::class, projection = ["title"]) lateinit var bookkTitle: List<String> } ``` ```sql @Transaction @Query("SELECT * FROM users WHERE id = :userId LIMIT 1") fun getUsersBooksTitles(userId: Long): UserWithAllBooksTitle ``` We can also pass multiple parameters to the query: ```sql @Query("SELECT * FROM users WHERE users.firstName LIKE :firstName OR users.lastName LIKE :lastName") fun getUsersByName(firstName: String, lastName: String): List<User> ``` As simple as that! Everything is a matter of how you create your query. ### **Abstract DAO class** Detailed gist can be found [***here***](https://gist.github.com/florina-muntenescu/1c78858f286d196d545c038a71a3e864). In a nutshell, instead of using interface for creating specifics DAO’s you can choose the abstract class. Then you can create methods like this: ```kotlin @Dao public abstract class ProductDao { @Insert public abstract void insert(Product product); @Delete public abstract void delete(Product product); @Transaction public void insertAndDeleteInTransaction(Product newProduct, Product oldProduct) { // Anything inside this method runs in a single transaction. insert(newProduct); delete(oldProduct); } } ``` Furthermore, if you are tired of creating same DAO methods (insert, delete etc..) you can create one generic Interface `BaseDao` then create abstract DAO that implements this interface so you will end up like this: ```kotlin interface BaseDao<T> { @Insert fun insert(obj: T) } ``` ```kotlin @Dao abstract class AbstractUserDao : BaseDao<User>() { @Query("SELECT * FROM users") abstract fun getUsers(): List<User> } ``` Usage: ```kotlin val users = abstractUserDao.getUsers() abstractUserDao.delete(users.first()) abstractUserDao.insert( /* some user */ ) ``` ## **Observable queries** Why would we need any observable queries? The simplest and the most obvious answer is that we want our UI to update automatically when the data changes. To achieve this we can simply use **LiveData** or **RxJava2** as returning value of our query methods. The Room is generating all necessary code to update the values when a database changed. ## **LiveData** We need to add some dependencies to use the LiveData and AndroidViewModel. It’s better to use LiveData with lifecycle aware component. ```kotlin dependencies { def lifecycle_version = "1.1.1" //… implementation "android.arch.lifecycle:extensions:$lifecycle_version” kapt "android.arch.lifecycle:compiler:$lifecycle_version" //… } ``` Now we need to modify the method for getting the user by **Id**. ```sql @Query("SELECT * FROM users WHERE id = :userId") fun user(userId: Long): LiveData<User> ``` To get all benefits of the architecture components we need to create a separate activity with the corresponding **AndroidViewModel** ```kotlin class LiveDataViewModel(application: Application) : AndroidViewModel(application) { private var userDao: UserDao = Injector.provideUserDao(application) fun getUser(userId: Long): LiveData<User> { return userDao.user(userId) } } ``` And the activity where in order to observe database for changes, we need to register an observer on the **LiveData** object by using the method ***observe()*** as shown below: ```kotlin class LiveDataTestActivity : AppCompatActivity() { override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_live_data_test) val model = ViewModelProviders.of(this).get(LiveDataViewModel::class.java) model.getUser(1).observe(this, Observer { user -> Timber.i("$user") }) } } ``` That’s all! Now when we run the **LiveDataTestActivity** we will see a similar log output: ```kotlin User( id=1, firstName=John, lastName=Doe, fullName=John Doe, birthday=1980-01-01, homeAddress=Address( street=Washington Street, city=Arkansas, postal=72701), officeAddress=Address( street=4th Street, city=Arkansas, postal=72749), emailAddress=jdoe@mail.com, phoneNumber=001333444555, picture=/pictures/jdoe/avatar/s34trag_732_jkdal.png, status=ACTIVE ) ``` The timber log will be now called every time Room detects that the result of that query you wrote has changed. ## **RxJava2** For using **RxJava2** we also need some more dependencies. **RxJava**, **RxKotlin** and **RxJava** support for Room ```kotlin implementation "io.reactivex.rxjava2:rxkotlin:2.3.0" implementation "io.reactivex.rxjava2:rxandroid:2.1.0" //.. implementation "android.arch.persistence.room:rxjava2:$room_version" ``` Similarly, as in the previous case, we need to modify the query in **UserDao** ```kotlin @Query("SELECT * FROM users WHERE id = :userId") fun userRx(userId: Long): Flowable<User> ``` Usage: ```kotlin userDao.userRx(1) .subscribeOn(Schedulers.io()) .observeOn(AndroidSchedulers.mainThread()) .doOnNext { Timber.i("$it") } .subscribe() .addTo(disposable) ``` Log output will be the same as in **LiveData**. If you are using RxJava2 in your application make your database reactive too! We can use the advantage of **Rx** returning also types such as **Maybe, Single, Flowable.** ### `@Flowable` * No data (no rows were returned) neither onNext or onError will be emitted * Data – onNext will be triggered * Update – every data that will be updated and is a part of a query will trigger another onNext ```sql @Query("SELECT * FROM users WHERE id = :userId") fun userRxFlowable(userId: Long): Flowable<User> ``` ### `@Single` * No data (no rows were returned) onError(EmptyResultSetException) will be triggered * Data – onSucces will be triggered * Update – nothing happens ```sql @Query("SELECT * FROM users WHERE id = :userId") fun userRxSingle(userId: Long): Single<User> ``` ### `@Maybe` * No data (no rows were returned) Maybe will complete * Data – onSuccess will be triggered and Maybe will complete * Update – nothing happens ```sql @Query("SELECT * FROM users WHERE id = :userId") fun userRxMaybe(userId: Long): Maybe<User> ``` \*\*Watch out! \*\*You need to be aware that every update on the field used in DB query will trigger the observable query and UI will be updated (or deleted) – this is OK if you design your app like that, but it can be also – unwanted behaviour. You should control your code. When you don’t need to update your UI try to use Single instead of Flowable. Make sure to handle the emission in your DAO. With flowable you can simply use `.distinctUntilChanged()`[***link***](http://reactivex.io/RxJava/2.x/javadoc/io/reactivex/Flowable.html#distinctUntilChanged--) With LiveData it’s a bit harder and you can see more [***here***](https://gist.github.com/florina-muntenescu/fea9431d0151ce0afd2f5a0b8834a6c7). That’s All Folks! We’ve reached the end of the third part of the introduction to the Room Persistence Library. I hope you’ve enjoyed this post. In case of any questions don’t hesitate to write a comment. > *Edit: \[October 8.2018\]* > *During work on this post, a new version of Room was released (2.1.0-alpha01).* Cheers! > *This post was originally published on*[***Speednet blog***](https://speednetsoftware.com/room-persistence-library-introduction-part-3/)*on 16.10.2018*