Room queries (Insert, Update, Delete, Select) in Android Kotlin

Introduction

Room queries let your app save, change, remove, and get data from a database easily. This helps your app remember things even after closing.

Saving user notes or messages in an app

Updating user profile information like name or email

Deleting old or unwanted data like completed tasks

Fetching a list of saved items to show on screen

Syntax

Android Kotlin

/* Insert */
@Insert
fun insertItem(item: Item)

/* Update */
@Update
fun updateItem(item: Item)

/* Delete */
@Delete
fun deleteItem(item: Item)

/* Select */
@Query("SELECT * FROM item_table")
fun getAllItems(): List<Item>

Use @Insert to add new data to the database.

Use @Update to change existing data, matching by primary key.

Use @Delete to remove data by matching the object.

Use @Query with SQL to get data; you can write any SELECT statement.

Examples

Adds a new user to the database.

Android Kotlin

@Insert
fun addUser(user: User)

Updates user details already saved.

Android Kotlin

@Update
fun updateUser(user: User)

Removes the user from the database.

Android Kotlin

@Delete
fun deleteUser(user: User)

Gets all users older than 18.

Android Kotlin

@Query("SELECT * FROM user_table WHERE age > 18")
fun getAdultUsers(): List<User>

Sample App

This example shows how to insert, update, delete, and select users in a Room database using Kotlin. The User data class defines the table. UserDao interface has methods with Room annotations to perform database actions.

Android Kotlin

import androidx.room.*

@Entity(tableName = "user_table")
data class User(
  @PrimaryKey val id: Int,
  val name: String,
  val age: Int
)

@Dao
interface UserDao {
  @Insert
  fun insertUser(user: User)

  @Update
  fun updateUser(user: User)

  @Delete
  fun deleteUser(user: User)

  @Query("SELECT * FROM user_table")
  fun getAllUsers(): List<User>
}

// Usage example (not full app):
fun main() {
  val userDao: UserDao = TODO("Get UserDao from Room database")

  val user = User(1, "Alice", 25)
  userDao.insertUser(user) // Save user

  val users = userDao.getAllUsers() // Get all users
  println(users)

  val updatedUser = user.copy(age = 26)
  userDao.updateUser(updatedUser) // Update age

  userDao.deleteUser(updatedUser) // Remove user
}

Important Notes

Room database operations should be run on a background thread to keep the app smooth.

Always use suspend functions or RxJava for queries in real apps to avoid blocking the main thread.

Primary key is important to identify which row to update or delete.

Summary

Use @Insert, @Update, @Delete annotations for basic data changes.

Use @Query with SQL to read data from the database.

Room helps keep your data safe and easy to manage inside your app.

Practice

1. Which Room annotation is used to add a new record to the database?

easy

A. @Query

B. @Insert

C. @Delete

D. @Update

Solution

1. Step 1: Understand Room annotations for data changes

   @Insert is used to add new data, @Update modifies existing data, and @Delete removes data.

2. Step 2: Identify the annotation for adding records

   Since adding means inserting new data, @Insert is the correct annotation.

3. Final Answer:

   @Insert -> Option B

4. Quick Check:

   Add new record = @Insert [OK]

Hint: Insert means add new data, so use @Insert [OK]

Common Mistakes:

• Confusing @Update with adding new data
• Using @Delete instead of @Insert
• Thinking @Query inserts data

2. Which of the following is the correct syntax to delete a user by ID using @Query in Room?

easy

A. @Query("DELETE users WHERE id = :userId")

B. @Query("REMOVE FROM users WHERE id = :userId")

C. @Query("DELETE FROM users WHERE id = :userId")

D. @Query("DELETE * FROM users WHERE id = :userId")

Solution

1. Step 1: Recall SQL DELETE syntax

   The correct SQL command to delete rows is DELETE FROM table WHERE condition.

2. Step 2: Match correct syntax in options

   @Query("DELETE FROM users WHERE id = :userId") matches the correct SQL syntax with parameter binding (:userId).

3. Final Answer:

   @Query("DELETE FROM users WHERE id = :userId") -> Option C

4. Quick Check:

   DELETE FROM table WHERE condition = correct syntax [OK]

Hint: Use 'DELETE FROM' not 'REMOVE' or 'DELETE *' [OK]

Common Mistakes:

• Using REMOVE instead of DELETE
• Omitting FROM keyword
• Adding * after DELETE

3. Given the DAO method below, what will be the return type when calling getAllUsers()?

@Query("SELECT * FROM users")
fun getAllUsers(): List<User>

medium

A. An integer count of users

B. A single User object

C. A Boolean indicating success

D. A list of User objects

Solution

1. Step 1: Analyze the method signature

   The method returns List<User>, which means a list of User objects.

2. Step 2: Understand the SQL query

   SELECT * FROM users returns all rows, so the method returns multiple User objects in a list.

3. Final Answer:

   A list of User objects -> Option D

4. Quick Check:

   Return type List<User> means multiple users [OK]

Hint: Return type List<User> means multiple users returned [OK]

Common Mistakes:

• Thinking it returns a single User
• Confusing return type with count
• Expecting a Boolean result

4. Identify the error in this Room DAO update method:

@Update
fun updateUser(user: User): Int {
  return 1
}

medium

A. The method should not have a body; it must be abstract

B. The method should return Unit, not Int

C. The method must not have a return statement

D. The @Update annotation is missing parameters

Solution

1. Step 1: Understand Room DAO method requirements

   DAO methods with @Update must be abstract or interface methods without a body.

2. Step 2: Identify the error in the method

   This method has a body with a return statement, which is not allowed for Room DAO annotated methods.

3. Final Answer:

   The method should not have a body; it must be abstract -> Option A

4. Quick Check:

   Room DAO methods are abstract, no method body [OK]

Hint: Room DAO methods must be abstract, no method body [OK]

Common Mistakes:

• Adding method body in DAO interface
• Returning wrong types intentionally
• Missing @Update annotation

5. You want to update a user's email by their ID using Room. Which DAO method below correctly performs this update?

hard

A. @Query("UPDATE users SET email = :email WHERE id = :userId")
fun updateEmail(userId: Int, email: String)

B. @Update
fun updateEmail(userId: Int, email: String)

C. @Insert(onConflict = OnConflictStrategy.REPLACE)
fun updateEmail(userId: Int, email: String)

D. @Delete
fun updateEmail(userId: Int, email: String)

Solution

1. Step 1: Understand update options in Room

   @Update requires a full entity object, so partial updates need @Query with SQL.

2. Step 2: Check which method updates email by ID

   @Query("UPDATE users SET email = :email WHERE id = :userId") fun updateEmail(userId: Int, email: String) uses @Query with SQL UPDATE statement and parameters for userId and email, which is correct.

3. Final Answer:

   @Query("UPDATE users SET email = :email WHERE id = :userId") fun updateEmail(userId: Int, email: String) -> Option A

4. Quick Check:

   Partial update needs @Query with SQL UPDATE [OK]

Hint: Use @Query with UPDATE for partial field changes [OK]

Common Mistakes:

• Using @Update without full entity
• Using @Insert to update data
• Using @Delete for updates