Kotlin: Generic class with SQLiteDatabase CRUD functions


Topics interesting only for Kotlin / Android / SQLite programmers

Link to this posting

Postby Ursego » 13 Dec 2019, 18:50

Most examples in books and Internet sites suggest creating CRUD functions directly in a class inherited from SQLiteOpenHelper, immediately after onCreate() and onUpgrade() (let's name that class CustomSQLiteOpenHelper). For me, that approach is absolutely unacceptable. For each entity (for example, Emp, Dept, etc.), I prefer to create a separate class, dedicated to DB operations only with that entity (for example, EmpCrud, DeptCrud, etc.) - instead of dumping everything into one the monstrous and messy heap inside CustomSQLiteOpenHelper.

In most cases, CRUD functions in different classes are similar (the only difference is how you populate ContentValues and extract fields from Cursor, or in SQLs), or even absolutely identical (code, which deletes by ID, can probably be reused by almost all the entities). Since I hate code duplication, I don't want to create 50 classes which look like enzygotic twins. Unfortunately, even the rarely found examples, which suggest to dedicate separate classes to CRUD operations on different entities, promote endless copy-paste. I have no idea why all those smart people, who wrote all these book and articles, didn't encapsulate the common logic in a generic class, but I will go against the mainstream.

You can reuse my solution, if you want. But it's important to mention, that the functionality is still under construction - in fact, I started to learn Kotlin & Android 2 weeks ago, and wrote the provided code while creating my first learning-purpose app (I just started creating it, ha-ha!). So, I believe, the solution is raw (and, for sure, not tested well). If you find a bug or have an idea how to improve - create a comment in this topic, or send me an email to ursego@gmail.com.

STEPS:

@ Perform the steps, described here.

@ Create "db" package, where you will put the stuff, related to database manipulations, specific to this application. Pay attention, that any stuff, which doesn't deal with the entities of this app (and, hence, can be reused in other apps), should be placed in the "util" package - even if it DB-related.

@ In "db" package, create a Kotlin file named DbInfo and copy the following code into it - just after the "package" directive (change the DB name to the actual one):

Code: Select all
object DbInfo {
    const val NAME = "<YOUR DB NAME>.db"
    const val VERSION = 1 // increment if you change the database schema
}

@ In "db" package, create a Kotlin file named DbTable. That object will contain the names of the DB tables, for example:

Code: Select all
object DbTable {
    const val EMP = "emp"
    const val DEPT = "dept"
}

@ In "db" package, create a Kotlin file named DbColumn. That object will contain the names of the DB tables' columns, for example:

Code: Select all
object DbColumn {
    const val ID = "_id"
    const val FIRST_NAME = "first_name"
    const val LAST_NAME = "last_name"
    const val DOB = "dob"
    const val IS_ACTIVE = "is_active"
}

We create one object which will contain all the columns of all the tables (rather than a dedicated object for each table) because a same column can exist in many tables, and we want to ensure consistency all over the application (after all, that's why we use constants!). That also obeys the DRY principle (Don't Repeat Yourself) - we don't duplicate a same column name constant in many places. Obviously, we will use these constants to build the CREATE TABLE statements. So, if the column name is "dob", it will be "dob" everywhere - not "dob" in one table, "birth_date" in another table, and "date_of_birth" in another table.

@ In "db" package, create a Kotlin file named CustomSQLiteOpenHelper and copy the following code into it - just after the "package" directive:

Code: Select all
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

open class CustomSQLiteOpenHelper(context: Context): SQLiteOpenHelper(context, DbInfo.NAME, null, DbInfo.VERSION) {
    /***********************************************************************************************************************/
    override fun onCreate(db: SQLiteDatabase) {
        this.createDbObjects(db)
    }
    /***********************************************************************************************************************/
    fun createDbObjects(db: SQLiteDatabase) {
        // Extracted from onCreate() of this object to allow the logic be applied many times from onCreate() of the main
        // Activity in debug purpose. In production, will be called only once, from onCreate() of this object.

        var sql: String

//        var sql: String = "DROP TABLE IF EXISTS " + DbTable.XXX // that allows to call this function many times in debug purposes
//        db.execSQL(sql)

//        sql = "CREATE TABLE " + DbTable.XXX + " (" +
//                DbColumn.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
//                DbColumn.FIELD_1 + " TEXT NOT NULL, " +
//                DbColumn.FIELD_2 + " INTEGER NOT NULL DEFAULT 0" +
//                ")"
//        db.execSQL(sql)
    }
    /***********************************************************************************************************************/
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        //TODO
    }
    /***********************************************************************************************************************/
    override fun close() {
        this.writableDatabase.close()
        super.close()
    }
    /***********************************************************************************************************************/
}

Interface Crudable

This interface forces you to write "boring" technical code (population of ContentValues and reading from Cursor) separately from the "interesting" business logic, which makes that logic easier to write and, later, understand.

@ In "util" package, create a Kotlin file named Crudabe and copy the following code into it - just after the "package" directive (everything is explained in the comments):

Code: Select all
import android.content.ContentValues
import android.database.Cursor
import <YOUR BASE PACKAGE>.db.DbColumn

// --------------------------------------------------------------------------------------------------------------------------
// Must be implemented by all model classes, representing different entities of the application (like Emp, Dept etc.).
// That will allow CrudHelper class to manipulate with those classes in its CRUD functions.
// This interface forces you to write pure technical boilerplate code (population of ContentValues and reading from Cursor)
// separately from the business logic, which makes that logic easier to write and, later, understand.
// http://code.intfast.ca/viewtopic.php?t=815
// --------------------------------------------------------------------------------------------------------------------------

interface Crudable {
    /***********************************************************************************************************************/
    val TABLE_NAME: String
    /***********************************************************************************************************************/
    val ID_COL_NAME: String
        get() = DbColumn.ID // override if the ID column name is not "_id"
    /***********************************************************************************************************************/
    var id: Int?
    // Override this way:
    // override var id: Int? = null
    /***********************************************************************************************************************/
    fun extractContentValues(): ContentValues
    // Called from insert() and update() of CrudHelper.

    // Just copy-paste to the descendant and customize according to the fields in that descendant:
    //    override fun extractContentValues() : ContentValues {
    //        val cv = ContentValues()
    //        // DON'T PUT PK FIELD(S) - PK IS AUTOGENERATED (ON INSERT) OR SUPPLIED WITHIN WHERE CLAUSE (ON UPDATE)
    //        cv.put(DbColumn.FIRST_NAME, this.firstName)
    //        cv.put(DbColumn.LAST_NAME, this.lastName)
    //        cv.put(DbColumn.DOB, this.dob)
    //        cv.put(DbColumn.IS_ACTIVE, this.isActive)
    //        return cv
    //    }
    /***********************************************************************************************************************/
    fun populateFromCursor(cursor: Cursor)
    // Called from retrieveListBySql() of CrudHelper.

    // Just copy-paste to the descendant and customize according to the fields in that descendant:
    //    override fun populateFromCursor(cursor: Cursor) {
    //        this.id = cursor.getInt(DbColumn.ID) // http://code.intfast.ca/viewtopic.php?t=814
    //        this.firstName = cursor.getString(DbColumn.FIRST_NAME)
    //        this.lastName = cursor.getString(DbColumn.LAST_NAME)
    //        this.dob = cursor.getLocalDate(DbColumn.DOB)
    //        this.isActive = cursor.getBoolean(DbColumn.IS_ACTIVE)
    //    }
    /***********************************************************************************************************************/
} // interface Crudable

Class CrudHelper

The main working horse of the functionality. Encapsulates population of ContentValues and reading from Cursor, so you will never write these loops anymore among your business logic. Has the following functions which operate on objects, implementing the Crudable interface:

* insert()
* update()
* delete()
* retrieveList() - retrieves a recordset (ArrayList); a few overloads
* retrieveOne() - retrieves a single record; a few overloads


@ In "util" package, create a Kotlin file named CrudHelper and copy the following code into it - just after the "package" directive:

Code: Select all
import android.content.Context
import <YOUR BASE PACKAGE>.db.CustomSQLiteOpenHelper
import kotlin.reflect.KFunction

// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// Before you add this class to your app, create CustomSQLiteOpenHelper: http://code.intfast.ca/viewtopic.php?t=815
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

open class CrudHelper(context: Context): CustomSQLiteOpenHelper(context) {
    // ----------------------------------------------------------------------------------------------------------------------
    // Encapsulates the typical CRUD functions applicable to DB entities.
    // In most cases, this class can be instantiated and used directly, with no need to be extended.
    // But if some entity's CRUD logic is less straightforward, you can inherit from CrudHelper and add/override functions:
    // class DeptCrudHelper(context: Context): CrudHelper(context) { ... }
    // ----------------------------------------------------------------------------------------------------------------------
    // Model (entity) classes (like Emp, Dept), for which you want to call CRUD functions, must implement Crudable interface.
    // That will allow CrudHelper to accept objects of these classes as an input parameter, and return them populated.
    // ----------------------------------------------------------------------------------------------------------------------
    // How to use this class:
    //
    // Instantiate CrudHelper in the activity which will use it (like EmpListActivity and EmpEditActivity) as a property:
    // private val crudHelper = CrudHelper(context = this)
    //
    // That's it! Now, each function of the Activity can call the CRUD functions of crudHelper. For example:
    //
    // val emp = Emp()
    // newAutoincrementedId = crudHelper.insert(emp)
    // crudHelper.update(emp)
    // crudHelper.delete(emp)
    // empWithWorkerNumber25 = crudHelper.retrieveOne<Emp>(DbTable.EMP, "${DbColumn.WORKER_NUMBER} = 25")
    // val allEmployees = crudHelper.retrieveList<Emp>()
    // val activeEmployees = crudHelper.retrieveList<Emp>(tableName = DbTable.EMP, whereClause = "${DbColumn.IS_ACTIVE}=1")
    // val sql = "SELECT ${DbColumn.LAST_NAME} AS textValue FROM "${DbTable.EMP} ORDER BY ${DbColumn.LAST_NAME}"
    // val empLastNames = crudHelper.retrieveList<CrudableString>(sql)
    //
    // If no fitting function is found in crudHelper, the Activity can call the functions of
    // crudHelper.writableDatabase & crudHelper.readableDatabase directly.
    // ----------------------------------------------------------------------------------------------------------------------

    // ----------------------------------------------------------------------------------------------------------------------
    // DML:
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    open fun insert(entity: Crudable, idAutoIncrement: Boolean = true): Int {
        if (idAutoIncrement && entity.id != null)
            throw Exception("CrudHelper.insert(): entity.id must be null (not ${entity.id}) since idAutoIncrement = true.")
        val cv = entity.extractContentValues()
        val rowId = this.writableDatabase.insert(entity.TABLE_NAME, null, cv)
        if (rowId == -1L) throw Exception("CrudHelper.insert() failed.")
        if (idAutoIncrement) entity.id = rowId.toInt()
        return rowId.toInt()
    }
    /***********************************************************************************************************************/
    open fun update(entity: Crudable, whereClause: String? = null): Int {
        // If whereClause is not supplied, this fun updates by entity.id.
        val cv = entity.extractContentValues()
        val finalWhereClause = whereClause ?: "${entity.ID_COL_NAME}=${entity.id}"
        return writableDatabase.update(entity.TABLE_NAME, cv, finalWhereClause, null)
    }
    /***********************************************************************************************************************/
    open fun delete(entity: Crudable): Int {
        // If deleting condition is more complicated that simply by ID, then call
        // writableDatabase.delete directly, supplying the corresponding WHERE clause.
        return this.writableDatabase.delete(entity.TABLE_NAME, "${entity.ID_COL_NAME}=${entity.id}", null)
    }
    /***********************************************************************************************************************/

    // ----------------------------------------------------------------------------------------------------------------------
    // retrieveList() [retrieves a recordset]:
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    inline fun <reified T: Crudable> retrieveList(sqlSelect: String, selectionArgs: Array<String>? = null): ArrayList<T> {
        // The fields in the SELECT statement must absolutely correspond the fields, copied
        // in extractContentValues() and populateFromCursor() of the class, passed as T.

        // If you need to SELECT FROM a few joined tables, create (an pass to this function as <T>)
        // a custom class which implements Crudable - just for that purpose. In this case, follow these rules:
        //      1. If the SQL SELECT has computed fields, give them aliases so they can be accessed by name.
        //      2. Override TABLE_NAME to simply return an empty string.
        //      3. Override extractContentValues() as doing nothing (no INSERTs or UPDATEs).
        //      4. Override populateFromCursor() properly (it's used on retrieval).
        val entities = ArrayList<T>()
        val cursor = this.readableDatabase.rawQuery(sqlSelect, selectionArgs)
            ?: throw Exception("CrudHelper.retrieveList(): rawQuery() returned null cursor by '$sqlSelect'.")
        cursor.use {
            while (cursor.moveToNext()) {
                // The following two code lines is a dirty trick to create an instance of a generic type.
                // To enable that, the generic parameter is marked as reified. That is possible only
                // in inline functions, so this function and all its callers are converted to inline.
                // http://code.intfast.ca/viewtopic.php?t=816
                val actualRuntimeClassConstructor: KFunction<T> = T::class.constructors.first()
                val entity: T = actualRuntimeClassConstructor.call()

                entity.populateFromCursor(cursor)
                entities.add(entity)
            }
        }

        return entities
    }
    /***********************************************************************************************************************/
    inline fun <reified T: Crudable> retrieveList
                (tableName: String, whereClause: String? = null, orderByClause: String? = null): ArrayList<T> {
        val sql = StringBuffer("SELECT * FROM $tableName")
        if (whereClause != null) sql.append(" WHERE $whereClause")
        if (orderByClause != null) sql.append(" ORDER BY $orderByClause")
        return this.retrieveList(sql.toString())
    }
    /***********************************************************************************************************************/

    // ----------------------------------------------------------------------------------------------------------------------
    // retrieveOne() [retrieves one single record]:
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    inline fun <reified T: Crudable> retrieveOne
                (sqlSelect: String, selectionArgs: Array<String>? = null, required: Boolean = false): T? {
        val entities: ArrayList<T> = this.retrieveList(sqlSelect, selectionArgs)
        return when (entities.size) {
            1 -> entities[0]
            0 -> {
                if (required) throw Exception("CrudHelper.retrieveOne(): no data found by '$sqlSelect'.")
                null
            }
            else -> throw
                Exception("CrudHelper.retrieveOne(): ${entities.size} rows returned by '$sqlSelect' while one row expected.")
        }
    }
    /***********************************************************************************************************************/
    inline fun <reified T: Crudable> retrieveOne
                (tableName: String, id: Int, idColName: String = "_id", required: Boolean = true): T? {
        return this.retrieveOne(sqlSelect = "SELECT * FROM $tableName WHERE $idColName=$id", required = required)
    }
    /***********************************************************************************************************************/
    inline fun <reified T: Crudable> retrieveOne
                (tableName: String, whereClause: String, selectionArgs: Array<String>? = null, required: Boolean = true): T? {
        return this.retrieveOne(sqlSelect = "SELECT * FROM $tableName WHERE $whereClause",
            selectionArgs = selectionArgs, required = required)
    }
    /***********************************************************************************************************************/
} // class CrudHelper

@ In "util" package, create this constant:

Code: Select all
const val g_DEBUG_MODE = true // TODO: must be false when promoted to production

@ Add to onCreate() of the MainActivity:

Code: Select all
        // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Comment out this fragment when CustomSQLiteOpenHelper.createDbObjects() has been successfully debugged:
        if (g_DEBUG_MODE) {
            val customSQLiteOpenHelper = CustomSQLiteOpenHelper(this)
            customSQLiteOpenHelper.createDbObjects(customSQLiteOpenHelper.writableDatabase)
        }
        // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

@ You have created the class CustomSQLiteOpenHelper with the function createDbObjects() having commented-out sample code. Now it's time to uncomment and customize it, so the function will create your table(s). When that function works perfectly, comment out the fragment, added to onCreate() of the MainActivity in the previous step (or make g_DEBUG_MODE false).
User avatar
Ursego
Site Admin
 
Posts: 140
Joined: 19 Feb 2013, 20:33



IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
Traffic Counter

free counters

eXTReMe Tracker