This article is the third and the last part of the three-part series that will smoothly introduceRoom Persistence Libraryto 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 – it was focused on configuring the project and explaining the basic structures.
Part 2 can be found here – it was focused on@Embedded
entities and@TypeConverters
.
All sources can be found in related GitHub 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
@Entity
data class Book(
@Id var id: Long,
var title: String
var authorId: Long,
@ToOne(joinProperty="authorId")
var author: Author
)
and
@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:
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.
@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 ForeignKeyconstraints 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!)
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:
@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:
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:
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:
@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.
@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:
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
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:
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-onerelation 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:
@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:
class BookWithCategorySimple {
@Embedded
lateinit var book: Book
@TypeConverters(CategoryNameTypeConverter::class)
lateinit var category: Category.Name
}
And we also need to upgrade our query:
@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.
@Entity(tableName = "subjects")
data class Subject(
@PrimaryKey(autoGenerate = true)
var id: Long,
var name: String
)
@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:
@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:
@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:
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:
@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.
class UserWithAllBooksTitle {
@Embedded
lateinit var user: User
@Relation(parentColumn = "id", entityColumn = "user_id", entity = Book::class, projection = ["title"])
lateinit var bookkTitle: List<String>
}
@Transaction
@Query("SELECT * FROM users WHERE id = :userId LIMIT 1")
fun getUsersBooksTitles(userId: Long): UserWithAllBooksTitle
We can also pass multiple parameters to the query:
@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.
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:
@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<T>
then create abstract DAO that implements this interface so you will end up like this:
interface BaseDao<T> {
@Insert
fun insert(obj: T)
}
@Dao
abstract class AbstractUserDao : BaseDao<User>() {
@Query("SELECT * FROM users")
abstract fun getUsers(): List<User>
}
Usage:
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.
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.
@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
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:
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:
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
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
@Query("SELECT * FROM users WHERE id = :userId")
fun userRx(userId: Long): Flowable<User>
Usage:
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
@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
@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
@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
With LiveData it’s a bit harder and you can see more here.
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 onSpeednet blogon 16.10.2018