The Dao of Entities

In the previous chapter, we went through the basic steps for setting up Room:

Create and annotate your entity classes
Create, annotate, and define operator functions on your DAO(s)
Create a subclass of RoomDatabase to tie the entities and DAO(s) together
Create an instance of that RoomDatabase at some likely point in time,
Use the RoomDatabase instance to retrieve your DAO and from there work with your entities

However, we only scratched the surface of what can be configured on entities and DAOs. In this chapter, we will start to explore the rest of the configuration for entities and DAOs.

Many of the code snippets shown in this chapter come from the the MiscSamples module of the book’s primary sample project, sample project. This is a library module with a variety of entities and DAOs, all tied into a MiscDatabase, with instrumented tests for each of the entities.

Configuring Entities

The only absolute requirements for a Room entity class is that it be annotated with the @Entity annotation and have a field identified as the primary key, typically by way of a @PrimaryKey annotation. Anything above and beyond that is optional.

However, there is a fair bit that is “above and beyond that”. Some — though probably not all — of these features will be of interest in larger apps.

Primary Keys

If you have a single field that is the primary key for your entity, using the @PrimaryKey annotation is simple and helps you clearly identify that primary key at a later point.

However, you do have some other options.

Auto-Generated Primary Keys

In SQLite, if you have an INTEGER column identified as the PRIMARY KEY, you can optionally have SQLite assign unique values for that column, by way of the AUTOINCREMENT keyword.

In Room, if you have an Long property that is your @PrimaryKey, you can optionally apply AUTOINCREMENT to the corresponding column by adding autoGenerate=true to the annotation:

package com.commonsware.room.misc

import androidx.room.*

@Entity(tableName = "autoGenerate")
data class AutoGenerateEntity(
  @PrimaryKey(autoGenerate = true)
  var id: Long,
  var text: String
) {
  @Dao
  abstract class Store {
    @Query("SELECT * FROM autoGenerate")
    abstract fun loadAll(): List<AutoGenerateEntity>

    @Query("SELECT * FROM autoGenerate WHERE id = :id")
    abstract fun findById(id: Int): AutoGenerateEntity

    fun insert(entity: AutoGenerateEntity): AutoGenerateEntity {
      entity.id = _insert(entity)

      return entity
    }

    @Insert
    abstract fun _insert(entity: AutoGenerateEntity): Long
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/AutoGenerateEntity.kt)

By default, autoGenerate is false. Setting that property to true gives you AUTOINCREMENT in the generated CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS autoGenerate (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, text TEXT NOT NULL)

However, this starts to get complicated in the app. You do not know your primary key until you insert the entity into a database. Your @Insert-annotated functions can return a Long result, and that will be the primary key for that inserted entity. In the AutoGenerateEntity shown above, _insert() has the @Insert annotation, while insert() wraps _insert() and sets the inserted entity’s id to be the Long returned by _insert(). Hence, insert() updates the entity to have its primary key:

package com.commonsware.room.misc

import androidx.room.Room
import androidx.test.platform.app.InstrumentationRegistry
import androidx.test.ext.junit.runners.AndroidJUnit4
import com.natpryce.hamkrest.assertion.assertThat
import com.natpryce.hamkrest.equalTo
import com.natpryce.hamkrest.isEmpty

import org.junit.Test
import org.junit.runner.RunWith

import org.junit.Assert.*

@RunWith(AndroidJUnit4::class)
class AutoGenerateEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.autoGenerate()

  @Test
  fun autoGenerate() {
    assertThat(underTest.loadAll(), isEmpty)

    val original = AutoGenerateEntity(id = 0, text = "This will get its own ID")
    val inserted = underTest.insert(original)

    assertTrue(original === inserted)
    assertThat(inserted.id, !equalTo(0L))
  }
}

(from MiscSamples/src/androidTest/java/com/commonsware/room/misc/AutoGenerateEntityTest.kt)

This presents “trickle-down” complications — for example, you cannot make the primary key property be val, as then you cannot create an instance of an entity that is not yet in the database.

Some of the samples in this book will use a UUID instead. While these take up much more room than a simple Long, they can be uniquely generated outside of the database. For your production apps, you will need to decide if the headaches surrounding database-generated identifiers are worth their benefits.

Composite Primary Keys

In some cases, you may have a composite primary key, made up of two or more columns in the database. This is particularly true if you are trying to design your entities around an existing database structure, one that used a composite primary key for one of its tables (for whatever reason).

If, logically, those are all part of a single object, you could combine them into a single property, as we will see in the next chapter. However, it may be that they should be individual properties in your entity, but they happen to combine to create the primary key. In that case, you can skip the @PrimaryKey annotation and use the primaryKeys property of the @Entity annotation.

One scenario for this is data versioning, where we are tracking changes to data over time, the way a version control system tracks changes to source code and other files over time. There are several ways of implementing data versioning. One approach has all versions of the same entity in the same table, with a version code attached to the “natural” primary key to identify a specific version of that content. In that case, you could have something like:

package com.commonsware.room.misc

import androidx.room.Dao
import androidx.room.Entity
import androidx.room.Insert
import androidx.room.Query

@Entity(tableName = "compositeKey", primaryKeys = ["id", "version"])
data class CompositeKeyEntity(
  val id: String,
  val title: String,
  val text: String = "",
  val version: Int = 1
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM compositeKey")
    fun loadAll(): List<CompositeKeyEntity>

    @Query("SELECT * FROM compositeKey where id = :id AND version = :version")
    fun findByPrimaryKey(id: String, version: Int): CompositeKeyEntity

    @Insert
    fun insert(entity: CompositeKeyEntity)
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/CompositeKeyEntity.kt)

Room will then use the PRIMARY KEY keyword in the CREATE TABLE statement to set up the composite primary key:

CREATE TABLE IF NOT EXISTS compositeKey (id TEXT NOT NULL, title TEXT NOT NULL, text TEXT NOT NULL, version INTEGER NOT NULL, PRIMARY KEY(id, version))

In our case, we set version to have a default value of 1, so we can create a CompositeKeyEntity with just a string identifier, at least for its initial version:

package com.commonsware.room.misc

import android.database.sqlite.SQLiteConstraintException
import androidx.room.Room
import androidx.test.ext.junit.runners.AndroidJUnit4
import androidx.test.platform.app.InstrumentationRegistry
import com.natpryce.hamkrest.assertion.assertThat
import com.natpryce.hamkrest.equalTo
import com.natpryce.hamkrest.isEmpty
import org.junit.Test
import org.junit.runner.RunWith
import java.util.*

@RunWith(AndroidJUnit4::class)
class CompositeKeyEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.compositeKey()

  @Test
  fun compositeKey() {
    assertThat(underTest.loadAll(), isEmpty)

    val original = CompositeKeyEntity(
      id = UUID.randomUUID().toString(),
      title = "A composite key entity"
    )

    underTest.insert(original)

    underTest.loadAll().let {
      assertThat(it.size, equalTo(1))
      assertThat(it[0], equalTo(original))
    }

    assertThat(
      underTest.findByPrimaryKey(
        id = original.id,
        version = original.version
      ), equalTo(original)
    )
  }

  @Test(expected = SQLiteConstraintException::class)
  fun duplicateCompositeKey() {
    assertThat(underTest.loadAll(), isEmpty)

    val original = CompositeKeyEntity(
      id = UUID.randomUUID().toString(),
      title = "A composite key entity"
    )

    underTest.insert(original)

    val copy = original.copy(text = "This is different!")

    underTest.insert(copy)
  }
}

(from MiscSamples/src/androidTest/java/com/commonsware/room/misc/CompositeKeyEntityTest.kt)

If we try to insert entities with the same key twice, our @Insert-annotated function will throw a SQLiteConstraintException.

Adding Indexes

Your primary key is indexed automatically by SQLite. However, you may wish to set up other indexes for other columns or collections of columns, to speed up queries. To do that, you have two choices:

Use the indices property on @Entity. This property takes a list of nested Index annotations, each of which declares an index.
Use the index property on @ColumnInfo, to add an index on a single property.

The latter is simpler; the former handles more complex scenarios (e.g., an index involving multiple properties).

Here, we have an entity with an index on a category property:

package com.commonsware.room.misc

import androidx.room.*

@Entity(tableName = "indexified")
data class IndexedEntity(
  @PrimaryKey
  val id: String,
  val title: String,
  @ColumnInfo(index = true) val category: String,
  val text: String? = null,
  val version: Int = 1
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM indexified")
    fun loadAll(): List<IndexedEntity>

    @Query("SELECT * FROM indexified where category = :category")
    fun loadAllForCategory(category: String): List<IndexedEntity>

    @Insert
    fun insert(vararg entity: IndexedEntity)
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/IndexedEntity.kt)

Room will add the requested index:

CREATE TABLE IF NOT EXISTS indexified (id TEXT NOT NULL, title TEXT NOT NULL, category TEXT NOT NULL, text TEXT, version INTEGER NOT NULL, PRIMARY KEY(id))
CREATE INDEX IF NOT EXISTS index_indexified_category ON indexified (category)

Alternatively, we could have used indices on the @Entity annotation:

@Entity(tableName = "indexified", indices = [Index("category")])
class IndexedEntity(
  @PrimaryKey
  val id: String,
  val title: String,
  val category: String,
  val text: String? = null,
  val version: Int = 1
)

If you have a composite index, consisting of two or more fields, the Index nested annotation takes a comma-delimited list of column names and will generate the composite index.

The index will be used by SQLite automatically if you execute queries that involve the index. The loadAllForCategory() function queries on the indexed category property, and so our index should be used when we call that function:

package com.commonsware.room.misc

import androidx.room.Room
import androidx.test.platform.app.InstrumentationRegistry
import androidx.test.ext.junit.runners.AndroidJUnit4
import com.natpryce.hamkrest.assertion.assertThat
import com.natpryce.hamkrest.equalTo
import com.natpryce.hamkrest.hasSize
import com.natpryce.hamkrest.isEmpty

import org.junit.Test
import org.junit.runner.RunWith

import java.util.*

@RunWith(AndroidJUnit4::class)
class IndexedEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.indexed()

  @Test
  fun queryByCategory() {
    assertThat(underTest.loadAll(), isEmpty)

    val funStuff = IndexedEntity(
      id = UUID.randomUUID().toString(),
      title = "This is fun!",
      category = "fun-stuff",
      text = "words words words"
    )
    val notAsFunStuff = IndexedEntity(
      id = UUID.randomUUID().toString(),
      title = "Gloom, despair, and agony on me",
      category = "un-fun-stuff"
    )

    underTest.insert(funStuff, notAsFunStuff)

    underTest.loadAllForCategory("fun-stuff").let {
      assertThat(it, hasSize(equalTo(1)))
      assert(it[0] == funStuff)
    }
  }
}

(from MiscSamples/src/androidTest/java/com/commonsware/room/misc/IndexedEntityTest.kt)

If the index should also enforce uniqueness — only one entity can have the indexed value — add unique = true to the Index annotation. This requires you to assign the column(s) for the index to the value property, due to the way annotations work in Kotlin:

package com.commonsware.room.misc

import androidx.room.*

@Entity(
  tableName = "uniquelyIndexed",
  indices = [Index(value = ["title"], unique = true)]
)
data class UniqueIndexEntity(
  @PrimaryKey val id: String,
  val title: String,
  val text: String = "",
  val version: Int = 1
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM uniquelyIndexed")
    fun loadAll(): List<UniqueIndexEntity>

    @Insert
    fun insert(entity: UniqueIndexEntity)

    @Insert(onConflict = OnConflictStrategy.ABORT)
    fun insertOrAbort(entity: UniqueIndexEntity)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertOrIgnore(entity: UniqueIndexEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertOrReplace(entity: UniqueIndexEntity)
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/UniqueIndexEntity.kt)

This causes Room to add the UNIQUE keyword to the CREATE INDEX statement:

CREATE TABLE IF NOT EXISTS uniquelyIndexed (id TEXT NOT NULL, title TEXT NOT NULL, text TEXT NOT NULL, version INTEGER NOT NULL, PRIMARY KEY(id))
CREATE UNIQUE INDEX IF NOT EXISTS index_uniquelyIndexed_title ON uniquelyIndexed (title)

While a regular index supports multiple values, a unique index does not, leading once again to a SQLiteConstraintException if we try inserting a duplicate:

package com.commonsware.room.misc

import android.database.sqlite.SQLiteConstraintException
import androidx.room.Room
import androidx.test.platform.app.InstrumentationRegistry
import androidx.test.ext.junit.runners.AndroidJUnit4
import com.natpryce.hamkrest.*
import com.natpryce.hamkrest.assertion.assertThat

import org.junit.Test
import org.junit.runner.RunWith

import java.util.*

private const val TEST_TITLE = "A Tale of Two Entities"

@RunWith(AndroidJUnit4::class)
class UniqueIndexEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.uniqueIndex()

  @Test
  fun singleInsert() {
    assertThat(underTest.loadAll(), isEmpty)

    val firstEntity = UniqueIndexEntity(
      id = UUID.randomUUID().toString(),
      title = TEST_TITLE,
      text = "This entity will get inserted successfully")

    underTest.insert(firstEntity)

    assertThat(
      underTest.loadAll(),
      allOf(hasSize(equalTo(1)), hasElement(firstEntity))
    )
  }

  @Test(expected = SQLiteConstraintException::class)
  fun duplicateFailure() {
    singleInsert()

    val secondEntity = UniqueIndexEntity(
      id = UUID.randomUUID().toString(),
      title = TEST_TITLE,
      text = "This entity is doomed")

    underTest.insert(secondEntity)
  }
}

(from MiscSamples/src/androidTest/java/com/commonsware/room/misc/UniqueIndexEntityTest.kt)

Ignoring Properties

If there are properties in the entity class that should not be persisted, you can annotate them with @Ignore:

package com.commonsware.room.misc

import androidx.room.*

@Entity(tableName = "ignoredProperty")
data class IgnoredPropertyEntity(
  @PrimaryKey val id: String,
  val title: String,
  val version: Int = 1
) {
  @Ignore var text: String = ""
  var moreText: String = ""

  @Dao
  interface Store {
    @Query("SELECT * FROM ignoredProperty")
    fun loadAll(): List<IgnoredPropertyEntity>

    @Query("SELECT * FROM ignoredProperty where id = :id")
    fun findByPrimaryKey(id: String): IgnoredPropertyEntity

    @Insert
    fun insert(entity: IgnoredPropertyEntity)
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/IgnoredPropertyEntity.kt)

You might think that you could skip that and use other techniques, such as by using a private val property:

@Entity(tableName = "ignoredProperty")
data class IgnoredPropertyEntity(
  @PrimaryKey val id: String,
  val title: String,
  val version: Int = 1
) {
  private val text: String = ""
  var moreText: String = ""
}

Since the text property is private and has no setter, one could argue that Room might ignore it automatically. Room, instead, generates a build error, as it cannot tell if you want to ignore that property or if you simply forgot to add it properly.

Another option, instead of @Ignore, is to use @Transient, if that annotation fits your needs better:

@Entity(tableName = "ignoredProperty")
data class IgnoredPropertyEntity(
  @PrimaryKey val id: String,
  val title: String,
  val version: Int = 1
) {
  @Transient var text: String = ""
  var moreText: String = ""
}

A third option is to use ignoredColumns, a property in the @Entity annotation, that takes an array of column names that should be ignored:

@Entity(
  tableName = "ignoredProperty",
  ignoredColumns = ["text"]
)
data class IgnoredPropertyEntity(
  @PrimaryKey val id: String,
  val title: String,
  val version: Int = 1,
  var text: String = ""
) {
  var moreText: String = ""
}

Custom Column Names

By default, Room will generate names for your tables and columns based off of the entity class names and property names. In general, it does a respectable job of this, and so you may just leave them alone. However, you may find that you need to control these names, particularly if you are trying to match an existing database schema (e.g., you are migrating an existing Android app to use Room instead of using SQLite directly). And for table names in particular, setting your own name can simplify some of the SQL that you have to write for @Query-annotated functions.

As we have seen, to control the table name, use the tableName property on the @Entity attribute, and give it a valid SQLite table name. To rename a column, add the @ColumnInfo annotation to the property, with a name property that provides your desired name for the column:

package com.commonsware.room.misc

import android.database.Cursor
import androidx.room.*

@Entity(tableName = "customColumn")
class CustomColumnNameEntity(
  @PrimaryKey
  val id: String,
  val title: String,
  @ColumnInfo(name = "words") val text: String? = null,
  val version: Int = 1
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM customColumn")
    fun loadAll(): List<CustomColumnNameEntity>

    @Query("SELECT * FROM customColumn where id = :id")
    fun findByPrimaryKey(id: String): CustomColumnNameEntity

    @Query("SELECT * FROM customColumn where id IN (:ids)")
    fun findByPrimaryKeys(vararg ids: String): List<CustomColumnNameEntity>

    @Query("SELECT * FROM customColumn where id IN (:ids)")
    fun findByPrimaryKeys(ids: List<String>): List<CustomColumnNameEntity>

    @Query("SELECT * FROM customColumn LIMIT :limit")
    fun loadFirst(limit: Int): List<CustomColumnNameEntity>

    @Query("SELECT * FROM customColumn")
    fun loadCursor(): Cursor

    @Insert
    fun insert(entity: CustomColumnNameEntity)
  }
}

(from MiscSamples/src/main/java/com/commonsware/room/misc/CustomColumnNameEntity.kt)

Here, we changed the text property’s column to words, along with specifying the table name. The SQL will reflect that change:

CREATE TABLE IF NOT EXISTS customColumn (id TEXT NOT NULL, title TEXT NOT NULL, words TEXT, version INTEGER NOT NULL, PRIMARY KEY(id))

…even though we still refer to the property by its regular Kotlin name:

package com.commonsware.room.misc

import androidx.room.Room
import androidx.test.platform.app.InstrumentationRegistry
import androidx.test.ext.junit.runners.AndroidJUnit4
import com.natpryce.hamkrest.assertion.assertThat
import com.natpryce.hamkrest.equalTo
import com.natpryce.hamkrest.isEmpty

import org.junit.Test
import org.junit.runner.RunWith

import org.junit.Assert.*
import java.util.*

@RunWith(AndroidJUnit4::class)
class CustomColumnNameEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.customColumn()

  @Test
  fun customColumnPersists() {
    assertThat(underTest.loadAll(), isEmpty)

    val original = CustomColumnNameEntity(
      id = UUID.randomUUID().toString(),
      title = "This space available for rent",
      text = "This will be stored as words. Well, it will be stored in a column named 'words'."
    )

    underTest.insert(original)

    val retrieved = underTest.findByPrimaryKey(original.id)

    assertThat(retrieved.id, equalTo(original.id))
    assertThat(retrieved.title, equalTo(original.title))
    assertThat(retrieved.text, equalTo(original.text))
  }
}

(from MiscSamples/src/androidTest/java/com/commonsware/room/misc/CustomColumnNameEntityTest.kt)

Note, though, that many of the annotation attributes that Room uses refer to column names, not property names. For example, suppose that instead of using * to indicate that our queries should return all columns, we list the ones that we want… and we use property names:

@Query("SELECT id, title, text, version FROM customColumn")
fun loadAll(): List<CustomColumnNameEntity>

Android Studio will be unhappy with you:

And, if you try ignoring Android Studio and building the project anyway, you will get a build error:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: text)
        public abstract java.util.List<com.commonsware.room.misc.CustomColumnNameEntity> loadAll();

You need to use the column name instead:

@Query("SELECT id, title, words, version FROM customColumn")
fun loadAll(): List<CustomColumnNameEntity>

Also note that adding @ColumnInfo to a @Transient property means that this property will be included when creating the table structure, despite the @Transient annotation. By default, @Transient properties are ignored, but adding @ColumnInfo indicates that you want that default behavior to be overridden.

Other @ColumnInfo Options

Beyond specifying the column name to use, you can configure other options on a @ColumnInfo annotation. We saw using index = true earlier to add an index to a column, but we have options beyond that as well.

Collation

You can specify a collate property to indicate the collation sequence to apply to this column. Here, “collation sequence” is a fancy way of saying “comparison function for comparing two strings”.

There are four options:

BINARY and UNDEFINED, which are equivalent, the default value, and indicate that case is sensitive
NOCASE, which indicates that case is not sensitive (more accurately, that the 26 English letters are converted to uppercase)
RTRIM, which indicates that trailing spaces should be ignored on a case-sensitive collation

There is no full-UTF equivalent of NOCASE in SQLite.

Type Affinity

Normally, Room will determine the type to use on the column in SQLite based upon the type of the property (e.g., Int properties create INTEGER columns). If, for some reason, you wish to try to override this behavior, you can use the typeAffinity property on @ColumnInfo to specify some other type to use.

Default Values

@ColumnInfo also has a defaultValue property. As you might guess from the name, it provides a default value for the column in the table definition.

However, “out of the box”, it may be less useful than you think. If you @Insert an entity, the value for this column from the entity will be used, not the default value.

We will explore defaultValue, and scenarios where it is useful, later in the book.

Prev Table of Contents Next

This book is licensed under the Creative Commons Attribution-ShareAlike 4.0 International license.