In Room, how do @Insert, @Update, @Delete and @Query DAO functions differ, and what can each one return?
answer
- generated implementation of your interface
- shortcuts take entity objects
- primary key picks the row
- rowid for inserts, counts for writes
- 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 sA `@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 linesimport 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
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.
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.
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.
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.