Other DAO Operations

To get data out of a database, generally it is useful to put data into it. We have seen basic @Insert, @Update, and @Delete DAO functions on NoteStore:

package com.commonsware.room.notes

import androidx.room.*

interface NoteStore {
  @Query("SELECT * FROM notes")
  fun loadAll(): List<NoteEntity>

  fun insert(note: NoteEntity)

  fun update(note: NoteEntity)

  fun delete(vararg notes: NoteEntity)

Generally speaking, these scenarios are simpler than @Query. The @Insert, @Update, and @Delete set up simple functions for inserting, updating, or deleting entities passed to their functions… and that is pretty much it. However, there are a few additional considerations that we should explore.


@Insert, @Update, and @Delete work with entities. In the above code, insert() and update() each take a single entity. delete() takes a vararg of entities, so you can pass one or several as you see fit.

You can also have a List of entities, as we saw in the insert() function in AggregateEntity:

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)
) {
  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

    fun insert(entities: List<AggregateEntity>)

Return Values

Frequently, you just 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 property. This maps to SQLite’s ON CONFLICT clause and indicates what should happen if there is either a uniqueness violation (e.g., duplicate primary keys) or a NOT NULL violation when the insert or update should occur.

The value of onConflict is an OnConflictStrategy enum:

Value Meaning
OnConflictStrategy.ABORT Cancels this statement but preserves prior results in the transaction and keeps the transaction alive
OnConflictStrategy.FAIL Like ABORT, but accepts prior changes by this specific statement (e.g., if we fail on the 50th row to be updated, keep the changes to the preceding 49)
OnConflictStrategy.IGNORE Like FAIL, but continues processing this statement (e.g., if we fail on the 50th row out of 100, keep the changes to the other 99)
OnConflictStrategy.REPLACE For uniqueness violations, deletes other rows that would cause the violation before executing this statement
OnConflictStrategy.ROLLBACK Rolls back the current transaction

The default strategy for @Insert and @Update is ABORT.

We will explore these conflict strategies in greater detail much later in the book.

Other Operations

The primary problem with @Insert, @Update, and @Delete is that they need entities. In part, that is so the DAO function knows what table to work against.

For anything else, use @Query. @Query not only works with operations that return result sets, but with any SQL that you wish to execute, even if that SQL does not return a result set.

So, for example, you could have:

@Query("DELETE FROM aliens")
fun nukeFromOrbit()  // it's the only way to be sure

…or INSERT INTO ... SELECT FROM ... syntax, or pretty much any other combination that cannot be supported directly by @Insert, @Update, and @Delete annotations.

Consider @Insert, @Update, and @Delete to be “convenience annotations” for entity-based operations, where @Query is the backbone for your DAO functions.

Prev Table of Contents Next

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