DAOs and Queries

One popular thing to do with a database is to get data out of it. For that, we add @Query functions on our DAO.

Those do not have to be especially complex. The loadAll() functions in the samples shown in this chapter are delightfully simple:

    @Query("SELECT * FROM customColumn")
    fun loadAll(): List<CustomColumnNameEntity>

However, SQL queries with SQLite can get remarkably complicated. Room tries to support a lot of the standard SQL syntax, but Room adds its own complexity, in terms of trying to decipher how to interpret your @Query function’s arguments and return type.

Adding Parameters

As we saw with functions like findByPrimaryKey(), you can map function arguments to query parameters by using : syntax. Put : before the argument name and its value will be injected into the query:

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

WHERE Clause

Principally, your function arguments will be injected into your WHERE clause, such as in the above example.

Note that Room has special support for IN in a WHERE clause, where you can query using a vararg or List parameter:

    @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>

Here, the IN (:ids) SQL syntax will be expanded by Room to include all of the values that you supply in the argument. In this case, you would retrieve all of the entities matching any of those primary key values.

Other Clauses

Wherever SQLite allows ? placeholders, Room should allow function arguments to be used instead.

So, for example, you can parameterize a LIMIT clause:

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

What You Can Return

We have seen that a @Query can return a single entity (e.g., the single-ID findByPrimaryKey() functions) or a collection of entities (e.g., loadAll() returning a List of entities).

While those are simple, Room offers a fair bit more flexibility than that.

Returning Cursor

In addition to returning single objects or collections of objects, a Room @Query can return a good old-fashioned Cursor:

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

This is particularly useful if you are migrating legacy code that uses CursorAdapter or other Cursor-specific classes. Similarly, if you are looking to expose part of a Room-defined database via a ContentProvider, it may be more convenient for you to get your results in the form of a Cursor, so that you can just return that from the provider’s query() function.

Note that, as with getting a Cursor from SQLiteDatabase, you are responsible for closing the Cursor when you are done with it.

Non-Entity Results

For small entities, like the ones shown so far in this chapter, usually we will retrieve all columns in the query. However, the real rule is: the core return object of the @Query function must be something that Room knows how to fill in from the columns that you request.

For wider tables with many columns, this is important. For example, perhaps for a RecyclerView, you only need a couple of columns, but for all entities in the table. In that case, it might be nice to only retrieve those specific columns. You have two ways to do that:

1. Have your @Entity support only a subset of columns, allowing the rest to be null or otherwise tracking the fact that we only retrieved a subset of columns from the table
2. Return something other than the entity that you have associated with this table

If you look at your @Dao-annotated interface, you will notice that while functions might refer to entities, its annotations do not. That is because the DAO is somewhat independent of the entities. The entities describe the table, but the DAO is not limited to using those entities. So long as the DAO can fulfill the contract stipulated by the SQL, the function arguments, and the function return type, Room is perfectly happy.

For example, suppose that you were making an app store client. While many developers think that the Play Store is the only app store client, there are lots of alternatives, such as F-Droid, which specializes in open source apps.

In your data model, you will need some sort of Room entity representing apps in the store catalog. That may involve a lot of data, where you might need all of that data for the app “display” screen in your app. However, when you are presenting a list of apps — the whole catalog, some category of apps, or search results — you may need just a subset of that data. For that, you can take advantage of Room’s query flexibility and define a “list model” class that contains the subset of data that your lists need:

package com.commonsware.room.misc

import androidx.room.*

@Entity(tableName = "apps")
data class AppEntity(
  @PrimaryKey
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val fullDescription: String,
  val latestVersionName: String,
  val lastUpdated: Long,
  val iconUrl: String,
  val packageUrl: String,
  val donationUrl: String
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM apps")
    fun loadAll(): List<AppEntity>

    @Query("SELECT applicationId, displayName, shortDescription, iconUrl FROM apps")
    fun loadListModels(): List<AppListModel>

    @Insert
    fun insert(entity: AppEntity)
  }
}

data class AppListModel(
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val iconUrl: String
)

Here, AppEntity itself is unremarkable. However, we also define an AppListModel class, with a subset of the AppEntity properties. AppEntity.Store not only defines a loadAll() function that returns entities, but it has a loadListModels() function that returns AppListModel objects. loadListModels() has a @Query annotation that returns only the columns needed by AppListModel, and Room will happily pour that data into AppListModel objects for us. The only connection between AppEntity and AppListModel is that our loadListModels() query queries the apps table.

Now, app code that displays lists, or otherwise only needs this subset of the overall entity data, can work with the “list models” instead:

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

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

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

    val fdroid = AppEntity(
      applicationId = "org.fdroid.fdroid",
      displayName = "F-Droid",
      shortDescription = "An independent app store featuring open source Android apps",
      fullDescription = "F-Droid is an installable catalogue of FOSS (Free and Open Source Software) applications for the Android platform. The client makes it easy to browse, install, and keep track of updates on your device. Visit https://f-droid.org to learn more!",
      lastUpdated = 1566652015000,
      latestVersionName = "1.7.1",
      donationUrl = "https://flattr.com/thing/343053/F-Droid-Repository",
      packageUrl = "https://f-droid.org/FDroid.apk",
      iconUrl = "https://gitlab.com/fdroid/fdroidclient/raw/master/app/src/main/res/drawable-hdpi/ic_launcher.png?inline=true"
    )

    underTest.insert(fdroid)

    underTest.loadAll().let {
      assertThat(it, hasSize(equalTo(1)))
      assert(it[0] == fdroid)
    }

    underTest.loadListModels().let {
      assertThat(it, hasSize(equalTo(1)))

      val model = it[0]

      assertThat(model.applicationId, equalTo(fdroid.applicationId))
      assertThat(model.displayName, equalTo(fdroid.displayName))
      assertThat(model.shortDescription, equalTo(fdroid.shortDescription))
      assertThat(model.iconUrl, equalTo(fdroid.iconUrl))
    }
  }
}

Note that @ColumnInfo annotations can be used on any class, not just entities. Frequently, if you use @ColumnInfo on a property in an entity, you will wind up using that same @ColumnInfo on the corresponding property in any sort of “list model”-style object, so that the property names line up with the column in the table.

Reactive Return Types

All of our DAO functions have returned values directly, whether those values are entities, lists of entities, or something else (e.g., list of some “tuple” objects).

Those functions are synchronous. The query will be performed when we call the function, and we get the results of the query returned to us.

That is very simple. It is also very annoying, as it means that we have to deal with background threads ourselves. We do not want to be calling these DAO functions on the main application thread, as database I/O can be slow. As a result, we will be forced to use something else, such as a Java Executor, to be able to call the functions from a background thread.

However, if you look at a lot of Room sample code, you will see that the @Query-annotated functions often wrap the return values in… something else:

• LiveData
• Flow
• Single
• Observable
• Flowable
• and so on

These are classes from reactive frameworks. LiveData is part of the Android Jetpack, Flow is from Kotlin coroutines, and the others are from RxJava. These frameworks are designed to help simplify threading. They hide the complexity of doing the actual database I/O on a background thread while getting the results to you on the main application thread (or some other thread of interest to you).

We will explore these options, and threads with Room in general, in an upcoming chapter.

Aggregate Functions

SQL supports aggregate functions, like COUNT and SUM. If you include these in a query, you get those calculated values back, instead of (or perhaps in addition to) actual data from a table.

Room also supports aggregate functions. However, by definition, there is no entity whose properties are counts or sums. We have two options for getting our results back for these calculations:

• If we need just a single value, we can have a @Query-annotated function return an Int, Long, or other basic type that represents the result from the aggregate function
• Otherwise, we use a variation on the approach from the preceding section, where we create a data class or similar structure that Room can use to return our results

package com.commonsware.room.misc

import androidx.room.*
import kotlin.random.Random

data class CountAndSumResult(val count: Int, val sum: Long)

@Entity(tableName = "aggregate")
class AggregateEntity(
  @PrimaryKey(autoGenerate = true)
  val id: Long = 0,
  val value: Long = Random.nextLong(1000000)
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM aggregate")
    fun loadAll(): List<AggregateEntity>

    @Query("SELECT COUNT(*) FROM aggregate")
    fun count(): Int

    @Query("SELECT COUNT(*) as count, SUM(value) as sum FROM aggregate")
    fun countAndSum(): CountAndSumResult

    @Insert
    fun insert(entities: List<AggregateEntity>)
  }
}

Here, our entity just has an auto-generated primary key, plus some other randomly-generated Long value (cunningly stored in a value property). In its associated AggregateEntity.Store, in addition to our standard loadAll() function and an insert() that accepts a List of entities, we have:

• count(), which returns an Int representing the count of rows in the table
• countAndSum(), which returns the count of rows and the sum of the values, in the form of a CountAndSumResult.

Note that the SQL for countAndSum() uses the AS operator to put a specific name on the returned values. Those need to map to the property names in your result type.

We can then use those functions just like any other @Dao functions. And, in the case of countAndSum(), we can use Kotlin’s destructuring declarations to avoid having to fuss with a CountAndSumResult:

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.greaterThan
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 AggregateEntityTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.aggregate()

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

    underTest.insert(List(100) { AggregateEntity() })

    assertThat(underTest.count(), equalTo(100))

    val (count, sum) = underTest.countAndSum()

    assertThat(count, equalTo(100))
    assertThat(sum, greaterThan(0L))
  }
}