15 - Database

Room & Flow - modern

The Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.

Compile-time verification of SQL queries.
Convenience annotations that minimize repetitive and error-prone boilerplate code.
Streamlined database migration paths.

// project build.gradle
plugins {
    alias(libs.plugins.ksp) apply false
    alias(libs.plugins.compose.compiler) apply false
    id("androidx.room") version "2.8.2"
    id("com.google.devtools.ksp") version "2.0.21-1.0.27" apply false
}


// app build.gradle
dependencies {
    val room_version = "2.8.2"

    implementation("androidx.room:room-runtime:$room_version")

    ksp("androidx.room:room-compiler:$room_version")

    implementation("androidx.room:room-ktx:$room_version")

    // optional - Guava support for Room, including Optional and ListenableFuture
    implementation("androidx.room:room-guava:$room_version")

    // optional - Test helpers
    testImplementation("androidx.room:room-testing:$room_version")

    // optional - Paging 3 Integration
    implementation("androidx.room:room-paging:$room_version")
}

Entity

Our data model - annotated with @Entity.
You can override all the names - @Entity(tableName = "users"), @ColumnInfo(name = "first_name").
One field must be primary key.
Ignore fields with @Ignore.
Indexes are possible with @Entity(indices = [Index(value = ["last_name", "address"], unique = true)]).

package ee.taltech.test20251016_01.dal

import androidx.room.Entity
import androidx.room.PrimaryKey

// Adding @Entity here tells Room that this will be a table in our database
@Entity
data class DataModel(
    // We can add attributes to the fields of our database using the @PrimaryKey annotation
    // the PrimaryKey annotation here tells our database that this column the primary key
    // and is therefore guaranteed to be unique
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val text: String,
    val priority: Int
)

enum class Operator {
    GREATER_THAN,
    LESS_THAN
}

// the fields in our table that we may want to sort on
enum class Sort {
    TEXT,
    PRIORITY
}

// should we sort in ascending or descending order
enum class Order {
    ASC,
    DESC
}

DAO

Room provides convenience annotations for defining methods that perform simple insertions, updates, and deletions without requiring you to write a SQL statement.

Insert
Update
Delete
Upsert

The @Query annotation lets you write SQL statements and expose them as DAO methods.

import androidx.room.Dao
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Upsert
import androidx.sqlite.db.SupportSQLiteQuery
import kotlinx.coroutines.flow.Flow

//Data Access Object
@Dao
interface DataModelDao {
    // Upsert means that this will either insert the object if it doesn't exist in the database
    // or update the object if it does already exists in the database
    @Upsert
    suspend fun upsertDataModel(dataModel: DataModel)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertDataModels(vararg dataModels: DataModel)

    @Update
    fun updateDataModels(vararg dataModels: DataModel)

    @Query("SELECT * FROM datamodel")
    fun getAllRecords(): Flow<List<DataModel>>

    @Query("SELECT * FROM datamodel ORDER BY priority ASC")
    fun sortByPriority(): Flow<List<DataModel>>

    @Query("SELECT * FROM datamodel ORDER BY text ASC")
    fun sortByText(): Flow<List<DataModel>>

    @Query("SELECT * FROM datamodel WHERE priority < :number")
    fun filterPriorityLessThan(number: Int): Flow<List<DataModel>>

    @Query("SELECT * FROM datamodel WHERE priority > :number")
    fun filterPriorityGreaterThan(number: Int): Flow<List<DataModel>>

    @RawQuery(observedEntities = [DataModel::class])
    fun query(query: SupportSQLiteQuery): Flow<List<DataModel>>
}

Relationships - 1:0-1, 1-m

1:0-1

@Entity
data class User(
    @PrimaryKey val userId: Long,
    val name: String,
    val age: Int
)

@Entity
data class Library(
    @PrimaryKey val libraryId: Long,
    val userOwnerId: Long
)


data class UserAndLibrary(
    @Embedded val user: User,
    @Relation(
         parentColumn = "userId",
         entityColumn = "userOwnerId"
    )
    val library: Library
)

// in dao
@Transaction
@Query("SELECT * FROM User")
fun getUsersAndLibraries(): List<UserAndLibrary>

1-m

@Entity
data class User(
    @PrimaryKey val userId: Long,
    val name: String,
    val age: Int
)

@Entity
data class Playlist(
    @PrimaryKey val playlistId: Long,
    val userCreatorId: Long,
    val playlistName: String
)

data class UserWithPlaylists(
    @Embedded val user: User,
    @Relation(
          parentColumn = "userId",
          entityColumn = "userCreatorId"
    )
    val playlists: List<Playlist>
)

// in dao
@Transaction
@Query("SELECT * FROM User")
fun getUsersWithPlaylists(): List<UserWithPlaylists>

In Room, there are two ways to define and query a relationship between entities. You can use either:

An intermediate data class with embedded objects, or
A relational query method with a multimap return type.

Intermediate

@Dao
interface UserBookDao {
    @Query(
        "SELECT user.name AS userName, book.name AS bookName " +
        "FROM user, book " +
        "WHERE user.id = book.user_id"
    )
    fun loadUserAndBookNames(): LiveData<List<UserBook>>
}

data class UserBook(val userName: String?, val bookName: String?)

Multimap

@Query(
    "SELECT * FROM user" +
    "JOIN book ON user.id = book.user_id"
)
fun loadUserAndBookNames(): Map<User, List<Book>>

Dao vs Repository - what?

Android has it's own idea about it...

DAO forms the main component of the Room Persistence Library. We use queries to perform CRUD operations on the database(Insert,Update,Delete and Create). And DAO is the place, where we add such queries to perform operations. Inside a DAO we define methods.

On the other hand, a repository class is something that abstracts access to multiple data sources.

A Data Access Object (DAO) defines the interface for how to perform CRUD operations on a particular entity. A Repository can have several DAOs.

DAO focuses on database operations (insert, update, delete), while Repository aligns with business logic in DDD.

Database

The class should be an abstract class and extend androidx.room.RoomDatabase. Do not use directly, use daos.

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(entities = [DataModel::class], version = 1)
abstract class DataModelDatabase: RoomDatabase() {
    abstract val dao: DataModelDao
}

Migration

@Database(
   version = MusicDatabase.LATEST_VERSION,
   entities = [
       Song.class,
       Artist.class
   ],
   autoMigrations = [
       AutoMigration (
           from = 1,
           to = 2
       ),
       AutoMigration (
           from = 2,
           to = 3,
           spec = MusicDatabase.MyExampleAutoMigration::class
       )
   ],
   exportSchema = true
)
abstract class MusicDatabase  : RoomDatabase() {
   const val LATEST_VERSION = 3

   @DeleteTable(deletedTableName = "Album")
   @RenameTable(fromTableName = "Singer", toTableName = "Artist")
   @RenameColumn(
       tableName = "Song",
       fromColumnName = "songName",
       toColumnName = "songTitle"
    )
   @DeleteColumn(fromTableName = "Song", deletedColumnName = "genre")
   class MyExampleAutoMigration : AutoMigrationSpec {
       @Override
       override fun onPostMigrate(db: SupportSQLiteDatabase) {
           // Invoked once auto migration is done
       }
    }
}

Viewmodel

import androidx.lifecycle.ViewModel
import androidx.lifecycle.viewModelScope
import androidx.sqlite.db.SimpleSQLiteQuery
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.launch

class DataModelViewModel(
    private val dao: DataModelDao
): ViewModel() {
    fun addData() {
        viewModelScope.launch {
            (0..10).forEach { _ ->
                val newDataToAdd = DataModel(
                    text = "Task ${(0..10).random()}",
                    priority = (0..100).random()
                )
                dao.upsertDataModel(newDataToAdd)
            }
        }
    }

    fun getAllRecords(): Flow<List<DataModel>> {
        return dao.getAllRecords()
    }

    fun sortBy(sort: Sort): Flow<List<DataModel>> {
        if (sort == Sort.TEXT) {
            return dao.sortByText()
        }

        return dao.sortByPriority()
    }

    fun filterPriority(operator: Operator, number: Int): Flow<List<DataModel>> {
        if (operator == Operator.LESS_THAN) {
            return dao.filterPriorityLessThan(number)
        }
        return dao.filterPriorityGreaterThan(number)
    }


    fun query(
        tableName: String = "datamodel",
        filter: String? = null,
        sort: Sort? = null,
        order: Order = Order.ASC
    ): Flow<List<DataModel>> {
        var queryText = "SELECT * FROM $tableName"

        if (filter != null) {
            queryText += " WHERE $filter"
        }

        if (sort != null) {
            queryText += " ORDER BY ${sort.name}"
            queryText += " ${order.name}"
        }

        return dao.query(SimpleSQLiteQuery(queryText))
    }
}

App

import android.os.Bundle
import androidx.activity.ComponentActivity
import androidx.activity.compose.setContent
import androidx.activity.enableEdgeToEdge
import androidx.activity.viewModels
import androidx.compose.foundation.layout.Column
import androidx.compose.foundation.layout.Row
import androidx.compose.foundation.layout.Spacer
import androidx.compose.foundation.layout.fillMaxSize
import androidx.compose.foundation.layout.padding
import androidx.compose.foundation.lazy.LazyColumn
import androidx.compose.foundation.lazy.items
import androidx.compose.material3.Button
import androidx.compose.material3.MaterialTheme
import androidx.compose.material3.Scaffold
import androidx.compose.material3.Surface
import androidx.compose.material3.Text
import androidx.compose.runtime.Composable
import androidx.compose.runtime.collectAsState
import androidx.compose.ui.Modifier
import androidx.compose.ui.tooling.preview.Preview
import androidx.compose.ui.unit.dp
import androidx.lifecycle.ViewModel
import androidx.lifecycle.ViewModelProvider
import androidx.room.Room
import kotlin.getValue

class MainActivity : ComponentActivity() {

    private val db by lazy {
        Room.databaseBuilder(
            context = applicationContext,
            klass = DataModelDatabase::class.java,
            name = "datamodel.db"
        ).build()
    }
    private val viewModel by viewModels<DataModelViewModel>(
        factoryProducer = {
            object : ViewModelProvider.Factory {
                override fun <T : ViewModel> create(modelClass: Class<T>): T {
                    return DataModelViewModel(db.dao) as T
                }
            }
        }
    )

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        enableEdgeToEdge()
        setContent {
            Test2025101601Theme {
                Scaffold(modifier = Modifier.fillMaxSize()) { innerPadding ->
                    Surface(
                        modifier = Modifier.padding(innerPadding).fillMaxSize(),
                        color = MaterialTheme.colorScheme.background
                    ) {
                        DatabaseListView(vm = viewModel)
                    }
                }
            }
        }
    }
}

@Composable
fun DatabaseListView(vm: DataModelViewModel) {
    //val dataList = vm.getAllRecords().collectAsState(initial = emptyList())
    //val dataList = vm.query(filter = "priority > 50").collectAsState(initial = emptyList())
    val dataList = vm.query(
        filter = "priority < 65",
        sort = Sort.TEXT,
        order = Order.ASC
    ).collectAsState(initial = emptyList())

    Column {
        Button(onClick = {
            vm.addData()
        }) {
            Text("Add Items")
        }

        LazyColumn(
            modifier = Modifier
                .fillMaxSize()
                .padding(all = 16.dp)
        ) {
            items(dataList.value) { item ->
                Row {
                    Text(item.text)
                    Spacer(Modifier.weight(1f))
                    Text(item.priority.toString())
                }
            }
        }
    }
}

Database - SQLite - OLD

Full support for SQLite

Any database will be accessible by name in any class in app

Private to your app

http://sqlite.org/docs.html

Create db

Create DB

Use SQLiteOpenHelper
Override onCreate(SQLiteDatabase db)
execute a SQLite commands to create tables in the database

The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

class DbHelper(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    companion object {
        const val DATABASE_NAME = "app.db"
        const val DATABASE_VERSION = 2

        const val PERSON_TABLE_NAME = "PERSONS"

        const val PERSON_ID = "_id"
        const val PERSON_FIRSTNAME = "firstName"
        const val PERSON_LASTNAME = "lastName"

        const val SQL_PERSON_CREATE_TABLE =
            "create table $PERSON_TABLE_NAME(" +
                    "$PERSON_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "$PERSON_FIRSTNAME TEXT NOT NULL, " +
                    "$PERSON_LASTNAME TEXT NOT NULL);"

        const val SQL_DELETE_TABLES = "DROP TABLE IF EXISTS " + 
            "$PERSON_TABLE_NAME";
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(SQL_PERSON_CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, 
        oldVersion: Int, newVersion: Int) {
        
        db?.execSQL(SQL_DELETE_TABLES)
        onCreate(db)
    }
}

Migration

Override onUpgrade(SQLiteDatabase? db, int oldVersion, int newVersion)

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

NB! SQLite does not support dropping columns!

Create new table, copy data, drop old table, rename table, etc...

Open DB

Get an instance of your SQLiteOpenHelper implementation using the constructor you've defined
To write to db - getWritableDatabase()
Read from the db – getReadableDatabase()
Both return a SQLiteDatabase object, providing methods for SQLite operations.

class PersonRepository(val context: Context) {

    private lateinit var dbHelper : DbHelper
    private lateinit var db: SQLiteDatabase

    fun open(): PersonRepository {
        dbHelper = DbHelper(context)
        db = dbHelper.writableDatabase
        return this
    }

    fun close(){
        dbHelper.close()
    }

Data types

NULL
INTEGER
REAL
TEXT - database encoding (UTF-8, UTF-16BE or UTF-16LE)
BLOB
Everything else is mapped into one of these types
- Boolean – int 0/1
- DateTime – integer, unix timestap or text
- ...

http://www.sqlite.org/datatype3.html

Date and time function

Date and time functions

date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

http://www.sqlite.org/lang_datefunc.html

Working with data

SQLiteDatabase provides

Insert
Update
Delete
execSQL
Queries can be created via
- rawQuery - directly accepts an SQL select statement as input
- query - provides a structured interface for specifying the SQL query
- SQLiteQueryBuilder - convenience class that helps to build SQL queries

rawQuey

val cursor = db.rawQuery("select * from person where _id=?", arrayOf("2"))

You may include ?-s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Query

layout

val cursor = db.query(DATABASE_TABLE, 
    arrayOf(KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION), 
    null, null, null, null, null)

If a condition is not required you can pass null, e.g. for the group by clause.
The "whereClause" is specified without the word "where", for example a "where" statement might look like: "_id=19 and summary=?"
If you specify placeholder values in the where clause via ?, you pass them as the selectionArgs parameter to the query

Cursor

A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.
To get the number of elements of the resulting query use the getCount() method.
To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached.
Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.
Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.
A Cursor needs to be closed with the close() method call.

Insert data

public long insert (String table, String nullColumnHack, ContentValues values)

table - the table to insert the row into
nullColumnHack - optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.
values - this map contains the initial column values for the row. The keys should be the column names and the values the column values

fun add(person: Person){
    val contentValues = ContentValues()
    contentValues.put(DbHelper.PERSON_FIRSTNAME, person.firstName)
    contentValues.put(DbHelper.PERSON_LASTNAME, person.lastName)
    db.insert(DbHelper.PERSON_TABLE_NAME, null, contentValues)
}

Update

public int update (String table, ContentValues values, String whereClause, String[] whereArgs)

table - the table to update in
values - a map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause - the optional WHERE clause to apply when updating. Passing null will update all rows.
whereArgs - You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

fun update(person: Person){
    val contentValues = ContentValues()
    contentValues.put(DbHelper.PERSON_FIRSTNAME, person.firstName)
    contentValues.put(DbHelper.PERSON_LASTNAME, person.lastName)
    db.update(DbHelper.PERSON_TABLE_NAME, contentValues, 
        DbHelper.PERSON_ID + "=?" , arrayOf(person.id.toString()))
}

Delete

public int delete (String table, String whereClause, String[] whereArgs)

table - the table to delete from
whereClause - the optional WHERE clause to apply when deleting. Passing null will delete all rows.
whereArgs - You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

App Data Architecture

BUT WHAT ABOUT...

Room (ORM)
LiveData (Observables)
Databinding (bind layout to data sources)
- aka Jetpack libraries

Will be in separate lecture after basics is covered

Room

Room – ORM on top of SQLite
Room provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.
In case of SQLite, there is no compile time verification of raw SQLite queries. In Room there is SQL validation at compile time.
As schema changes, you need to update the affected SQL queries manually. Room generates queries, so no manual updates.
SQLite has lot’s of boilerplate code to convert between SQL and Objects. Room has this built-in.
Room is built to work with LiveData and RxJava for data observation, while SQLite is not.

LiveData 1

LiveData is an observable data holder class.
Ensures your UI matches your data state
- LiveData follows the observer pattern. LiveData notifies Observer objects when underlying data changes. You can consolidate your code to update the UI in these Observer objects.
No memory leaks
- Observers are bound to Lifecycle objects and clean up after themselves when their associated lifecycle is destroyed.
No crashes due to stopped activities
- If the observer's lifecycle is inactive, such as in the case of an activity in the back stack, then it doesn’t receive any LiveData events.
No more manual lifecycle handling
- UI components just observe relevant data and don’t stop or resume observation.

LiveData 2

Always up to date data
- If a lifecycle becomes inactive, it receives the latest data upon becoming active again. For example, an activity that was in the background receives the latest data right after it returns to the foreground.
Proper configuration changes
- If an activity or fragment is recreated due to a configuration change, like device rotation, it immediately receives the latest available data.
Sharing resources
- You can extend a LiveData object using the singleton pattern to wrap system services so that they can be shared in your app.

Data binding

The Data Binding Library is a support library that allows you to bind UI components in your layouts to data sources in your app using a declarative format rather than programmatically.

<TextView android:text="@{viewmodel.userName}" />

App Architecture

Room
LiveData
Databinding

layout

Room & Flow - modern​

Entity​

DAO​

Relationships - 1:0-1, 1-m​

Dao vs Repository - what?​

Database​

Viewmodel​

App​

Database - SQLite - OLD​

Create db​

Migration​

Open DB​

Data types​

Date and time function​

Working with data​

rawQuey​

Query​

Cursor​

Insert data​

Update​

Delete​

App Data Architecture​

BUT WHAT ABOUT...​

Room​

LiveData 1​

LiveData 2​

Data binding​

App Architecture​

Room & Flow - modern

Entity

DAO

Relationships - 1:0-1, 1-m

Dao vs Repository - what?

Database

Viewmodel

App

Database - SQLite - OLD

Create db

Migration

Open DB

Data types

Date and time function

Working with data

rawQuey

Query

Cursor

Insert data

Update

Delete

App Data Architecture

BUT WHAT ABOUT...

Room

LiveData 1

LiveData 2

Data binding

App Architecture