Save Data into SQLite Database using Kotlin in Android

Suppose you want to create an application like Android Note App or ToDo Task app that need to save data into your device, so that later you can access them without internet connection. You may insert new data, search data, update or delete them. In short, an offline database functionality is needed for your application. Different database can be implemented for those purposes, SQLite is one of them. SQLite database supports all the relational database features. In order to access this database, you don’t need to establish any kind of connections for it like JDBC, ODBC etc. In today’s tutorial, you’ll learn implementing SQLite database in your android app using Kotlin.

Android SQLite

Android SQLite is a very lightweight database which comes with Android OS. Android SQLite combines a clean SQL interface with a very small memory footprint and decent speed. For Android, SQLite is “baked into” the Android runtime, so every Android application can create its own SQLite databases.

Android SQLite native API is not JDBC, as JDBC might be too much overhead for a memory-limited smartphone. Once a database is created successfully its located in data/data//databases/ accessible from Android Device Monitor.

SQLite is a typical relational database, containing tables (which consists of rows and columns), indexes etc. We can create our own tables to hold the data accordingly. This structure is referred to as a schema.

The above video demonstrate the app we’ll be building today while learning SQLite using Kotlin. You can directly download the full Android Studio Project below, but I would suggest to read the article first for your better understanding.

Create a database using SQLiteOpenHelper

First of all, For this Android SQLite tutorial, we’ll create a class that will contain all database related functionality like creating the database for first time, inserting new data, updating existing data or deleting specific data. But before that let me show you how are database table will look like.

id	name	age	email
1	Someone’s Name	20	user1@email.com
2	Someone’s Name	25	user2@email.com

So I guess, you understood how our data will be saved in a Table. We’ll create our database, then create our table “users” with these 4 attributes or columns:

• id – It will contain integer values and it will increment automatically.
• name – It will contain text values.
• age – It will contain text values. You may also make it’s data type integer.
• email – It will also contain text values.

So create a new Kotlin file named “DBHelper.kt” and place the below codes. I’ll break them later.

package com.frsarker.sqlitetutorial

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


class DBHelper(context: Context, factory: SQLiteDatabase.CursorFactory?) :
    SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION) {


    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            "CREATE TABLE $TABLE_NAME " +
                    "($COLUMN_ID INTEGER PRIMARY KEY, $COLUMN_NAME TEXT, $COLUMN_AGE TEXT, $COLUMN_EMAIL TEXT)"
        )
    }

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

    fun insertRow(name: String, age:String, email: String) {
        val values = ContentValues()
        values.put(COLUMN_NAME, name)
        values.put(COLUMN_AGE, age)
        values.put(COLUMN_EMAIL, email)

        val db = this.writableDatabase
        db.insert(TABLE_NAME, null, values)
        db.close()
    }

    fun updateRow(row_id: String, name: String, age:String, email: String) {
        val values = ContentValues()
        values.put(COLUMN_NAME, name)
        values.put(COLUMN_AGE, age)
        values.put(COLUMN_EMAIL, email)

        val db = this.writableDatabase
        db.update(TABLE_NAME, values, "$COLUMN_ID = ?", arrayOf(row_id))
        db.close()
    }

    fun deleteRow(row_id: String) {
        val db = this.writableDatabase
        db.delete(TABLE_NAME, "$COLUMN_ID = ?", arrayOf(row_id))
        db.close()
    }

    fun getAllRow(): Cursor? {
        val db = this.readableDatabase
        return db.rawQuery("SELECT * FROM $TABLE_NAME", null)
    }

    companion object {
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "myDBfile.db"
        const val TABLE_NAME = "users"

        const val COLUMN_ID = "id"
        const val COLUMN_NAME = "name"
        const val COLUMN_AGE = "age"
        const val COLUMN_EMAIL = "email"
    }


}

So now lets break the code one by one. First we declared all our necessary values like database name, database version, table name, column’s name as

companion object {
    const val DATABASE_VERSION = 1
    const val DATABASE_NAME = "myDBfile.db"
    const val TABLE_NAME = "users"

    const val COLUMN_ID = "id"
    const val COLUMN_NAME = "name"
    const val COLUMN_AGE = "age"
    const val COLUMN_EMAIL = "email"
}

The onCreate() method is called only the first time of execution and it creates the database table using SQL query

override fun onCreate(db: SQLiteDatabase) {
    db.execSQL(
        "CREATE TABLE $TABLE_NAME " +
                "($COLUMN_ID INTEGER PRIMARY KEY, $COLUMN_NAME TEXT, $COLUMN_AGE TEXT, $COLUMN_EMAIL TEXT)"
    )
}

The onUpgrade() method will be called if you change the database version. Cause in the next version of database you may add one or more column in the table, So only then, the table need to be deleted and create again.

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

The insertRow() method will be used for inserting new user’s information into the table using db.insert()

fun insertRow(name: String, age:String, email: String) {
    val values = ContentValues()
    values.put(COLUMN_NAME, name)
    values.put(COLUMN_AGE, age)
    values.put(COLUMN_EMAIL, email)

    val db = this.writableDatabase
    db.insert(TABLE_NAME, null, values)
    db.close()
}

The updateRow() will be used for modifying any existing information. And deleteRow() will be used for up modifying and deleting existing information respectively.

Displaying all data inside ListView

As I mentioned before that MainActivity.kt will be used for displaying all saved user’s information in a list. That meas we’ll be pupulating ListView using a custom adapter. This is our MainActivity.kt

package com.frsarker.sqlitetutorial

import android.content.Intent
import android.os.Bundle
import android.view.View
import android.widget.ListView
import androidx.appcompat.app.AppCompatActivity


class MainActivity : AppCompatActivity() {
    val dbHandler = DBHelper(this, null)
    var dataList = ArrayList<HashMap<String, String>>()
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
    }


    fun loadIntoList(){
        dataList.clear()
        val cursor = dbHandler.getAllRow()
        cursor!!.moveToFirst()

        while (!cursor.isAfterLast) {
            val map = HashMap<String, String>()
            map["id"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_ID))
            map["name"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_NAME))
            map["age"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_AGE))
            map["email"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_EMAIL))
            dataList.add(map)

            cursor.moveToNext()
        }
        findViewById<ListView>(R.id.listView).adapter = CustomAdapter(this@MainActivity, dataList)
        findViewById<ListView>(R.id.listView).setOnItemClickListener { _, _, i, _ ->
            val intent = Intent(this, DetailsActivity::class.java)
            intent.putExtra("id", dataList[+i]["id"])
            intent.putExtra("name", dataList[+i]["name"])
            intent.putExtra("age", dataList[+i]["age"])
            intent.putExtra("email", dataList[+i]["email"])
            startActivity(intent)
        }
    }

    fun fabClicked(v:View){
        val intent = Intent(this, DetailsActivity::class.java)
        startActivity(intent)
    }


    public override fun onResume() {
        super.onResume()
        loadIntoList()
    }
}

Create a new kotlin file named “CustomAdapter.kt“

package com.frsarker.sqlitetutorial

import android.content.Context
import android.view.LayoutInflater
import android.view.View
import android.view.ViewGroup
import android.widget.BaseAdapter
import android.widget.TextView


class CustomAdapter(private val context: Context,
                    private val dataList: ArrayList<HashMap<String, String>>) : BaseAdapter() {

    private val inflater: LayoutInflater = this.context.getSystemService(Context.LAYOUT_INFLATER_SERVICE) as LayoutInflater
    override fun getCount(): Int { return dataList.size }
    override fun getItem(position: Int): Int { return position }
    override fun getItemId(position: Int): Long { return position.toLong() }

    override fun getView(position: Int, convertView: View?, parent: ViewGroup): View {
        var dataitem = dataList[position]

        val rowView = inflater.inflate(R.layout.list_row, parent, false)
        rowView.findViewById<TextView>(R.id.row_name).text = dataitem["name"]
        rowView.findViewById<TextView>(R.id.row_age).text = "Age: " + dataitem["age"]
        rowView.findViewById<TextView>(R.id.row_email).text = "Email: " + dataitem["email"]



        rowView.tag = position
        return rowView
    }
}

Layout resource file for CustomAdapter “list_row.xml“

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:padding="20dp"
    android:gravity="center_vertical">


    <TextView
        android:id="@+id/row_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Name"
        android:textStyle="bold"
        android:textSize="22dp"/>

    <LinearLayout android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/row_age"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Age"/>

        <TextView
            android:id="@+id/row_email"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="15dp"
            android:text="City"/>

    </LinearLayout>

</LinearLayout>

And now our activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="match_parent"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:orientation="vertical">

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent"/>

    <com.google.android.material.floatingactionbutton.FloatingActionButton
        style="@style/Widget.MaterialComponents.FloatingActionButton"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:layout_alignParentBottom="true"
        android:layout_marginRight="30dp"
        android:layout_marginBottom="50dp"
        android:onClick="fabClicked"
        app:srcCompat="@android:drawable/ic_input_add"
        app:tint="#FFFFFF"
        app:backgroundTint="#E91E63"/>

</RelativeLayout>

You can see that I’ve used a FloatingActionButton, for using a FloatingActionButton you’ll need to add this dependency

implementation 'com.google.android.material:material:1.0.0'

The time I’m writing this article, version 1.0.0 is the stable version of this library. You should use the latest library always. You can find the latest library version from MVN Repository.

Insert, Update or Delete Data

In the flowchart I’ve given before, you know that we’ll be using DetailsActivity.kt for both inserting a new user’s information and updating or deleting them.

Create a new Activity called DetailsActivity.kt

package com.frsarker.sqlitetutorial

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.Button
import android.widget.EditText
import android.widget.Toast

class DetailsActivity : AppCompatActivity() {
    private val dbHandler = DBHelper(this, null)
    lateinit var nameEditText:EditText
    lateinit var ageEditText:EditText
    lateinit var emailEditText:EditText
    lateinit var modifyId:String

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_details)

        nameEditText = findViewById(R.id.name)
        ageEditText = findViewById(R.id.age)
        emailEditText = findViewById(R.id.email)

        /* Check  if activity opened from List Item Click */
        if(intent.hasExtra("id")){
            modifyId = intent.getStringExtra("id")
            nameEditText.setText(intent.getStringExtra("name"))
            ageEditText.setText(intent.getStringExtra("age"))
            emailEditText.setText(intent.getStringExtra("email"))
            findViewById<Button>(R.id.btnAdd).visibility = View.GONE
        }else{
            findViewById<Button>(R.id.btnUpdate).visibility = View.GONE
            findViewById<Button>(R.id.btnDelete).visibility = View.GONE
        }

    }


    fun add(v:View){
        val name = nameEditText.text.toString()
        val age = ageEditText.text.toString()
        val email = emailEditText.text.toString()
        dbHandler.insertRow(name, age, email)
        Toast.makeText(this, "Data Addeded", Toast.LENGTH_SHORT).show()
        finish()
    }

    fun update(v:View){
        val name = nameEditText.text.toString()
        val age = ageEditText.text.toString()
        val email = emailEditText.text.toString()
        dbHandler.updateRow(modifyId, name, age, email)
        Toast.makeText(this, "Data Updated", Toast.LENGTH_SHORT).show()
        finish()
    }

    fun delete(v:View){
        dbHandler.deleteRow(modifyId)
        Toast.makeText(this, "Data Deleted", Toast.LENGTH_SHORT).show()
        finish()
    }
}

You can see that if the activity opens by intent with extras then we make the insert button invisible otherwise the update and delete button is invisible.

Other things I’m not explaining because they are just calling methods of DBHelper class.

and the layout resource file for this activity activity_details.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="15dp">

    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Name"/>

    <EditText
        android:id="@+id/age"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Age"
        android:inputType="number"/>

    <EditText
        android:id="@+id/email"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Email"/>


    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <Button
            android:id="@+id/btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Add"
            android:onClick="add"/>

        <Button
            android:id="@+id/btnUpdate"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Update"
            android:onClick="update"/>
        <Button
            android:id="@+id/btnDelete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Delete"
            android:onClick="delete"/>
    </LinearLayout>


</LinearLayout>

This is how my AndroidManifest.xml looks now.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.frsarker.sqlitetutorial">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity android:name=".DetailsActivity"/>
    </application>

</manifest>

Now Run the App and start with adding new user’s information into SQLite database by clicking the FAB button.