Android – SQLite database handling with singleton class

SQLite

Android provides facility to store local data in SQL format using SQLite. SQLite is lightweight database management system used in android. You can also visit official SQLite site. To know more about SQLite please visit about page from SQLite. Please also checks datatypes supported by SQLite to use in app.

How to use SQLite in app?

To use SQLite in app we are going to use SQLiteOpenHelper class. Note that we are going to use use singleton class which only define once in application and this tutorial will in kotlin language. If you want to convert your java file into kotlin file than hit shift two times and write convert java file to kotlin, ensure that you have kotlin plugin enabled or you are using android studio 3.0 or grater.

Define DatabaseHelper

We are going to create DatabaseHelper class which will handle database related tasks like create or upgrade database schema. To use SQLiteOpenHelper class simply extends our DatabaseHelper class with SQLiteOpenHelper class like below.

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log

class DatabaseHelper private constructor(context: Context) : SQLiteOpenHelper(context, "LjDatabase", null, 1) {

    companion object {

        var database: SQLiteDatabase? = null
        fun initDatabase(context: Context) {
            database = DatabaseHelper(context).writableDatabase
        }
    }

    override fun onCreate(db: SQLiteDatabase?) {
        if (db != null) {
            val tableAppData = TableAppData()
            tableAppData.createTable(db)
        }
        Log.e("DB", "database created")
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        if (db != null) {
            val tableAppData = TableAppData()
            tableAppData.dropTable(db)
        }
        onCreate(db)
        Log.e("DB", "database upgrade")
    }
}

here, SQLiteOpenHelper takes four arguments context, databaseName, cursorFactory, databaseVersion. You can specify yours.

Add Table

To give common structure to all tables used in our database I have define one common abstract class. This class provide common methods, and variable.

import android.database.sqlite.SQLiteDatabase

abstract class TableBase {
    abstract fun createTable(db: SQLiteDatabase)
    abstract val TABLE_NAME: String

    fun dropTable(db: SQLiteDatabase) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
    }
}

Now we can define our table. I have used table to store my app language, you can also store it in sharedpreference, but Here it is demo.

class TableAppData : TableBase() {
    override val TABLE_NAME: String = "AppData"
    private val COLUMN_NAME: String = "name"
    private val COLUMN_VALUE: String = "value"

    override fun createTable(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_NAME TEXT, $COLUMN_VALUE TEXT)")
    }

    fun setAppLanguage(language: String) {
        val cursor = DatabaseHelper.database!!.rawQuery("SELECT $COLUMN_VALUE FROM $TABLE_NAME WHERE $COLUMN_NAME='language'", null)
        try {

            val values = ContentValues()
            values.put(COLUMN_NAME, "language")
            values.put(COLUMN_VALUE, language.toLowerCase())
            if (cursor.count > 0) {
                DatabaseHelper.database?.update(TABLE_NAME, values, null, null)
            } else {
                DatabaseHelper.database?.insert(TABLE_NAME, null, values)
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
        }
    }

    fun getAppLanguage(): String {
        var language = "en"
        val cursor = DatabaseHelper.database!!.rawQuery("SELECT $COLUMN_VALUE FROM $TABLE_NAME WHERE $COLUMN_NAME='language'", null)
        try {
            if (cursor.count > 0) {
                cursor.moveToFirst()
                language = cursor.getString(cursor.getColumnIndex(COLUMN_VALUE))
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
        }
        return language
    }

}

Here, what I have done is whenever I want to save language I will call setAppLanguage(myLanguage) and to retrive I will call getAppLanguage(). Our setAppLanguage method first checks if raw exists with language key then update it, else insert it. Our getAppLanguage method returns language if raw exist, else returns default language.
We are done with setup things. Now how to use this class in Activity or other class to get or set data in database.

Initialise database

Create application class for your project and assign it to application in Menifest file, if you have already one just initialise database

public class LjApplication extends Application {
@Override
    public void onCreate() {
        super.onCreate();
        DatabaseHelper.Companion.initDatabase(this);
    }
}

<application android:allowbackup="true" 
     android:icon="@mipmap/ic_launcher" 
     android:label="@string/app_name" 
     android:largeheap="true" 
     android:name=".LjApplication" 
     android:supportsrtl="true" 
     android:theme="@style/AppTheme">
     ...
     ...

<application>

Get or Set data

Now we can get data from everywhere and also set data in project without context. We just need use TableAppData instance to get or set data.

val language = TableAppData().getAppLanguage() // to get data from database
TableAppData().setAppLanguage(language) // to set data in database