Android Room using Kotlin

Android Room Database Tutorial using Kotlin will guide you to quickly perform the database integration into your live project.

Android Room is a library provided by Google which I find a better alternative to native Sqlite Open Helper. This is a layer on top of native interfaces which offers much more control on your Sqlite database.

I am from java background and seeing the popularity Kotlin has got I thought of diving into coding and use it directly into my running project.

No doubt about it, almost all the software applications from small scale to large scale enterprise applications rely on database. And, I thought to begin with same, so I started building my first Kotlin feature to handle database and its interfaces using Android Room.

When I started working on Kotlin after years on Java, its not a happy situation to be in when syntax becomes hindrance.

I started looking at various options to create interfaces and came across Android Room as a layer on top of native java Sqlite interface which looked promising to me in comparison to any other options/tools/libraries available.

If you are looking for the database integration using JAVA, please go through this blog post ANDROID ROOM USING JAVA.

In this article we will only go through Android Room using KOTLIN. 

I think this is enough of talk, lets try to understand Android Room using real problem.

To use Android Room Library with Kotlin there are steps involved which are more clerical than the real work. Being lazy, I admit that, my intention was to reduce the work required in doing the following repetitive tasks :

At this point, you may have already realized that there is a good amount of work required to be done.

You may be wondering why I mentioned this as a clerical job or a repetitive task. Is there anything else I can do to avoid this ?
I will come back to this point, mean while lets understand each of these in detail using source code.

My intention is to bring you into fast development mode rather than spending too much time on tiny details.

Here we go…

Android Room Entity Class

Entity class is used to define table, its columns and the key relationship with other tables.

This class contains :
1> Table Name 
2> Columns

Below is the source code for sample Entity class, which will look something like this :

package com.example.database

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.ForeignKey
import java.sql.Blob

@Entity(
    tableName = (DatabaseConstants.AccountTableKey.TABLE_NAME))

data class Account(

	@PrimaryKey(autoGenerate = false)
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.ACCOUNT_ID_FIELD)	var AccountId: Integer,
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.NAME_FIELD) val Name: String,
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.AGE_FIELD) val Age: Integer,
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.DATE_FIELD) val Date: Long,
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.EARNINGS_FIELD) val Earnings: Double,
	@ColumnInfo(name = DatabaseConstants.AccountTableKey.MORE_FIELD) val More: Float
)

In the above example I am using constant class “DatabaseConstants.kt” to store all the constants related to data structure. 

You can simply put the table name instead of this constant class. I have added this to keep the code clean. The source code for this file will be dumped at the bottom of this post.

Android Room DAO Class

DAO Interface is used to define the functions ahead of time, so they can be utilized when required.

– This is the place where you define your ADD, MOD, DEL and GET methods.
– You can control the type of data you are expecting in return using the SQL queries.

Below is the source code for sample Dao class:

package com.example.database

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.RawQuery
import androidx.sqlite.db.SupportSQLiteQuery

@Dao
interface AccountDao: BaseCommonDao {

    @Query("SELECT * FROM ${DatabaseConstants.AccountTableKey.TABLE_NAME}")
	fun getAllData(): LiveData<List>

    @Query("SELECT * FROM ${DatabaseConstants.AccountTableKey.TABLE_NAME} ORDER BY :orderBy") 
	fun getAllData(orderBy: String): LiveData<List>

    @RawQuery(observedEntities = [Account::class])
    fun getData(query: SupportSQLiteQuery?): LiveData

    @RawQuery(observedEntities = [Account::class])
    fun getListData(query: SupportSQLiteQuery?): LiveData<List>

    @Query("DELETE FROM ${DatabaseConstants.AccountTableKey.TABLE_NAME}")
    suspend fun deleteAllData(): Int

    @RawQuery(observedEntities = [Account::class])
    suspend fun delete(query: SupportSQLiteQuery?): Int

    /**
     * Table name to be utilized by BaseCommonDao to perform operations sa. execution of
     * Sqlite functions (MAX, MIN, etc..)
     */
    override fun getTableName(): String {
        return DatabaseConstants.AccountTableKey.TABLE_NAME
    }
}

Android Room Repository Class

This is a Repository class which shall be utilized to pull or push your data into your Sqlite database. Repository class internally uses DAO interfaces to perform database operations such as ADDITION, MODIFICATION, DELETION, GET LIST, GET DATA, etc…

package com.example.database

import androidx.lifecycle.LiveData
import androidx.sqlite.db.SimpleSQLiteQuery

class AccountRepository(private val accountDao: AccountDao) {

    fun getAllData() = accountDao.getAllData()

    fun getAllData(orderBy: String): LiveData<List> {

        return accountDao.getAllData(orderBy)
    }

	suspend fun insertAll(data: List): List {
        return accountDao.insertAll(data);
    }

	suspend fun insert(data: Account): Long {
        return accountDao.insert(data)
    }

    fun getData(whereCondition: String): LiveData {

        var finalQuery = StringBuilder ();
        finalQuery.append("SELECT * FROM ${DatabaseConstants.AccountTableKey.TABLE_NAME}")

        if(whereCondition.isNotEmpty()) {

            finalQuery.append(" WHERE $whereCondition")
        }

        finalQuery.append(" LIMIT 1")

        val simpleSQLiteQuery = SimpleSQLiteQuery(finalQuery.toString());

        return accountDao.getData(simpleSQLiteQuery)
    }

	fun getListData(whereCondition: String, orderBy: String): LiveData<List> {

        var finalQuery = StringBuilder ();
        finalQuery.append("SELECT * FROM  ${DatabaseConstants.AccountTableKey.TABLE_NAME}")

        if(whereCondition.isNotEmpty()) {

            finalQuery.append(" WHERE $whereCondition")
        }

        if(orderBy.isNotEmpty()) {

            finalQuery.append(" ORDER BY $orderBy")
        }

        val simpleSQLiteQuery = SimpleSQLiteQuery(finalQuery.toString());

        return accountDao.getListData(simpleSQLiteQuery)
    }

    suspend fun delete(whereCondition: String): Int {

        if(whereCondition.isEmpty()) {

            return 0
        }

        var finalQuery = "DELETE FROM ${DatabaseConstants.AccountTableKey.TABLE_NAME} WHERE $whereCondition"
        
        val simpleSQLiteQuery = SimpleSQLiteQuery(finalQuery);

        return accountDao.delete(simpleSQLiteQuery)
    }

    suspend fun deleteAllData(): Int {

        return accountDao.deleteAllData()
    }
}

Android RoomDatabase Class

This is the place where you need to define DAO interfaces and the name of the database. This class holds the database connection as a single-ton class

package com.example.database

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.sqlite.db.SupportSQLiteDatabase
import kotlinx.coroutines.CoroutineScope
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.launch


@Database(entities = [Account::class], version = 1, exportSchema = false)
//@TypeConverters(Converters::class)

abstract class AppRoomDatabase : RoomDatabase() {

abstract fun accountDao(): AccountDao
//abstract fun artistDao(): ArtistDao

companion object {

        @Volatile
        private var INSTANCE: AppRoomDatabase? = null

        fun getDatabase(
                context: Context,
                scope: CoroutineScope
        ): AppRoomDatabase {
            // if the INSTANCE is not null, then return it,
            // if it is, then create the database
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                        context.applicationContext,
                        AppRoomDatabase::class.java,
                        "database"
                )
                                        .build()
                INSTANCE = instance
                // return instance
                instance
            }
        }
    }
}

DAO Base Class

I have created this class to keep the source code clean by adding common/duplicate stuff into one class. If you find anything common throughout your DAO Classes, you can utilize this class and put it here.

package com.example.database

import android.database.Cursor
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.RawQuery
import androidx.sqlite.db.SimpleSQLiteQuery
import androidx.sqlite.db.SupportSQLiteQuery

/**
 * Class holds the common functionality for all the DAO classes
 * s.a. insert, update, delete and sqlite functions
 */
interface BaseCommonDao {

    /**
     * Sqlite functions
     */
    interface SqliteFunctions {
        companion object {
            const val AVERAGE = "avg"
            const val COUNT = "count"
            const val MAX = "max"
            const val MIN = "min"
            const val SUM = "sum"
            const val TOTAL = "total"
        }
    }

    /**
     * Forced to be implemented by all its child classes
     * It will be utilized for Sqlite function execution
     *
     * @return the table name
     */
    abstract fun getTableName(): String

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertAll(data: List): List

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(data: T): Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun update(data: AccountTest): Long

    //@Insert(onConflict = OnConflictStrategy.REPLACE)
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun updateAll(data: List): List

    @Delete
    suspend fun delete(obj: T): Int

    @RawQuery
    fun executeSqliteFunction(query: SupportSQLiteQuery?): Cursor

    /**
     * To perform the execution of Sqlite functions
     * s.a avg, count, max, min, sum, total, ..
     *
     * e.g function("max", "id", null);
     * will return the maximum value of 'id' column
     *
     * Returns the sqlite function response of the given [functionType] = BaseCommonDao.SqliteFunctions.MAX, BaseCommonDao.SqliteFunctions.MIN, etc..,
     * [columnName] or [condition], if any.
     */
    fun function(
        functionType: String, columnName: String,
        condition: String?
    ): Long {
        var result: Long = 0
        var cursor: Cursor? = null
        var rawQuery = (" select " + functionType + "(" + columnName
                + ")" + " from " + getTableName())
        if (condition != null && condition.isNotEmpty()) rawQuery += " where $condition"

        val simpleSQLiteQuery = SimpleSQLiteQuery(rawQuery.toString());
        cursor = executeSqliteFunction(simpleSQLiteQuery)

        if (cursor != null && cursor.moveToFirst()) {
            result = cursor.getLong(0)
            cursor.close()
        }

        return result
    }
}

Database Constant Class

I have added this class to store all the columns and table names in one place. Again, this is not mandatory but the only intention is to keep the source code clean and avoid duplicate entries.

package com.example.database;

class  DatabaseConstants {

	interface BaseConstants {
        companion object {

			// If you change the database schema, you must increment the database version
		const val DATABASE_VERSION = 1
			const val DATABASE_NAME = "database.db"
		}
	}

	interface AccountTableKey {

		companion object {

			const val TABLE_NAME = "account"

			const val ACCOUNT_ID_FIELD = "account_id"
			const val NAME_FIELD = "name"
			const val AGE_FIELD = "age"
			const val DATE_FIELD = "date"
			const val EARNINGS_FIELD = "earnings"
			const val MORE_FIELD = "more"
        }
    }
}

Conclusion

Now, coming back to the alternative.

Writing and maintaining all of this is going to take a lot of time. What if you get something through which you are able to generate all the bug-free source code in a click, refer to the link here.

You can check this link to know more about code generator and how to use this in your project to increase your productivity.

Please share your comments, I would love to hear!

Don’t miss to subscribe, I will be sharing more contents on software programming practices.

Full Source Code

To download the full sample code for Android Room using Kotlin, click on the button given below: