Skip to content

Instantly share code, notes, and snippets.

@jugyo
Last active May 25, 2023 18:53
Show Gist options
  • Save jugyo/8b369a3233b925373a23cf756ca173bd to your computer and use it in GitHub Desktop.
Save jugyo/8b369a3233b925373a23cf756ca173bd to your computer and use it in GitHub Desktop.

LiteSQLite

A thin wrapper library for android.database.sqlite.SQLiteOpenHelper, providing enhanced database operation capabilities through the use of Kotlin Coroutines.

Usage

val liteSQLite = LiteSQLite(context, "TestDatabase.db", 1)

liteSQLite.execSQL(
    """
    DROP TABLE IF EXISTS user;
    """,
    """
    CREATE TABLE user (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        gender TEXT NOT NULL
    )            
    """,
    """INSERT INTO user (id, name, gender) VALUES (0, 'John Doe', 'Male');""",
    """INSERT INTO user (id, name, gender) VALUES (1, 'Sam Smith', 'Male');""",
    """INSERT INTO user (id, name, gender) VALUES (2, 'Jane Doe', 'Female');""",
    """INSERT INTO user (id, name, gender) VALUES (3, 'Emma Brown', 'Female');"""
)

data class User(val id: Int, val name: String, val gender: String)

val records = liteSQLite.rawQuery("SELECT * FROM user ORDER BY id", null) { cursor ->
    User(
        id = cursor["id"] ?: 0,
        name = cursor["name"] ?: "",
        gender = cursor["gender"] ?: "",
    )    
}
import android.database.Cursor
inline operator fun <reified T> Cursor.get(column: String): T? {
val columnIndex = getColumnIndex(column)
if (columnIndex == -1) {
// Column not found
return null
}
return when (T::class) {
Int::class -> getInt(columnIndex)
String::class -> getString(columnIndex)
Double::class -> getDouble(columnIndex)
Float::class -> getFloat(columnIndex)
Short::class -> getShort(columnIndex)
ByteArray::class -> getBlob(columnIndex)
else -> null
} as T?
}
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext
/**
* - This database helper class can be used to complement another OR mapper library.
* - It does not take care of database migration.
* - Don't forget to specify the database version you are using with your OR mapper.
*/
class LiteSQLite(
context: Context,
databaseName: String,
databaseVersion: Int
) :
SQLiteOpenHelper(
context, databaseName,
null,
databaseVersion
) {
override fun onCreate(db: SQLiteDatabase?) {
// Do nothing
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
// Do nothing
}
/**
* Executes a raw SQL query and returns a list of entity.
*
* @param sql the SQL query to execute
* @param selectionArgs the arguments to replace placeholders in the SQL query
* @param mapper the function that maps raw data to an entity class instance
* @return a List<T>
*/
suspend fun <T> rawQuery(
sql: String,
selectionArgs: List<String>? = null,
mapper: (Cursor) -> T
): List<T> = withContext(Dispatchers.IO) {
val cursor = readableDatabase.rawQuery(sql, selectionArgs?.toTypedArray())
val records = mutableListOf<T>()
while (cursor.moveToNext()) {
records.add(mapper(cursor))
}
cursor.close()
records
}
suspend fun execSQL(
vararg sql: String
) = withContext(Dispatchers.IO) {
sql.forEach {
writableDatabase.execSQL(it)
}
}
suspend fun execSQL(
sql: String,
bindArgs: List<String>
) = withContext(Dispatchers.IO) {
writableDatabase.execSQL(sql, bindArgs.toTypedArray())
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment