28.4. Working with SQLite Databases: SQLiteOpenHelper Class

When developing Android applications, data management is often a critical component. One of the most common ways to store data locally within an Android app is through the use of SQLite databases. SQLite is a lightweight, disk-based database that doesn’t require a separate server process, making it ideal for mobile applications.

To effectively manage SQLite databases in Android, developers often use the SQLiteOpenHelper class, which helps in creating and managing database versions. This class provides a robust framework for handling database creation and version management, allowing developers to focus on the app's core functionality without worrying about the intricacies of database setup and migration.

Understanding SQLiteOpenHelper

The SQLiteOpenHelper class is an abstract class that simplifies the process of working with SQLite databases in Android. It provides two primary callback methods that you need to override:

• onCreate(SQLiteDatabase db): Called when the database is created for the first time. This is where you should create tables and initialize data.
• onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion): Called when the database needs to be upgraded. This is where you handle schema changes between versions.

By using these methods, you can manage database creation and version management efficiently. The SQLiteOpenHelper class also provides methods to get readable and writable database instances, making it easier to perform database operations.

Creating a SQLiteOpenHelper Class

To create a custom SQLiteOpenHelper class, you need to extend the SQLiteOpenHelper class and implement its abstract methods. Here’s a step-by-step guide to creating a simple SQLiteOpenHelper class:

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

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

    override fun onCreate(db: SQLiteDatabase) {
        // Create tables and initialize data
        val createTableQuery = """
            CREATE TABLE my_table (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                age INTEGER
            )
        """.trimIndent()
        db.execSQL(createTableQuery)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Handle database upgrades
        db.execSQL("DROP TABLE IF EXISTS my_table")
        onCreate(db)
    }
}

In this example, we define a database named mydatabase.db with a version number of 1. The onCreate() method executes a SQL statement to create a table named my_table with columns for id, name, and age. The onUpgrade() method drops the existing table and calls onCreate() to recreate it, which is a simple way to handle database upgrades.

Using SQLiteOpenHelper

Once you have your SQLiteOpenHelper class set up, you can use it to interact with the database. Here’s how you can use it to perform basic CRUD (Create, Read, Update, Delete) operations:

Create Operation

To insert data into the database, you can use the insert() method provided by the SQLiteDatabase class:

fun insertData(name: String, age: Int) {
    val db = this.writableDatabase
    val values = ContentValues().apply {
        put("name", name)
        put("age", age)
    }
    db.insert("my_table", null, values)
    db.close()
}

In this method, we get a writable database instance, create a ContentValues object to hold the data, and then insert it into the my_table.

Read Operation

To read data from the database, you can use the query() method:

fun readData(): List<String> {
    val db = this.readableDatabase
    val cursor = db.query("my_table", arrayOf("name", "age"), null, null, null, null, null)
    val dataList = mutableListOf<String>()

    with(cursor) {
        while (moveToNext()) {
            val name = getString(getColumnIndexOrThrow("name"))
            val age = getInt(getColumnIndexOrThrow("age"))
            dataList.add("Name: $name, Age: $age")
        }
    }
    cursor.close()
    db.close()
    return dataList
}

This method queries the my_table for all entries, iterates over the results using a cursor, and collects the data into a list of strings.

Update Operation

To update existing data, you can use the update() method:

fun updateData(id: Int, name: String, age: Int) {
    val db = this.writableDatabase
    val values = ContentValues().apply {
        put("name", name)
        put("age", age)
    }
    db.update("my_table", values, "id = ?", arrayOf(id.toString()))
    db.close()
}

Here, we update a specific row in the table by specifying the id of the row to update.

Delete Operation

To delete data, you can use the delete() method:

fun deleteData(id: Int) {
    val db = this.writableDatabase
    db.delete("my_table", "id = ?", arrayOf(id.toString()))
    db.close()
}

This method deletes a row from the table based on the specified id.

Handling Database Upgrades

One of the more challenging aspects of database management is handling upgrades. When you need to change the database schema, for example, by adding new columns or tables, you must increment the database version and handle the changes in the onUpgrade() method.

Here’s an example of how you might handle an upgrade that adds a new column:

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    if (oldVersion < 2) {
        db.execSQL("ALTER TABLE my_table ADD COLUMN email TEXT")
    }
    // Handle further upgrades
}

In this example, we check if the old version is less than 2 and, if so, add a new column named email to the existing table. This approach allows for incremental upgrades without losing existing data.

Best Practices

• Use Transactions: When performing multiple database operations, use transactions to ensure data integrity and improve performance.
• Close Resources: Always close database and cursor resources when done to prevent memory leaks.
• Use ContentValues: Use ContentValues for inserting and updating data to avoid SQL injection vulnerabilities.
• Version Management: Plan for future database upgrades by carefully managing version numbers and upgrade paths.

By following these best practices and utilizing the SQLiteOpenHelper class, you can effectively manage SQLite databases within your Android applications, ensuring efficient data storage and retrieval while maintaining data integrity through version upgrades.

Next page of the Free Ebook:

33Working with SQLite Databases: Performing CRUD Operations

8 minutes