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.*

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

  @Insert
  fun insert(note: NoteEntity)

  @Update
  fun update(note: NoteEntity)

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

Parameters

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

Return Values

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

However:

• For @Update and @Delete, you can have them return an Int, which will be the number of rows affected by the update or delete operation
• For an @Insert function accepting a single entity, you can have it return a Long which will be the ROWID of the entity (and, if you are using an auto-increment int as your primary key, this will also be that key)
• For an @Insert function accepting multiple entities, you can have it return a LongArray or a List of Long values, being the corresponding ROWID values for those inserted entities

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:

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.