SQLite in Android

1. Introduction to SQLite in Android

SQLite is an embedded database engine that is self-contained, serverless, and zero-configuration. It’s included in Android by default, making it a convenient choice for apps needing local storage. SQLite supports standard SQL queries, transactions, and data manipulation.

2. Setting Up the Project

To start, create a new Android project in Android Studio and ensure that the necessary dependencies are added. The following dependencies should be included in your build.gradle (app-level) file:

dependencies {
    implementation "androidx.core:core-ktx:1.9.0"
    implementation "androidx.appcompat:appcompat:1.5.1"
    implementation "com.google.android.material:material:1.7.0"
    implementation "androidx.constraintlayout:constraintlayout:2.1.4"
}

3. Creating the Database Schema

Define the schema for your SQLite database. For this example, let’s create a simple users table with columns for id, name, and age.

4. Database Helper Class

Create a class that extends SQLiteOpenHelper to manage database creation and version management. This class will handle the creation, updating, and management of the database.

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

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

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

        private const val TABLE_NAME = "users"
        private const val COLUMN_ID = "id"
        private const val COLUMN_NAME = "name"
        private const val COLUMN_AGE = "age"

        private const val SQL_CREATE_ENTRIES =
            "CREATE TABLE $TABLE_NAME (" +
                    "$COLUMN_ID INTEGER PRIMARY KEY," +
                    "$COLUMN_NAME TEXT," +
                    "$COLUMN_AGE INTEGER)"

        private const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS $TABLE_NAME"
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_ENTRIES)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL(SQL_DELETE_ENTRIES)
        onCreate(db)
    }
}

5. CRUD Operations

Next, implement the CRUD (Create, Read, Update, Delete) operations in the DatabaseHelper class.

Inserting Data

import android.content.ContentValues

fun insertUser(name: String, age: Int): Long {
    val db = writableDatabase
    val values = ContentValues().apply {
        put(COLUMN_NAME, name)
        put(COLUMN_AGE, age)
    }
    return db.insert(TABLE_NAME, null, values)
}

Reading Data

import android.database.Cursor

fun getAllUsers(): List<User> {
    val db = readableDatabase
    val projection = arrayOf(COLUMN_ID, COLUMN_NAME, COLUMN_AGE)
    val cursor: Cursor = db.query(
        TABLE_NAME,
        projection,
        null,
        null,
        null,
        null,
        null
    )

    val users = mutableListOf<User>()
    with(cursor) {
        while (moveToNext()) {
            val userId = getLong(getColumnIndexOrThrow(COLUMN_ID))
            val userName = getString(getColumnIndexOrThrow(COLUMN_NAME))
            val userAge = getInt(getColumnIndexOrThrow(COLUMN_AGE))
            users.add(User(userId, userName, userAge))
        }
    }
    cursor.close()
    return users
}

Updating Data

fun updateUser(id: Long, name: String, age: Int): Int {
    val db = writableDatabase
    val values = ContentValues().apply {
        put(COLUMN_NAME, name)
        put(COLUMN_AGE, age)
    }
    val selection = "$COLUMN_ID = ?"
    val selectionArgs = arrayOf(id.toString())
    return db.update(TABLE_NAME, values, selection, selectionArgs)
}

Deleting Data

fun deleteUser(id: Long): Int {
    val db = writableDatabase
    val selection = "$COLUMN_ID = ?"
    val selectionArgs = arrayOf(id.toString())
    return db.delete(TABLE_NAME, selection, selectionArgs)
}

6. Using the Database in Your App

To use the database helper in your app, instantiate it and call its methods. For example, to insert a new user:

val dbHelper = DatabaseHelper(context)
val newUserId = dbHelper.insertUser("John Doe", 30)

To read all users:

val users = dbHelper.getAllUsers()
for (user in users) {
    println("User: ${user.name}, Age: ${user.age}")
}

7. Best Practices

• Use Singleton Pattern: Ensure a single instance of the database helper is used throughout the app to prevent memory leaks and redundant database connections.
• Use Async Tasks: Perform database operations on a background thread to avoid blocking the UI thread.
• Input Validation: Validate user inputs before inserting or updating data in the database.
• Database Migration: Handle database version upgrades and migrations carefully to preserve user data.

8. Conclusion

SQLite is a powerful tool for local data storage in Android apps. By following this guide, you can create a robust and efficient local database using SQLite and Kotlin. Remember to adhere to best practices to ensure smooth and efficient data handling in your applications.

This guide covers the fundamental aspects of integrating SQLite in an Android app with Kotlin. With this foundation, you can build more complex and feature-rich applications that handle data efficiently and securely. Happy coding!