skip to content

DAOs and Queries

DAO methods built from @Insert, @Update, @Delete and @Query, conflict strategies and @RawQuery, checked against the schema at compile time. That check is Room's case over hand-written SQL.

on this pageshow

explore

questions

5

In Room, how do @Insert, @Update, @Delete and @Query DAO functions differ, and what can each one return?

level: juniorimportance: must knowfreq 78%

answer

  1. generated implementation of your interface
  2. shortcuts take entity objects
  3. primary key picks the row
  4. rowid for inserts, counts for writes
  5. a WHERE clause needs @Query

basics

~20 s

@Insert, @Update and @Delete take entity objects and Room writes the SQL, matching rows by primary key; @Query runs SQL you write. Inserts can return the new rowid as Long, updates and deletes an Int row count.

solid answer

~40 s

A `@Dao` is an interface or abstract class whose implementation Room generates at build time. The shortcut annotations `@Insert`, `@Update` and `@Delete` take entities (or lists, arrays or varargs of them) and write the statement for you; `@Update` and `@Delete` find the row by **primary key** and change nothing when no row has that key. Return types follow SQLite: `@Insert` can return the new `rowid` as `Long` (`List<Long>` or `LongArray` for several), `@Update` and `@Delete` an `Int` count. Anything with a condition, such as deleting every archived note or finding notes by tag, is a `@Query`: a `SELECT` maps rows into an entity, a data class, a `List` or a nullable single object, and an `UPDATE` or `DELETE` can return the affected count.

code

kotlin · 37 lines
kotlin
import androidx.room3.Dao
import androidx.room3.Delete
import androidx.room3.Entity
import androidx.room3.Insert
import androidx.room3.PrimaryKey
import androidx.room3.Query
import androidx.room3.Update

@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val body: String,
    val tag: String,
    val archived: Boolean = false,
)

@Dao
interface NoteDao {
    @Insert
    suspend fun insert(note: Note): Long

    @Update
    suspend fun update(note: Note): Int

    @Delete
    suspend fun delete(note: Note): Int

    @Query("DELETE FROM notes WHERE archived = 1")
    suspend fun deleteArchived(): Int

    @Query("SELECT * FROM notes WHERE id = :id")
    suspend fun findById(id: Long): Note?

    @Query("SELECT * FROM notes WHERE tag = :tag ORDER BY title")
    suspend fun findByTag(tag: String): List<Note>
}

go deeper

for a junior

Recall the four annotations, that @Update and @Delete match by primary key, and the return values: rowid as Long for inserts, Int counts for updates and deletes.

for a middle

Explain why a bulk delete by condition needs @Query, how :name parameters bind, and how Room maps a SELECT into an entity, a data class or a list.

for a senior

Show judgment on return types: nullable single rows for lookups, counts used to detect a missed update, partial-entity updates, and DAOs split by the tables they touch.

for a principal

Frame DAOs as the one reviewed boundary where every SQL statement in the app lives, which is what makes the build-time checks and the generated mapping worth having.

## What a DAO is A **DAO** (data access object) is where a Room app declares every read and write it makes. You write an interface or an abstract class, annotate it with `@Dao`, and expose it from your `@Database` class through an abstract function such as `abstract fun noteDao(): NoteDao`. At build time Room's annotation processor generates the implementation: the SQL statements, the code that binds arguments, and the code that turns result rows back into objects. You never write that glue by hand, and that is most of what Room adds over a raw `SQLiteOpenHelper`. A DAO function belongs to one of two families: - **Shortcut functions**, annotated `@Insert`, `@Update`, `@Delete` or `@Upsert`, where Room writes the SQL from the entity you pass. - **Query functions**, annotated `@Query`, where you write the SQL and Room checks it against the schema while compiling. Room's own guidance is to keep several DAOs, split by the tables they access, rather than one DAO for the whole database. ## The shortcut annotations Every parameter of a shortcut function must be an `@Entity` class, or a collection, array or vararg of one. Room derives the table from the entity and the statement from the annotation. 1. `@Insert` writes each object as a new row. By default a clash with an existing primary key or unique index aborts the statement and throws; the `onConflict` argument changes that. 2. `@Update` finds each row **by primary key** and overwrites its columns with the object's values. When no row has that key, Room changes nothing. 3. `@Delete` also matches **by primary key** and removes the row; an unmatched key is again a silent no-op. 4. `@Upsert` inserts when the key is new and updates when it already exists. Because `@Update` and `@Delete` look only at the primary key of the entity you pass, they cannot express "delete everything archived" or "retag every note tagged work". Those need a condition, and a condition means `@Query`. ## @Query for everything else `@Query` accepts four statement kinds: `SELECT`, `INSERT`, `UPDATE` and `DELETE`. Function parameters bind to `:name` placeholders in the SQL, so `@Query("SELECT * FROM notes WHERE tag = :tag")` takes a `tag: String` parameter and Room binds it as a value, never pasting it into the SQL text. For a `SELECT`, Room reads the declared return type and generates the mapping: a whole entity, a data class holding a subset of columns, a single column value, or a `List` or `Array` of any of those. In a notes app the DAO ends up as a mix: `insert(note)` and `update(note)` as shortcuts, `deleteArchived()` as `@Query("DELETE FROM notes WHERE archived = 1")`, and `findById(id)` and `findByTag(tag)` as `SELECT` queries. ## Return types at a glance | Function | Plain return type | What the value means | |---|---|---| | `@Insert`, one entity | `Long` | the new row's `rowid` | | `@Insert`, several entities | `List<Long>` or `LongArray` | one `rowid` per object | | `@Update`, `@Delete` | `Int` | rows updated or deleted | | `@Query` with `INSERT` | `Long` | `rowid` of the inserted row | | `@Query` with `UPDATE` or `DELETE` | `Int` | rows affected, SQLite's `changes()` | | `@Query` with `SELECT` | entity, data class, column value, `List`, `Array` | the mapped result | Each write can also return `Unit` when you do not need the number. For a lookup that can miss, declare the single-row result nullable (`Note?`) so an empty result comes back as `null`. A `@Query` `INSERT` that adds several rows can still report only one `rowid`, so avoid declaring `Long` on such a statement. ## Where the call runs These plain return types are the synchronous face of a DAO. **Room 3.0** accepts them only from `suspend` functions. **Room 2.x** also allowed ordinary blocking functions, which Room refuses to run on the main thread unless the builder calls `allowMainThreadQueries()`. Observable return types such as `Flow` are a separate subject, covered with Room's coroutine support. ## Common mistakes - Expecting `@Delete` or `@Update` to match on columns other than the primary key when you pass a full entity. - Treating a `0` from `@Update` as a failure: it only means that no row had that key. - Building SQL strings in app code instead of binding `:name` parameters in a `@Query`. - Declaring a non-null single-row return for a lookup that may legitimately find nothing. - Expecting `@Insert` to return the number of rows written: it returns `rowid` values, one per object.

  • Why does an @Insert function return a rowid rather than "the id" in every case?
    Room returns what SQLite reports for the new row: its `rowid`. When the entity's primary key is a single integer column, such as `@PrimaryKey(autoGenerate = true) val id: Long`, that column is the `rowid`, so the value is the new id. With a text or composite primary key the `rowid` is a separate internal number, so it is not your key.
  • How do you change only a note's title without loading the whole row first?
    Write `@Query("UPDATE notes SET title = :title WHERE id = :id")` returning `Int`, or declare `@Update(entity = Note::class)` with a partial data class holding the primary key and just the title. With the partial class Room updates only the columns that class carries, on the row whose key matches.
  • When would you make a DAO an abstract class instead of an interface?
    When it needs functions with bodies, for example a `@Transaction` function that calls two of the DAO's own functions in one transaction, or when it wants the `RoomDatabase` passed as its only constructor parameter. When every function is a plain annotated declaration, an interface is the simpler default.

  • @Delete with a Note object removes rows whose title and body match it.
  • @Update inserts the note when its primary key is not in the table yet.
  • @Insert returns how many rows it inserted.
  • A 0 returned from @Update means the database threw an error.
  • Every write has to be a @Query; the shortcut annotations are only for tests.
open as a page

In Room, what does @Insert(onConflict = OnConflictStrategy.REPLACE) do to an existing row with the same key, and how does @Upsert differ?

level: middleimportance: must knowfreq 66%

basics

~20 s

REPLACE resolves the clash by deleting the existing row and inserting the new one, so ON DELETE CASCADE actions fire. @Upsert tries the insert and, on a uniqueness conflict, updates the row by primary key, returning -1 for updated rows.

open as a page

In Room, what does the annotation processor verify about a @Query's SQL, its :name parameters and its return type at build time?

level: middleimportance: should knowfreq 55%

basics

~20 s

Room prepares each @Query against the schema its entities define, so unknown tables or columns and SQL syntax errors fail the build; it binds :name placeholders to same-named parameters and checks result columns against the return type.

open as a page

In Room, what does @Transaction do on a DAO function with a body, and on which DAO functions is it pointless?

level: seniorimportance: should knowfreq 45%

basics

~20 s

On a DAO function with a body, @Transaction runs the whole body in one database transaction, committed unless an exception escapes. On @Insert, @Update, @Delete and write @Query functions it adds nothing: each already runs in a transaction.

open as a page