SQLite database in Android kotlin example

SQLite database in Android is used to store a structure of relational or simple offline data in the android device. Where in other simply SQLite is relational database management, In android application development use of manage private database. SQLite database API android.database.sqlite package.

In this example, we are sharing the basic setup and use the android SQLite database in the Android application with basic operation Inserting and Reading data using Kotlin. SQLite is an open-source database-based SQL Language. It is widely used and Android has it by default to store data locally.

Define a schema

The defining schema is the main principles of SQL databases. Its a formal declaration of how the database is organized. Below image represents a visual look of a database table.

CREATE TABLE users 
  ( 
     id        INTEGER PRIMARY KEY, 
     firstname TEXT, 
     lastname  TEXT 
  )

Create a database using an SQL helper

Once you have defined how your database looks, you should implement methods that create and maintain the database and tables. Here are some typical statements that create and delete a table:

override fun onCreate(db: SQLiteDatabase?) {
        val CREATE_TABLE = "CREATE TABLE $TABLE_NAME ($ID Integer PRIMARY KEY, $FIRST_NAME TEXT, $LAST_NAME TEXT)"
        db?.execSQL(CREATE_TABLE)
    }

Put/Insert/Create information into a database

//Inserting (Creating) data
    fun addUser(user: Users): Boolean {
        //Create and/or open a database that will be used for reading and writing.
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(FIRST_NAME, user.firstName)
        values.put(LAST_NAME, user.lastName)
        val _success = db.insert(TABLE_NAME, null, values)
        db.close()
        Log.v("InsertedID", "$_success")
        return (Integer.parseInt("$_success") != -1)
    }

Read All information from a database

Here is how to read data for the database in android.

//get all users
    fun getAllUsers(): String {
        var allUser: String = "";
        val db = readableDatabase
        val selectALLQuery = "SELECT * FROM $TABLE_NAME"
        val cursor = db.rawQuery(selectALLQuery, null)
        if (cursor != null) {
            if (cursor.moveToFirst()) {
                do {
                    var id = cursor.getString(cursor.getColumnIndex(ID))
                    var firstName = cursor.getString(cursor.getColumnIndex(FIRST_NAME))
                    var lastName = cursor.getString(cursor.getColumnIndex(LAST_NAME))

                    allUser = "$allUser\n$id $firstName $lastName"
                } while (cursor.moveToNext())
            }
        }
        cursor.close()
        db.close()
        return allUser
    }

Let’s do Complete Code SQLite database in Android kotlin example

This is a basic and simple example for beginners.

Step 1. Create an android project in the android studio (Follow this tutorial: Android First Program in Android Studio kotlin)

Step 2. Create a model class (POJO): Users

Create a package directory in the root package its good practice to maintain your code.

package `in`.eyehunt.sqliteandroidexample.model

class Users {
    var id: Int = 0;
    var firstName: String = "";
    var lastName: String = "";
}

Step 2. Create DatabaseHandler kotlin class and extend SQLiteOpenHelper

Must override the onCreate() and onUpgrade() callback methods. You may also want to implement the onDowngrade() or onOpen() methods, but they are not required in this example.

package `in`.eyehunt.sqliteandroidexample.db

import `in`.eyehunt.sqliteandroidexample.model.Users
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log

/**
 * Created by Eyehunt Team on 07/06/18.
 */
class DatabaseHandler(context: Context) : 
        SQLiteOpenHelper(context, DB_NAME, null, DB_VERSIOM) {

    override fun onCreate(db: SQLiteDatabase?) {
        val CREATE_TABLE = "CREATE TABLE $TABLE_NAME " +
                "($ID Integer PRIMARY KEY, $FIRST_NAME TEXT, $LAST_NAME TEXT)"
        db?.execSQL(CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // Called when the database needs to be upgraded
    }

    //Inserting (Creating) data
    fun addUser(user: Users): Boolean {
        //Create and/or open a database that will be used for reading and writing.
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(FIRST_NAME, user.firstName)
        values.put(LAST_NAME, user.lastName)
        val _success = db.insert(TABLE_NAME, null, values)
        db.close()
        Log.v("InsertedID", "$_success")
        return (Integer.parseInt("$_success") != -1)
    }

    //get all users
    fun getAllUsers(): String {
        var allUser: String = "";
        val db = readableDatabase
        val selectALLQuery = "SELECT * FROM $TABLE_NAME"
        val cursor = db.rawQuery(selectALLQuery, null)
        if (cursor != null) {
            if (cursor.moveToFirst()) {
                do {
                    var id = cursor.getString(cursor.getColumnIndex(ID))
                    var firstName = cursor.getString(cursor.getColumnIndex(FIRST_NAME))
                    var lastName = cursor.getString(cursor.getColumnIndex(LAST_NAME))

                    allUser = "$allUser\n$id $firstName $lastName"
                } while (cursor.moveToNext())
            }
        }
        cursor.close()
        db.close()
        return allUser
    }

    companion object {
        private val DB_NAME = "UsersDB"
        private val DB_VERSIOM = 1;
        private val TABLE_NAME = "users"
        private val ID = "id"
        private val FIRST_NAME = "FirstName"
        private val LAST_NAME = "LastName"
    }
}

Step 3. Add Button, TextView, EditText widget in MainActivity resource layout.

before adding, you must add a string in the resource. Its an always good practice you define your string in res/values/strings.xml.

<resources>
    <string name="app_name">SQLite Android Example</string>
    <string name="f_name">First Name</string>
    <string name="l_name">Last Name</string>
    <string name="save">Save</string>
    <string name="show">Show</string>
    <string name="empty"></string>
</resources>

now add following widget in activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="in.eyehunt.sqliteandroidexample.MainActivity">

    <EditText
        android:id="@+id/editText_firstName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="@string/f_name"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editText_lastName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="@string/l_name"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText_firstName" />

    <Button
        android:id="@+id/button_save"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="16dp"
        android:layout_marginLeft="16dp"
        android:layout_marginTop="32dp"
        android:text="@string/save"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText_lastName" />

    <Button
        android:id="@+id/button_show"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:layout_marginTop="32dp"
        android:text="@string/show"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText_lastName" />

    <TextView
        android:id="@+id/textView_show"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="136dp"
        android:text="@string/empty"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText_lastName" />
</android.support.constraint.ConstraintLayout>

Step 4. Add following code in MainAcitvity class

package `in`.eyehunt.sqliteandroidexample

import `in`.eyehunt.sqliteandroidexample.db.DatabaseHandler
import `in`.eyehunt.sqliteandroidexample.model.Users
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.Toast
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    var dbHandler: DatabaseHandler? = null

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

        //init db
        dbHandler = DatabaseHandler(this)

        //on Click Save button
        button_save.setOnClickListener(View.OnClickListener {
            // checking input text should not be null
            if (validation()){
                val user: Users = Users()
                var success: Boolean = false
                user.firstName = editText_firstName.text.toString()
                user.lastName = editText_lastName.text.toString()

                success = dbHandler!!.addUser(user)

                if (success){
                    val toast = Toast.makeText(this,"Saved Successfully", Toast.LENGTH_LONG).show()
                }
            }

        })

        //on Click show button
        button_show.setOnClickListener(View.OnClickListener {
               var user = dbHandler!!.getAllUsers()
            textView_show.setText(user)
        })

    }
    fun validation(): Boolean{
        var validate = false

        if (!editText_firstName.text.toString().equals("") &&
                !editText_lastName.text.toString().equals("")){
            validate = true
        }else{
            validate = false
            val toast = Toast.makeText(this,"Fill all details", Toast.LENGTH_LONG).show()
        }

        return validate
    }
}

Step 5. Now Run the application, in an emulator or On your Android device

Output screenshot SQLite database in Android app :

Video output 

Download Link and Source code SQLite database in Android in Github :

Note : This example (Project) is developed in Android Studio 3.0.1 ,tested on Android 7.1.1 ( Android Nougat), compile SDK version API 26: Android 8.0 (Oreo)

MinSdkVersion=”15″

TargetSdkVersion=”26″

Coding in Kotlin