Interact with a database in your project

Android  database  room 

In this lesson, we will learn how to use a database in our application. For the moment we need an Internet connection to display data on our screens, but we want to be able to display something when a user is offline or when the remote API is not able to answer.

android use room

Jetpack Room

Each Android phone has a local SQLite database.

The Jetpack Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite. Room library is an ORM (Object Relational Mapping) library (like Hibernate for the backend development). It allows you to manipulate objects in your code and to persist them in a database.

You have other libraries to do the same things but Room has the advantage of being provided and created by the Google team.


An entity is a Kotlin class binded to a database table. With Room, each table is represented by a class.

An entity contains the fields of the table as properties. Each instance of an entity represents a row of the table.

SQLite database has very few types :

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

When you use Jetpack Room, you work with Kotlin objects, and the library handles the conversion to and from the database.

Mapping is based on the names of the variables in the model class, and the names of the columns in the database. If the names don’t match, you can use annotations to define the mapping yourself. You can also use annotations to define primary keys, autoincrementing values, and other aspects of your database.

We have to define several things

  • to be an entity the class must be annotated with @Entity (This annotation accept a property tableName to personalize the name)

  • each table need a unique id, the primary key. You can mark it with the @PrimaryKey annotation and tell if the value will be generated by the database

  • each column must be declared with a @ColumnInfo annotation. The column name can be overridden.

Typically, SQL column names will have words separated by an underscore, as opposed to the lowerCamelCase used by Kotlin properties.

You can use the @Ignore annotation to tell Room to ignore specific fields. For example, you may want to ignore a field that is used only in logic within the app, but is not stored or referenced in the database.

If we use the building sensor API we can see the Window Entity. The window table includes some basic information about a room window.

@Entity(tableName = "rwindow")
data class Window(
    @PrimaryKey(autoGenerate = true) val id: Long,
    @ColumnInfo val name: String,
    @ColumnInfo(name = "room_id") val roomId: Long,
    @ColumnInfo(name = "room_name") val roomName: String,
    @ColumnInfo(name = "window_status") val windowStatus: WindowStatus,
    @Ignore val windows: MutableList<Window> = mutableListOf()
) {
    // When you need to transform your entity in a DTO (Data Transfer Object) you can use this method
    fun toDto(): WindowDto =
        WindowDto(id.toLong(), name, RoomDto(roomId.toLong(), roomName, null, null), windowStatus)

In this code we used an enum WindowStatus, but this enum is not a known type in the database. We should help Rooms to serialize and deserialize this enum value. Create in the package com.automacorp.model a new class EnumConverters.

class EnumConverters {

    // A first method to convert enum in string when the data will be stored in the database
    fun fromWindowStatus(value: WindowStatus?): String? {
        return value?.toString()

    // A second one to do the inverse operation
    fun toWindowStatus(value: String?): WindowStatus? {
        return value?.let { WindowStatus.valueOf(it) }


With this class we can use the annotation @TypeConverters to tell Room to use this class to convert our enum when the data will be stored or read in the database.

Data Object Access DAO

A DAO (Data Access Object) is a Kotlin class that provides access to the data. We will define functions for reading or manipulating data. Each function call will perform a SQL command on the database.

With Jetpack Room, a Dao is an interface with annotated methods. The implementation of these methods is not written by you. The Room library generates the code to execute these queries from yours interfaces.

If you followed the Spring Data labs, Room is like Spring and it will generate the interface implementation at compile time.

To activate this mechanism you need to add the annotation @Dao on your class

interface WindowDao {

A query is specified as a string passed into a @Query annotation.

Contrary to Hibernate for backend developpers, we won’t manipulate objets in these queries but we have to use SQL request with the database model.

Room provides also different annotations @Insert, @Update, @Delete to manipulate an entity.

interface WindowDao {
    @Query("select * from rwindow order by name")
    fun findAll(): List<Window>

    @Query("select * from rwindow where id = :windowId")
    fun findById(windowId: Long): Window

    suspend fun create(window: Window)

    suspend fun update(window: Window): Int

    suspend fun delete(window: Window)

    @Query("delete from rwindow")
    suspend fun clearAll()

In the second example we use a function argument in the request.

Create a database

We now need to configure the database in our project. With Jetpack Room library we have to initialize an object that implements the RoomDatabase interface. We had to declare on this object, the different entities, the converters and their DAOs.

  • @Database annotation is used to declare all entities. The version number is incremented each time you make a schema change. The app checks this version with the one in the database to determine if and how a migration should be performed.

  • @TypeConverters annotation is used to declare all type converters (enum convertion for example).

  • the class is also used to declare all DAOs.

@Database(entities = [Window::class], version = 1)
abstract class AutomacorpDatabase : RoomDatabase() {
    abstract fun windowDao(): WindowDao

Use Singleton in an Android app

Now you need to use this database in your code. And you need to use only one instance of this database.

We need to declare a singleton. A singleton is a class that can have only one instance of the class at a time. We have to do that to prevent race conditions or other potential issues.

To resolve this problem you can use a dependency injection libray as Hilt.

Or you can define your own Android Application class and use it to store the database instance. An Android Application object is created when you launch your application, and it will be destroyed when your application is terminated.

Create a new class AutomacorpApplication in the root folder of your project. This class must extends Application class.

class AutomacorpApplication : Application() {}

You need to declare this new class in AndroidManifest.xml to launch you own implementation in place of the default one, when your app will be started.

<manifest xmlns:android=""

Now we will declare our database in this AutomacorpApplication class. The database creation can be done with the room builder. You need to declare the global context, your Database class and the db name. the by lazy is used to initialize the property only when it will be used.

class AutomacorpApplication : Application() {

    val database: AutomacorpDatabase by lazy {
        Room.databaseBuilder(this,, "automacorpdb")


If you need to use a DAO in your code you will be able to use

val windowDao = AutomacorpApplication.database.windowDao()

Use ViewModel object

Why use a ViewModel ?

The Android framework manages the lifecycle of UI controllers, such as activities and fragments. The framework may decide to destroy or re-create an UI controller in response to certain user actions or device events that are completely out of your control.

If the system destroys or re-creates an UI controller, any transient UI-related data you store in them is lost. For example, your app may include a list of users in one of its activities. When the activity is re-created for a configuration change, the new activity has to re-fetch the list of users.

For simple data, the activity can use the onSaveInstanceState() method and restore its data from the bundle in onCreate(), but this approach is only suitable for small amounts of data that can be serialized then deserialized, not for potentially large amounts of data like a list of users or bitmaps.

Another problem is that UI controllers frequently need to make asynchronous calls that may take some time to return. The UI controller needs to manage these calls and ensure the system cleans them up after it’s destroyed to avoid potential memory leaks.

ViewModels were created to resolve these problems and separate out view data ownership from UI controller logic. UI controllers such as activities and fragments should only display UI data, react to user actions, or handle operating system communication, such as permission requests. The data should be now managed by a ViewModel.

Using a view model helps enforce a clear separation between the code for your app’s UI and its data model.

View model

The ViewModel class is used to store data related to an app’s UI, and is also lifecycle aware, meaning that it responds to lifecycle events much like an activity or fragment does. If lifecycle events such as screen rotation cause an activity or fragment to be destroyed and recreated, the associated ViewModel won’t need to be recreated.

Create a ViewModel

To create a view model class, create a new class called WindowViewModel in a new package called com.automacorp.viewmodel. It should only use the WindowDao and for the moment we can implement inside the method used to load data

class WindowViewModel(private val windowDao: WindowDao) : ViewModel() { // (1)

    fun findById(windowId: Long): LiveData<WindowDto> = // (2)
        liveData(Dispatchers.IO) { // (3)
            emit(windowDao.findById(windowId).toDto()) // (4)

    fun save(windowId: Long, command: WindowCommandDto): LiveData<WindowDto> = // (2)
        liveData(Dispatchers.IO) { // (3)
          val window = Window(
              id = windowId,
          if (windowId == 0L) {
          } else {
          emit(window.toDto()) // (4)
  • (1) a view model must implement an abstract class ViewModel

  • (2) LiveData is an observable data holder class. Unlike a regular observable, LiveData is lifecycle-aware, meaning it respects the lifecycle of other app components, such as activities, fragments, or services. This awareness ensures LiveData only updates app component observers that are in an active lifecycle state.

  • (3) As we have to access to the DB we must do that outside the main thread. Coroutine liveData(Dispatchers.IO) is used to do that

  • (4) result mut be emitted and the different observers (Activity, Fragment) will be ready to manipulate this result.

A ViewModel class must be lifecycle aware, it should be instantiated by an object that can respond to lifecycle events and an object made to handle all memory managements. For that we will use a ViewModelProvider.Factory. This object should be defined in a compagnon object

class WindowViewModel(private val windowDao: WindowDao) : ViewModel() {

    companion object {
        val factory: ViewModelProvider.Factory =
            object : ViewModelProvider.Factory {
                override fun <T : ViewModel> create(
                    modelClass: Class<T>,
                    extras: CreationExtras
                ): T {
                    // Load the Dao from the Application object
                    val windowDao = (extras[APPLICATION_KEY] as AutomacorpApplication)
                    return WindowViewModel(windowDao) as T

    // ...

Use the view model in an activity

You can a global property in your property to define your view model.


private val viewModel: WindowViewModel by viewModels {

And you want to pouplate your list you can use

viewModel.findAll().observe(this) { windows ->

: Use a database in your project


  1. Open build.gradle.kts (Module:

  2. As Room uses annotations we need to configure Gradle to launch the kotlin annotation processor. For that you just have to add a new plugin id kotlin-kapt

    plugins {
  3. In the dependencies block, declare new libraries

    implementation ""
    implementation ""
    kapt ""
    implementation "androidx.lifecycle:lifecycle-viewmodel-ktx:2.5.1"
    implementation "androidx.lifecycle:lifecycle-livedata-ktx:2.5.1"
    implementation "androidx.activity:activity-ktx:1.6.0"
  4. As you updated your gradle configuration, Android Studio display a message to synchronize your projet. Click on Sync now

    Sync Gradle project

Create your first entity

  • Create a new class in the package com.automacorp.model called Room and use annotations to link this class to the database (@Entity, @PrimaryKey, @ColumnInfo…​)

  • Create a new interface called RoomDao in the package com.automacorp.dao and write methods to manage a Room : findAll, findById, save, update, delete…​

  • Create a new class AutomacorpDatabase in com.automacorp.dao to declare the database

  • As we have to create this database only once, create a AutomacorpApplication in the root folder, and declare this App override in your AndroidManifest.xml

  • Create a property val database: AutomacorpDatabase by lazy {} in your AutomacorpApplication

  • Create in package com.automacorp.viewmodel a RoomViewModel class to manage all CRUD operations (Create, read all or one, update and delete)

Now, you can update the RoomsActivity used to list all rooms.

  1. Add a new global property to define your view model

    private val viewModel: RoomViewModel by viewModels {
  2. We need to replace the code used to populate the adapter, to update a room (ie the calls to ApiServices.windowsApiService)

  3. In RoomsActivity you can for example used this code with a method to observe our livedata returned by the view model. The code was made to manage asynchronous calls and you don’t need anymore to switch between coroutines in your Activity or Fragment

    viewModel.findAll().observe(this) { rooms ->
        roomsAdapter.setItems(rooms) }
  4. Do the same job in RoomActivity

You can start your application and as we have nothing in database you should have an empty list when you want to display the window list.

Synchronize our database

We want to only use this database when the remote API is not accessible. To do that we will refactor our ViewModel to

  1. call the remote API by default

  2. remove the last data if call is OK

  3. store the last received data

  4. call the database if remote API is not available (no network, service deny…​)

Update the ViewModel to do these steps. Below you can find an example for the room

fun findAll(): LiveData<List<RoomDto>> =
    liveData(Dispatchers.IO) {
        runCatching {
        }.onSuccess {
            // If remote API is available we synchronize data locally
                ?.also { rooms ->
                    rooms.onEach { room ->
                                id =,
                                name =,
                                currentTemperature = room.currentTemperature,
                                targetTemperature = room.targetTemperature
                                    id =,
                                    name =,
                                    roomId =,
                                    roomName =,
                                    windowStatus = it.windowStatus
                ?: emit(emptyList())
        }.onFailure {
            val rooms = roomDao.findAll().map { it.toDto() }
            emit(rooms) // (4)

fun findById(roomId: Long): LiveData<RoomDto> =
    liveData(Dispatchers.IO) { // (2)
        runCatching {
            // We call the remote API
        }.onSuccess {
        }.onFailure {
            val room = roomDao.findById(roomId).apply {
                windows = windowDao.findByRoomId(roomId)

fun save(roomId: Long, room: RoomCommandDto): LiveData<RoomDto?> =
    liveData(Dispatchers.IO) {
        runCatching {
            if (roomId == 0L) {
            } else {
                ApiServices.roomsApiService.updateRoom(roomId, room).execute().body()
        }.onSuccess {
        }.onFailure {

This code should work but it should be nice to know when we are in the fallback mode. For that we can expose a new live data in your code.

  1. Create a new enum called State in WindowViewModel

    enum class State { ONLINE, OFFLINE }
  2. Create a property in RoomViewModel to expose this state. By default the state is ONLINE

    val networkState: MutableLiveData<State> by lazy {
        MutableLiveData<State>().also { it.postValue(State.ONLINE) }
  3. You can add a new Observable in your activity RoomsActivity and RoomActivity to display a message when the data will be loaded from the local database

    viewModel.networkState.observe(this) { state ->
        if(state == State.OFFLINE) {
            Toast.makeText(this,"Offline mode, the last known values are displayed", Toast.LENGTH_LONG)
  4. Update the state in the methods findAll, findById, 'save` in RoomViewModel when you use the API or the database. Be careful you need to do this update on the main thread and you have to use this coroutine scope (Dispatcher.Main).

    fun findById(roomId: Long): LiveData<RoomDto> =
        liveData(Dispatchers.IO) { // (2)
            runCatching {
                // We call the remote API
            }.onSuccess {
            }.onFailure {
                val room = roomDao.findById(roomId).apply {
                    windows = windowDao.findByRoomId(roomId)