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 on the Internet advise creating CRUD functions directly in a class inherited from SQLiteOpenHelper, immediately after onCreate() and onUpgrade() (let's name that class CustomSQLiteOpenHelper - because that's how I name it! :lol: ). For me, that approach is absolutely unacceptable. For each entity (for example, Emp, Dept, etc.), I want to create a separate class, dedicated to database 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 I want to clarify, 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.

STEP 1

Create a Kotlin file named Crudabe and copy next code into it (everything is explained in the comments):

Code: Select all
package <YOUR PACKAGE>

import android.content.ContentValues
import android.database.Cursor

interface Crudable {

    // ----------------------------------------------------------------------------------------------------------------------
    // Must be implemented by all model classes, representing different entities of the application (like Emp. Dept etc.).
    // Works in team with BaseCrud class which uses generic type parameter <T : Crudable> in its CRUD functions.
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    fun extractContentValues() : ContentValues
        // Called from insert() and update() of BaseCrud.

        // 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 BaseCrud.

        // 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) // how to pass column name (rather than index): 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


STEP 2

Create a Kotlin file named BaseCrud and copy next code into it:

Code: Select all
package <YOUR PACKAGE>

import android.content.Context
import kotlin.reflect.KFunction

abstract class BaseCrud (
    context : Context,
    protected val tableName : String,
    protected val idColumnName : String
) : CustomSQLiteOpenHelper(context) {

    // ----------------------------------------------------------------------------------------------------------------------
    // Incapsulates the typical CRUD functions applicable to DB entities.
    // In most cases, these functions can be used by the descendants directly, with no need to override.
    // But they can also be overridden if the descendant's logic is not so straightforward.
    // ----------------------------------------------------------------------------------------------------------------------
    // To utilize the CRUD functions of this class, different model (entity) classes (like Emp, Dept etc.)
    // must implement the Crudable interface and its functions extractContentValues() and populateFromCursor().
    // ----------------------------------------------------------------------------------------------------------------------
    // How to use this class:
    //
    // ### STEP1: Create a descendant (let's use EmpCrud as an example - a class which operates on Emp database table and,
    // accordingly, on objects of Emp model class):
    // class EmpCrud(context: Context) : BaseCrud(context, DbTable.EMP, DbColumn.ID) { }
    // Override some CRUD functions if needed, otherwise leave the class body empty.
    //
    // ### STEP2: Instantiate that descendant in the activities which will use it (like EmpListActivity and EmpEditActivity)
    // as a property:
    // private val empCrud = EmpCrud(context = this)
    //
    // That's it! Now, each function of the Activity can call the CRUD functions of empCrud. For example:
    //
    // newAutoincrementedId = empCrud.insert<Emp>(newEmp)
    // empCrud.update<Emp>(emp, id)
    // empCrud.delete<Emp>(id)
    // val allEmployees = empCrud.retrieveList<Emp>()
    // val activeEmployees = empCrud.retrieveListByWhereClause<Emp>("${DbColumn.IS_ACTIVE}=1")
    // val sql = "SELECT ${DbColumn.LAST_NAME} AS textValue FROM "${DbTable.EMP} ORDER BY ${DbColumn.LAST_NAME}"
    // val empLastNames = empCrud.retrieveListBySql<CrudableString>(sql)
    //
    // If no fitting function is found in empCrud, the Activity can use empCrud.writableDatabase & empCrud.readableDatabase.
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    open fun insert (
        entity : Crudable
    ) : Int {
        val cv = entity.extractContentValues()
        val newAutoincrementedId = this.writableDatabase.insert(this.tableName, null, cv)
        if (newAutoincrementedId == -1L) throw Exception("BaseCrud.insert() failed.")
        return newAutoincrementedId.toInt()
    }

    /***********************************************************************************************************************/
    open fun update ( // call if PK is complex or not an integer
        entity : Crudable,
        whereClause : String
    ) : Int {
        val cv = entity.extractContentValues()
        return writableDatabase.update(tableName, cv, whereClause, null)
    }

    /***********************************************************************************************************************/
    open fun update ( // call if PK is just an integer
        entity: Crudable,
        id : Int
    ) : Int {
        return update(entity = entity, whereClause = "${DbColumn.ID}=$id")
    }

    /***********************************************************************************************************************/
    open fun delete ( // call if PK is complex or not an integer
        whereClause : String
    ) : Int {
        return this.writableDatabase.delete(this.tableName, whereClause, null)
    }

    /***********************************************************************************************************************/
    open fun delete ( // call if PK is just an integer
        id : Int
    ) : Int {
        return this.delete(whereClause = "${DbColumn.ID}=$id")
    }

    // ----------------------------------------------------------------------------------------------------------------------
    // Functions which retrieve a list of records - retrieveList...
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    inline fun <reified T : Crudable> retrieveListBySql ( // any fields of any tables
        sql : String
    ) : ArrayList<T> {
        // The fields in the SELECT statement must absolutely correspond the fields, being copied
        // in extractContentValues() and populateFromCursor() of the class, passed as T.
        // If you need to retrieve a custom data set (for example, not all the fields of the underlying table
        // of the entity, or SELECT FROM a few joined tables), create (an pass to this function as a generic parameter)
        // a custom class which implements Crudable - just for that purpose.
        // If your custom SELECT has computed fields, give them aliases so they can be accessed by name.
        val entities = ArrayList<T>()
        val cursor = this.readableDatabase.rawQuery(sql, null)
            ?: throw Exception("BaseCrud.retrieveListBySql() - rawQuery() returned null cursor by '$sql'.")
        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> retrieveListByWhereClause ( // all the fields of the underlying table of the entity
        whereClause : String,
        orderByClause : String? = null
    ) : ArrayList<T> {
        var sql = "SELECT * FROM ${this.tableName} WHERE $whereClause"
        if (orderByClause != null) sql += " ORDER BY $orderByClause"
        return this.retrieveListBySql(sql)
    }

    /***********************************************************************************************************************/
    inline fun <reified T : Crudable> retrieveList() : ArrayList<T> { // all the fields of the underlying table of the entity
        return this.retrieveListBySql("SELECT * FROM ${this.tableName}")
    }

    // ----------------------------------------------------------------------------------------------------------------------
    // Functions which retrieve one single record - retrieveOne...
    // ----------------------------------------------------------------------------------------------------------------------

    /***********************************************************************************************************************/
    inline fun <reified T : Crudable> retrieveOneBySql ( // any fields of any tables
        sql : String
    ) : T {
        // See comment in retrieveListBySql.
        val entities : ArrayList<T> = this.retrieveListBySql(sql)
        if (entities.size != 1)
            throw Exception("BaseCrud.retrieveOneBySql() - ${entities.size} rows returned by '$sql'.")
        return entities[0]
    }

    /***********************************************************************************************************************/
    inline fun <reified T : Crudable> retrieveOneByWhereClause ( // all the fields of the underlying table of the entity
        whereClause : String
    ) : T {
        return this.retrieveOneBySql("SELECT * FROM ${this.tableName} WHERE $whereClause")
    }

    /***********************************************************************************************************************/
    inline fun <reified T : Crudable> retrieveOneById ( // all the fields of the underlying table of the entity
        id : Int
    ) : T {
        return this.retrieveOneByWhereClause("${this.idColumnName}=$id")
    }
} // class BaseCrud


STEP 3

Add to your CustomSQLiteOpenHelper:

Code: Select all
    override fun close() {
        this.writableDatabase.close()
        super.close()
    }
User avatar
Ursego
Site Admin
 
Posts: 139
Joined: 19 Feb 2013, 20:33



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




Traffic Counter

free counters

eXTReMe Tracker