Room queries (Insert, Update, Delete, Select) in Android Kotlin - Mini App: Build & Ship

Build: Simple Notes

A screen to add, update, delete, and view notes using Room database queries.

Target UI

-------------------------
| Simple Notes          |
-------------------------
| [Enter note here]     |
| [Save] [Update]       |
|-----------------------|
| Notes List:           |
| 1. Note one           |
| 2. Note two           |
| ...                   |
-------------------------

Use Room database with an Entity called Note with fields id (auto-generated) and text.

Implement DAO with insert, update, delete, and select all queries.

Screen has an EditText to enter note text.

Save button inserts a new note.

Update button updates the selected note.

Display all notes in a scrollable list.

Tapping a note loads it into the EditText for editing.

Delete a note by long-pressing it.

Starter Code

Android Kotlin

import android.os.Bundle
import androidx.activity.ComponentActivity
import androidx.activity.compose.setContent
import androidx.compose.foundation.clickable
import androidx.compose.foundation.layout.*
import androidx.compose.foundation.lazy.LazyColumn
import androidx.compose.foundation.lazy.items
import androidx.compose.material3.*
import androidx.compose.runtime.*
import androidx.compose.ui.Modifier
import androidx.compose.ui.unit.dp
import androidx.room.*

@Entity
 data class Note(
  @PrimaryKey(autoGenerate = true) val id: Int = 0,
  val text: String
)

@Dao
interface NoteDao {
  // TODO: Add insert, update, delete, and select queries here
}

@Database(entities = [Note::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
  abstract fun noteDao(): NoteDao
}

class MainActivity : ComponentActivity() {
  override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    val db = Room.databaseBuilder(
      applicationContext,
      AppDatabase::class.java, "notes-db"
    ).build()
    val noteDao = db.noteDao()

    setContent {
      // TODO: Build UI with EditText, Save, Update buttons, and notes list
    }
  }
}

Task 1

Task 2

Task 3

Task 4

Task 5

Task 6

Task 7

Solution

Android Kotlin

import android.os.Bundle
import androidx.activity.ComponentActivity
import androidx.activity.compose.setContent
import androidx.compose.foundation.clickable
import androidx.compose.foundation.layout.*
import androidx.compose.foundation.lazy.LazyColumn
import androidx.compose.foundation.lazy.items
import androidx.compose.material3.*
import androidx.compose.runtime.*
import androidx.compose.ui.Modifier
import androidx.compose.ui.input.pointer.pointerInput
import androidx.compose.ui.unit.dp
import androidx.room.*
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.launch
import kotlinx.coroutines.withContext

@Entity
 data class Note(
  @PrimaryKey(autoGenerate = true) val id: Int = 0,
  val text: String
)

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

  @Update
  suspend fun update(note: Note)

  @Delete
  suspend fun delete(note: Note)

  @Query("SELECT * FROM Note ORDER BY id DESC")
  suspend fun getAll(): List<Note>
}

@Database(entities = [Note::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
  abstract fun noteDao(): NoteDao
}

class MainActivity : ComponentActivity() {
  override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    val db = Room.databaseBuilder(
      applicationContext,
      AppDatabase::class.java, "notes-db"
    ).build()
    val noteDao = db.noteDao()

    setContent {
      val scope = rememberCoroutineScope()
      var notes by remember { mutableStateOf(listOf<Note>()) }
      var currentNoteId by remember { mutableStateOf<Int?>(null) }
      var noteText by remember { mutableStateOf("") }

      LaunchedEffect(Unit) {
        notes = withContext(Dispatchers.IO) { noteDao.getAll() }
      }

      fun refreshNotes() {
        scope.launch {
          notes = withContext(Dispatchers.IO) { noteDao.getAll() }
        }
      }

      Scaffold(
        topBar = {
          CenterAlignedTopAppBar(title = { Text("Simple Notes") })
        },
        content = { padding ->
          Column(
            modifier = Modifier
              .padding(padding)
              .padding(16.dp)
          ) {
            OutlinedTextField(
              value = noteText,
              onValueChange = { noteText = it },
              label = { Text("Enter note here") },
              modifier = Modifier.fillMaxWidth()
            )
            Spacer(modifier = Modifier.height(8.dp))
            Row {
              Button(onClick = {
                if (noteText.isNotBlank()) {
                  scope.launch {
                    withContext(Dispatchers.IO) {
                      noteDao.insert(Note(text = noteText))
                    }
                    noteText = ""
                    currentNoteId = null
                    refreshNotes()
                  }
                }
              }) {
                Text("Save")
              }
              Spacer(modifier = Modifier.width(8.dp))
              Button(onClick = {
                val id = currentNoteId
                if (id != null && noteText.isNotBlank()) {
                  scope.launch {
                    withContext(Dispatchers.IO) {
                      noteDao.update(Note(id = id, text = noteText))
                    }
                    noteText = ""
                    currentNoteId = null
                    refreshNotes()
                  }
                }
              }) {
                Text("Update")
              }
            }
            Spacer(modifier = Modifier.height(16.dp))
            Text("Notes List:", style = MaterialTheme.typography.titleMedium)
            Spacer(modifier = Modifier.height(8.dp))
            LazyColumn {
              items(notes) { note ->
                Text(
                  text = note.text,
                  modifier = Modifier
                    .fillMaxWidth()
                    .clickable {
                      noteText = note.text
                      currentNoteId = note.id
                    }
                    .pointerInput(Unit) {
                      detectTapGestures(
                        onLongPress = {
                          scope.launch {
                            withContext(Dispatchers.IO) {
                              noteDao.delete(note)
                            }
                            if (currentNoteId == note.id) {
                              noteText = ""
                              currentNoteId = null
                            }
                            refreshNotes()
                          }
                        }
                      )
                    }
                    .padding(8.dp),
                  style = MaterialTheme.typography.bodyLarge
                )
                Divider()
              }
            }
          }
        }
      )
    }
  }
}

This solution uses Room to store notes with an auto-generated ID and text field.

The NoteDao interface defines suspend functions for insert, update, delete, and select all notes using Room annotations.

The Compose UI has an OutlinedTextField for entering note text, and two buttons: Save and Update.

Save inserts a new note if text is not blank. Update modifies the selected note by ID.

The notes are displayed in a scrollable list using LazyColumn. Clicking a note loads it into the text field for editing.

Long pressing a note deletes it from the database.

Coroutines run database operations off the main thread to keep UI smooth.

Final Result

Completed Screen

-------------------------
| Simple Notes          |
-------------------------
| [Enter note here_____]|
| [Save] [Update]       |
|-----------------------|
| Notes List:           |
| 1. Buy groceries      |
| 2. Call mom           |
| 3. Read book          |
-------------------------

User types text in the input field.

Tapping Save adds a new note to the list below.

Tapping a note loads its text into the input field for editing.

Tapping Update saves changes to the selected note.

Long pressing a note deletes it from the list.

Stretch Goal

Add a confirmation dialog before deleting a note on long press.

💡 Hint

Use Compose's AlertDialog composable to ask user to confirm deletion.

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