SQLite Basics

Initializing SQLiteOpenHelper

private val wdb: SQLiteDatabase = writableDatabase
private val rdb: SQLiteDatabase = readableDatabase

NOTE : onCreate() is called when writableDatabase is called for the first time.

Creating SQLiteOpenHelper() instance does not call onCreate().
This means memeber value for SQLiteDatabase cannot be lateinit on onCreate() and used in other methods without calling writableDatabase at some point.
Thus, in order to initialize database on instance creation, or avoid lateinit exceptions, call writableDatabase on init() or use lazy.

Using Database

sqliteDB = SQLiteAccessHelper(this, SQLiteAccessHelper.dbName, null, 1)

Create an instance on onCreate() of an activity to access the database.
Because it requires context for access, making the openhelper into a singleton is not such a good idea.

Example Code

class SQLiteAccessHelper(
context : Context, 
name : String, 
factory: SQLiteDatabase.CursorFactory?, 
version:Int
) : SQLiteOpenHelper(context, name, factory, version){

    private val wdb: SQLiteDatabase = writableDatabase
    private val rdb: SQLiteDatabase = readableDatabase

    companion object{
        final const val dbName = "todo.db"
    }

    private final val TABLENAME = "todo"
    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
    }
    override fun onCreate(db: SQLiteDatabase) {

        val queryCreateTable : String = """
            CREATE TABLE IF NOT EXISTS $TABLENAME 
            ( 
            _id integer primary key autoincrement, 
            title text,
            content text,
            regdate text,
            isDone text
            );
        """.trimIndent()
        db.execSQL(queryCreateTable)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {

    }

    fun insert(data : DtoToDo) : DtoToDo? {
        var inserted : DtoToDo? = null
        val contentValues = ContentValues()
        contentValues.put(DtoToDo::title.name, data.title)
        contentValues.put(DtoToDo::content.name, data.content)
        contentValues.put(DtoToDo::regDate.name, data.regDate)
        contentValues.put(DtoToDo::isDone.name, data.isDone)

        wdb.beginTransaction()
        val result = wdb.insert(TABLENAME, null, contentValues)
        if(result > 0) {
            wdb.setTransactionSuccessful()
            inserted = data.copy(id = result)
        }
        wdb.endTransaction()
        return inserted
    }
    fun selectAll() : List<DtoToDo> {
        val list : MutableList<DtoToDo> = mutableListOf()
        val query = """
            SELECT _id, title, content, regdate, isDone
            FROM $TABLENAME
        """.trimIndent()
        rdb.rawQuery(query, null).use { cursor->
            while(cursor.moveToNext()){
                list.add(DtoToDo(
                    id = cursor.getLong(0),
                    title = cursor.getString(1),
                    content = cursor.getString(2),
                    regDate = cursor.getString(3),
                    isDone = cursor.getString(4) == "T"
                ))
            }
        }
        return list
    }
    fun select(id : Int) : DtoToDo? {
        var data : DtoToDo? = null
        val query = """
            SELECT _id, title, content, regdate, isDone
            FROM $TABLENAME
            WHERE _id = $id
        """.trimIndent()
        rdb.rawQuery(query, null).use { cursor->
            while(cursor.moveToNext()){
                data = DtoToDo(
                    id = cursor.getLong(0),
                    title = cursor.getString(1),
                    content = cursor.getString(2),
                    regDate = cursor.getString(3),
                    isDone = cursor.getString(4) == "T"
                )
            }
        }
        return data;
    }
    fun update(data : DtoToDo){
        val contentValues = ContentValues()
        contentValues.put(DtoToDo::title.name, data.title)
        contentValues.put(DtoToDo::content.name, data.content)
        contentValues.put(DtoToDo::isDone.name, data.isDone)
        val where = """
            _id = ?
        """.trimIndent()

        wdb.beginTransaction()
        val result = wdb.update(TABLENAME, contentValues, where, arrayOf(data.id.toString()))

        if(result > 0){
            wdb.setTransactionSuccessful()
        }
        wdb.endTransaction()
    }
    fun delete(id : Int){
        val where = """
            _id = ?
        """.trimIndent()

        wdb.beginTransaction()
        val result = wdb.delete(TABLENAME, where, arrayOf(id.toString()))

        if(result > 0){
            wdb.setTransactionSuccessful()
        }
        wdb.endTransaction()
    }
}