Utilizing SQLite Database in Android

SQLite is a popular and lightweight database management system native to Android. In this article, we will explore an alternative method to work with SQLite databases in Android using Kotlin without relying on Room. Instead, we will use the SQLiteDatabase class directly to create and manage the database. We will create a sample application to demonstrate CRUD (Create, Read, Update, Delete) operations using this approach.

Implementation steps:

Step 1: Creating the Database Helper Class

We need to create a helper class that will be responsible for managing database creation, upgrades, and versioning.

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

data class YourDataModel(
    val id: Long,
    val name: String,
    val age: Int
)

class DatabaseHelper(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "my_database.db"
        private const val DATABASE_VERSION = 1

        // Define table and column names
        const val TABLE_NAME = "my_table"
        const val COLUMN_ID = "id"
        const val COLUMN_NAME = "name"
        const val COLUMN_AGE = "age"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val createTableQuery = """
            CREATE TABLE $TABLE_NAME (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_NAME TEXT,
                $COLUMN_AGE INTEGER
            )
        """.trimIndent()

        db.execSQL(createTableQuery)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Add any upgrade logic here if needed
    }

    fun insertData(name: String, age: Int): Long {
        val values = ContentValues().apply {
            put(COLUMN_NAME, name)
            put(COLUMN_AGE, age)
        }

        return writableDatabase.insert(TABLE_NAME, null, values)
    }

    fun readData(): List<YourDataModel> {
        val dataList = mutableListOf<YourDataModel>()
        val cursor: Cursor = readableDatabase.query(TABLE_NAME, null, null, null, null, null, null)

        with(cursor) {
            while (moveToNext()) {
                val id = getLong(getColumnIndexOrThrow(COLUMN_ID))
                val name = getString(getColumnIndexOrThrow(COLUMN_NAME))
                val age = getInt(getColumnIndexOrThrow(COLUMN_AGE))

                dataList.add(YourDataModel(id, name, age))
            }
        }

        cursor.close()
        return dataList
    }

    fun updateData(id: Long, name: String, age: Int): Int {
        val values = ContentValues().apply {
            put(COLUMN_NAME, name)
            put(COLUMN_AGE, age)
        }

        val selection = "$COLUMN_ID = ?"
        val selectionArgs = arrayOf(id.toString())

        return writableDatabase.update(TABLE_NAME, values, selection, selectionArgs)
    }

    fun deleteData(id: Long): Int {
        val selection = "$COLUMN_ID = ?"
        val selectionArgs = arrayOf(id.toString())

        return writableDatabase.delete(TABLE_NAME, selection, selectionArgs)
    }
}

Step 2: Using the Database in Your Activities/Fragments

Finally, we can use the DatabaseHelper class in our activities or fragments to perform database operations.

class MainActivity : AppCompatActivity() {

    private lateinit var databaseHelper: DatabaseHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        databaseHelper = DatabaseHelper(this)

        // Example: Inserting data
        val insertedId = databaseHelper.insertData("John Doe", 30)

        // Example: Retrieving data
        val dataList = databaseHelper.readData()
        for (item in dataList) {
            Log.d("Database", "ID: ${item.id}, Name: ${item.name}, Age: ${item.age}")
        }

        // Example: Updating data
        databaseHelper.updateData(insertedId, "Jane Doe", 31)

        // Example: Deleting data
        databaseHelper.deleteData(insertedId)
    }

    override fun onDestroy() {
        super.onDestroy()
        databaseHelper.close()
    }
}