Skip to main content
Updated date:

How to Create And Manage Local Database on Android Effortlessly

Davinder Singh is a Polymath. He loves reading, learning and writing technical cookbooks about new technologies.

What You Will Learn?

  • You will learn how to do basic database management with sqlite.
  • You will learn how to add Room persistence API in your project
  • You will learn how to implement Room API and perform database operations with it.

Prerequisites

  • A firm understanding of SQL statements.
  • Know-how of working with Android at any level of expertise with Kotlin programming language.
  • A computer with strong Internet connection.

Crash Course in Android Sqlite Database API

Database is crucial part of any application regardless of its size. In android there are two options for storing and retrieving data viz. SharedPreferences and Sqlite Database Management System (DBMS). While trivial data involving key–value pairs only, upto a limited amount of size can be handled with ease through shared-preferences; Data containing complex structures having reasonably large size is only suitably can be handled with sqlite.

Android has native support for sqlite. You can either ship your application with pre-created database or you can ship it with schema(skeleton) of database. This schema can then be used to form tables during runtime.To add sqlite database capabilities into your application all that you will have to do is extend SQLiteOpenHelper class(resides inside android.database.sqlite package) and overriding following methods: onCreate, onUpgrade.

“In computer programming, boilerplate code or just boilerplate refers to sections of code that have to be included in many places with little or no alteration.”

We will create a simple application to manage database of employees of an organisation by following normal approach i.e. extending SqliteOpenHelper class and then we will have a look at how this same application can be implemented with Room. However, I will not go into details of implementing the GUI of this application instead, I will merely show you how you can implement database capabilities in this app.

Column nameData type

emp_id

integer(Primary key and Auto-increment)

name

string/text

email

string/text

phone_number

string/text

Create A Model Data Class for Employee

  • Create a new Android project with empty activity.
  • Add a class file to the project. Name it Employee.kt. This will represent the model of an employee. Model class basically describes the entity–Employee in this case.
//Employee.kt
data class Employee(
    val emp_id: Int,
    val name: String,
    val email: String,
    val number: String
)

Create SqlHelper Class

Add another class file to the project, this class will extend SQLiteOpenHelper and provide us access to create, manage, manipulate and query database.

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper


class SqlHelper(context: Context) : SQLiteOpenHelper(
    context, DB_NAME,
    null, 1
) {

    companion object {
        private const val EMPLOYEE_TABLE = "employee_table"
        private const val DB_NAME = "employee_db"
        //--TABLE COLUMN NAMES
        private const val EMP_ID = "emp_id"
        private const val EMP_NAME = "name"
        private const val EMP_EMAIL = "email"
        private const val EMP_NUMBER = "phone_number"
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(
            "CREATE TABLE $EMPLOYEE_TABLE " +
                    "($EMP_ID integer primary key AUTOINCREMENT," +
                    "$EMP_NAME text," +
                    "$EMP_EMAIL text," +
                    "$EMP_NUMBER text)"
        )
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL("DROP TABLE IF EXISTS $EMPLOYEE_TABLE")
        onCreate(db)
    }

    fun insertEmployee(employee: Employee): Long {
        val contentValue = ContentValues()
        contentValue.put(EMP_NAME, employee.name)
        contentValue.put(EMP_NUMBER, employee.number)
        contentValue.put(EMP_EMAIL, employee.email)
        return writableDatabase.insert(EMPLOYEE_TABLE, null, contentValue)
    }

    fun deleteRow(employee: Employee): Int {
        return writableDatabase.delete(EMPLOYEE_TABLE, "$EMP_EMAIL = ?", arrayOf(employee.email))
    }

    fun getAllEmployess(): List<Employee> {
        val cursor = readableDatabase.rawQuery("SELECT * FROM $EMPLOYEE_TABLE", null)
        val list = ArrayList<Employee>()
        cursor.use{ cur ->
            cur.moveToFirst()
            with(cur) {
            while (!isAfterLast) {
                list.add(
                    Employee(
                        getString(getColumnIndex(EMP_ID)),
                        getString(getColumnIndex(EMP_NAME)),
                        getString(getColumnIndex(EMP_EMAIL)),
                        getString(getColumnIndex(EMP_NUMBER))
                    )
                )
             }
           }
        }
        return list
    }
}

Performing Database Operations

Now in the final step, We can perform operations on database with the help of an instance of SqlHelper class.

class MainActivity : AppCompatActivity() {
    private lateinit var dbHelper: SqlHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        dbHelper = SqlHelper(this)
        insert()
        show()
    }

    private fun insert() {
        dbHelper.insertEmployee(Employee("DV", "erudtio@gmail.com", "1234455"))
        dbHelper.insertEmployee(Employee("DV2", "erudti@gmail.com", "1234455"))
        dbHelper.insertEmployee(Employee("DV3", "erudi@gmail.com", "1234455"))
    }

    private fun show() {
        dbHelper.getAllEmployess().forEachIndexed { index, it ->
            Log.e("Employe: $index", "Name: ${it.name}, Email: ${it.email}, Number: ${it.number}")
        }
    }
}

Problems With Native API

There are many problems with this approach because of low-level nature of the API. While they are not quite apparent in our little application; they often turn up in applications of normal size. Following are some of them:

  • Readability of code is very less.
  • Because, there's no compile time checking of SQL queries, finding errors as well as debugging can become headache and In worst cases application might crash during runtime.
  • As there's no requirement forced on programmer for using background thread for executing slow database queries (I/O bound tasks); Performing them on main thread can waste resources and in worst case can hang whole application itself.

Room Persistence API

The point of above discussion was that, Native APIs for working with Sqlite in Android are very low level and hence we require an abstraction layer upon these APIs which provide us with concise, efficient and easy to debug methods for working with sqlite.

Room persistence API is part of Android JetPack components. It is an easy to understand, readable and easy to debug library for working with sqlite. It is a high level layer upon native sqlite APIs.

Basics of Room API

Before we move on to implementing Room API; We first have to understand few basic terminologies:

  • DAO short for Data Access Object. It contains all the methods for accessing database.
  • Entity is class which represents a table (schema). In other sense it's model of database.
  • Database contains glue to hold together DAOs and Entities. Any abstract class which is annotated with “@Database” and extends RoomDatabase class is valid database class. Every database operation is carryout through instance of this class. Which can be created during runtime by calling Room.databaseBuilder() or Room.inMemoryDatabaseBuilder() methods.

Implementing Room API

We will implement same example again (Employee Application) but this time with the help of Room persistence API. Again for GUI you can add two fragments, One for adding content another one for displaying the content.

Application doesn't required to have a sophisticated interface, Add two fragment: AddEmployee fragment and DisplayEmployee fragment.

In AddEmployee fragment:

  • Add three edit texts with labels: Employee Name, Employee Email and Employee Phone number.
  • A single button in the middle, for performing validation of data as well as insertion of it into the table.

In DisplayEmployee fragment:

  • Add a recycler view for displaying employee list.
  • In list item for the recycler view add four textviews horizontally spaced apart. Displaying Employee Id, Name, Email and Phone number.
  • Add a button in the middle for removing all of the data in a single go.

Step 0: Add Room Dependencies

Before using Room, We must first have to add gradle dependency for Room into the project. Add following lines in your module level gradle file

dependencies {
    def room_version = "2.1.0-alpha04"
    //Room
    implementation "androidx.room:room-runtime:$room_version"
    kapt "androidx.room:room-compiler:$room_version"
}

Also at the very beginning of the this file, add following lines which will indicate gradle to apply plugins to the project during the build.

//Kotlin annotation processor
apply plugin: 'kotlin-kapt'

Step 1: Add An Entity Class

Entity class is where we define to Room the data model or skeleton or schema of database. At this point I am assuming that you have Room API included in the project and it (project) is compiling as well as running fine. For sake of showing flexibility of Room, Let add one more field to the employee table: hiring_date.

Column NameData type

emp_id

integer(Primary key, Autoincrement)

name

string/text

email

string/text

phone_number

string/text

hire_date

long

Room has annotations to help you describe schema of entity. Following is list of some of them:

  • @Entity: This is class level annotation i.e. it only can be placed above a class. Entity annotation takes following arguments: tableName, array of primary keys and list of indices on table etcetera.
  • @TypeConverters: This is also class level annotation. Argument to this annotations is an array of classes which have methods for mapping between complex koltin/Java types such as Date into simple SQL types. This is doesn't required if your entity class doesn't have any types other than: Number and String types.
  • @PrimaryKey: This a field level annotation meaning that it can only be used to annotate variables. It indicates that the underlying field is a Primary key in the table. You can pass autoGenerate = true to let Room know that this value-type is auto increment-able.
  • @ColumnInfo: Column info annotation is also field type annotation. It is used to annotate fields so as to make them fields in the table. It takes name as argument to specify name of column being represented by this field.
//Employee.kt
@Entity(tableName = “employee_table”)
@TypeConverters(DateConverter::class)
data class Employee(
    @PrimaryKey(autoGenerate = true) var emp_id: Int,
    @ColumnInfo(name = “name”) var name: String,
    @ColumnInfo(name = “email”) var email: String,
    @ColumnInfo(name = “phone_number”) var phoneNumber: String,
    @ColumnInfo(name = “hiring_date”) var hiringDate: Date
)

class DateConverter {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time?.toLong()
    }
}

Step 2: Add Database Access Object (DAO)

Now that we have our database model ready, We need corresponding DAO interface for performing operations over the model or entity. Database Access Object is basically a middleman between your application's business logic and database.

Inside the DAO interface, all you have to do is add methods for each corresponding query that you want to perform. And best part is they all are auto-generated for you from annotations by annotation processor. Common annotations:

  • @Query is annotation which is used to annotate methods with corresponding SQL queries or raw sql queries.
  • @Insert is used to annotate method which will inturn perform insertion operations.
  • @Delete annotation indicates the methods is to be for deleting entries from the table.
//EmployeeDao.kt
@Dao
interface EmployeeDao {
    @Query("SELECT * FROM employee_table ORDER BY hiring_date ASC")
    fun getAllEmployees(): List<Employee>

    @Insert
    fun insertAll(vararg employees: Employee)

    @Delete
    fun delete(employee: Employee): Int

    @Query("DELETE FROM employee_table WHERE `email`=:email")
    fun deleteAllWith(email: String): Int
}

Step 3: Add Room Database Class

Room database class is an abstract class. Again Room's annotation processor takes care of providing underlying definitions and implementation part. So, All you have to do is annotate the class. Following are the annotations that you can use with database class.

  • @Database annotation is class level annotation. It specifies that the annotated class is a Room database class. Argument to this annotation includes: Array of entities class, version of database and boolean indicating whether to export this schema to disk or not.
@Database(entities = [Employee::class], version = 1, exportSchema = false)
abstract class EmployeeDatabase : RoomDatabase() {
    abstract fun employeeDao(): EmployeeDao
}

Exporting schema means that Room should export the schema into a folder. Defaults to true but only works if you have had the room.schemaLocation variable set.

Version is used for upgrading purposes. In cases when you have updated or changed the schema of underlying database after you already have released the application.

Step 4: Add Database Provider

Database provider is last piece of this whole Room puzzle. Database provider class supplies the database object when required. Make sure that it's a singleton object. We call Room API's databaseBuilder inside this class.

class DatabaseProvider(var context: Context) {
    val db by lazy {
        Room.databaseBuilder(
            context,
            EmployeeDatabase::class.java, "employee-db"
        ).build()
    }
}

Step 5: Inserting Data

Now that we have completed implementing the Room API we are ready to use it to perform database operations. We'll take a look at inserting, deleting and querying.

Note: Room restrict performing database operations on Main thread and hence all database operations must be performed on background thread using either AsyncTask or Bare thread.


class InsertDataFragment : Fragment(){
        //....
        
   fun addCallbacks(){
      btAddEntry.setOnClickListener{
         InsertData(
           WeakReference(
	                   EmployeeDatabaseProvider(context!!)))
                    .execute(Employee(/*EmployeeData*/))
            }
        }

   class InsertData(private val provider: WeakReference<DatabaseProvider>) : AsyncTask<Employee, Void?, Void?>(){
	        override fun doInBackground(vararg params: Employee): Void? {
	            val dao = provider.get()!!.db.employeeDao()
	            dao.insertAll(*params)
	            return  null
	        }
	    }
}

Step 7: Removing Data

Removing data is also as easy as inserting the data. Again we will use AsyncTask for database removal.

class DeleteListAsync(
    val dao: EmployeeDao,
    var adapter: WeakReference<EmployeeListAdapter>
) : AsyncTask<Int, Void?, Void?>() {
    override fun doInBackground(vararg params: Int?): Void? {
        print(adapter.get()?.array?.size)
        adapter.get()?.array
            ?.forEach { employee ->
                println(dao.delete(employee))
            }
        return null
    }

    override fun onPostExecute(result: Void?) {
        super.onPostExecute(result)
        val mAdapter = adapter.get()
        mAdapter!!.array = listOf()
        mAdapter.notifyDataSetChanged()
    }
}

Step 8: Querying Data

And now for the last part we will have a look at how to get list of employees.

class GetListAsync(
        val dao: EmployeeDao,
        var adapter: WeakReference<EmployeeListAdapter>
    ) : AsyncTask<Void, Void, List<Employee>>() {
        override fun doInBackground(): List<Employee> {
            return dao.getAllEmployee()
        }

        override fun onPostExecute(result: List<Employee>?) {
            super.onPostExecute(result)
            val mAdapter = adapter.get()
            mAdapter!!.array = result!!
            mAdapter.notifyDataSetChanged()
        }
    }

Conclusion

Well that's all for this one folk. Room is powerful yet easy to use API. It provides a feature rich and MVVM compatible access to sqlite. You can read more about Room from here. If you ran into any problems or have questions feel free to ask them below in the comment section. As always Thank you for reading.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2019 Dav Vendator

Related Articles