Room - Query

Room tries to support a lot of the standard SQL syntax.

• Introduction
• Adding Parameters
• What You Can Return
• Aggregate Functions
• Dynamic Queries
• Other SQL Operations
• Transaction
• Summary

Introduction

A common thing to do with a database is to read data. For that, we add @Query functions on our DAO.

Those do not have to be especially complex.

The select() functions in the samples are delightfully simple:

@Query("SELECT * FROM Person")
suspend fun select(): List<Person>

However, SQL queries with SQLite can get quite sophisticated. Room tries to support much of the standard SQL syntax and adds its own layer to interpret your @Query function’s arguments and return types.

Adding Parameters

As we saw with functions like selectWhereId(), you can map function arguments to query parameters by using : syntax.

Put : before the argument name; its value will be bound into the query:

@Query("SELECT * FROM Party WHERE id = :id")
suspend fun selectWhereId(id: Long): Message?

where clause

Typically, function arguments are injected into the where clause, as in the previous example.

Room has special support for in with a vararg or List parameter:

@Query("select * from person where name in (:names)")
fun selectWhereNameIn(vararg names: String): List<Person>

Here, in (:names) is expanded by Room to include all values supplied in the argument, returning all entities matching any of those values.

Other clauses

Wherever SQLite allows ? placeholders, Room should allow function arguments.

For example, parameterize a limit clause:

@Query("select * from Person limit :limit")
fun loadFirst(limit: Int): List<Person>

What You Can Return

We have seen that a @Query can return a single entity (e.g., a single-ID selectWhereId() function) or a collection of entities (e.g., select() returning a List).

Room offers more flexibility than that.

Returning Cursor

In addition to objects or collections, a @Query can return a Cursor:

@Query("SELECT * FROM Person")
fun select(): Cursor

This is handy when migrating legacy code that uses CursorAdapter or when exposing data via a ContentProvider. As with SQLiteDatabase, you must close the Cursor.

Non-Entity Results

For wide tables, you may want only a subset of columns.

Room can map query columns to:

• An entity that tolerates missing columns, or
• A separate data type (e.g., a data class) that matches the selected columns

DAOs are independent of entities; they only need to fulfill the contract implied by the SQL, arguments, and return type.

For a Person table where you only need name and surname:

@Entity
data class Person(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String,
    val surname: String,
    val height: Double,
    val weight: Double,
    val description: String
) {
    @Dao
    interface SQL {

        @Query("SELECT id, name, surname FROM Person")
        suspend fun selectName(): List<Name>

        @Insert
        suspend fun insert(person: Person)
    }
}

data class Name(
    val id: Long,
    val name: String,
    val surname: String,
)

This avoids fetching unnecessary columns:

db.person().insert(
    Person(name = "David",surname = "de Mingo",height = 1.80, weight = 80.0, description = "A very nice teacher(sometimes 😺).")
)

db.person().selectName().first() shouldBe Name(1, "David", "de Mingo")

Reactive return types

Room supports reactive types like Flow. Queries are executed upon collection, and re-executed when underlying data changes, emitting updated results.

We will explore these options later.

Aggregate Functions

SQL supports aggregates like count and sum.

Room supports them too.

Since these do not map to entities, use either:

• A single primitive return type (Int, Long, etc.) for a single aggregate value, or
• A data class (or similar) for multiple values

@Entity
data class City(
    @PrimaryKey val name: String,
    val country: String,
    val population: Long,

    ) {
    @Dao
    interface SQL {
        @Insert
        suspend fun insert(cities: List<City>)

        @Query("SELECT SUM(population) / COUNT(*)  FROM City")
        suspend fun meanPopulation(): Long

        @Query("SELECT country, sum(population) as population FROM City GROUP BY country")
        suspend fun country(): List<CountryTotal>
    }
}

Note the as alias in country to match property names on the result type.

Dynamic Queries

Dynamic Queries

Other SQL Operations

To read data, we also need to write data.

We have seen basic @Insert, @Update, and @Delete SQL functions on Note.SQL:

@Entity(tableName = "Note")
data class Note(
    @PrimaryKey val id: String,
    val title: String,
    val text: String,
    val version: Int
) {
    @Dao
    interface SQL {
        @Query("select * from Note")
        suspend fun select(): List<Note>

        @Insert
        suspend fun insert(note: Note)

        @Update
        suspend fun update(note: Note)

        @Delete
        suspend fun delete(vararg note: Note)
    }
}

These are simpler than @Query. They provide convenience for table-targeted operations.

A few additional considerations:

Parameters

@Insert, @Update, and @Delete work with entities. delete() can accept a vararg. You can also use a List, as shown in the City example.

Return values

Often these return Unit.

Optionally:

• @Update/@Delete can return an Int with the number of rows affected
• Single-entity @Insert can return a Long rowid (and auto-increment PK if applicable)
• Multi-entity @Insert can return a List<Long> of rowids

Frequently, you have these functions return nothing (technically, they return Unit, though we can drop that in Kotlin).

Conflict resolution

@Insert and @Update support an optional onConflict (OnConflictStrategy), mapping to SQLite’s on conflict clause:

Default is ABORT. We will cover these in detail later.

Other operations

@Insert, @Update, and @Delete require entities (so Room knows the table). For anything else, use @Query — it supports any SQL, even those without result sets:

@Query("delete from monster")
fun dropMonsters()  // it's the only way to be sure

Think of these as convenience annotations, with @Query as the backbone for everything else.

Transaction

By default, SQLite treats each SQL statement as its own transaction. If Room needs multiple statements for your annotations, it wraps them appropriately.

Sometimes your business logic spans multiple SQL functions and must be atomic. For example, inserting an Invoice with its InvoiceLineItems.

Room offers two ways: the @Transaction annotation and functions on RoomDatabase.

Using @Transaction

Your @Dao can have functions annotated with @Transaction. Everything done within is wrapped in an SQLite transaction. If the function completes normally, Room commits; if it throws, Room rolls back.

You can apply @Transaction to custom functions on an abstract DAO class or directly on @Query functions.

Custom Functions

Use a @Transaction-annotated function to orchestrate multiple SQL calls so they “succeed or fail as a whole.”

Example with a fictitious Invoice:

data class Invoice(
    val id: Int
) {
    @Dao
    interface SQL {
        @Insert
        suspend fun __insert(invoice: Invoice)

        @Insert
        fun __insertItems(lineItems: List<InvoiceLineItem>)

        @Transaction
        suspend fun insert(invoice: Invoice, items: List<InvoiceLineItem>) {
            __insert(invoice)
            __insertItems(items)
        }
    }
}

Here, insert() wraps two SQL calls to insert both the Invoice and its InvoiceLineItems.

Here, we still use an insert() function to insert an Invoice, but we use that to wrap two SQL calls to insert the Invoice metadata and insert the InvoiceLineItem objects.

On @Query Functions

It may seem odd to explicitly request a transaction on a @Query-annotated function, since SQLite defaults to one transaction per statement.

However, some scenarios benefit from @Transaction. One example involves @Relation (covered later in Room - Relation), where ensuring atomic reads can prevent inconsistent intermediate states.

Using RoomDatabase

Alternatively, RoomDatabase exposes beginTransaction(), setTransactionSuccessful(), and endTransaction(), following the familiar pattern:

database.beginTransaction()

try {
  // bunch of SQL operations here
  database.setTransactionSuccessful()
}
finally {
  database.endTransaction()
}

This approach lets you place transaction logic outside the DAO when that better matches your architecture. The trade-off is a bit more boilerplate.

Summary