28.2. Working with SQLite Databases: Setting up SQLite in Android Projects

Android app development often requires the use of a database system to manage data efficiently. One of the most popular choices for local storage is SQLite, a lightweight, disk-based database that doesn’t require a separate server process. This makes it a perfect fit for mobile applications where simplicity and performance are crucial. In this section, we will delve into setting up SQLite in Android projects using Kotlin, covering everything from initial setup to basic CRUD operations.

SQLite is embedded within Android, which means there’s no need to perform any installation steps. However, setting it up within your project involves several key steps. Let’s walk through these steps to integrate SQLite into your Android app using Kotlin.

1. Understanding SQLiteOpenHelper

The first step in using SQLite in an Android application is to create a subclass of SQLiteOpenHelper. This class helps in managing database creation and version management. It provides two abstract methods that you must override:

• onCreate(): This method is called when the database is created for the first time. Here, you should define the schema of your database, typically by executing SQL statements to create tables.
• onUpgrade(): This method is called when the database needs to be upgraded, such as when you change the database schema. It allows you to handle the migration of data from the old schema to the new schema.

Here is an example of how you might implement a simple SQLiteOpenHelper subclass in Kotlin:

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

    override fun onCreate(db: SQLiteDatabase) {
        val createTableSQL = "CREATE TABLE $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY, $COLUMN_NAME TEXT)"
        db.execSQL(createTableSQL)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }

    companion object {
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "MyDatabase.db"
        const val TABLE_NAME = "MyTable"
        const val COLUMN_ID = "id"
        const val COLUMN_NAME = "name"
    }
}

2. Creating and Accessing the Database

Once you have your SQLiteOpenHelper subclass, you can use it to create and access your database. You typically instantiate your helper class in your activity or wherever you need database access and call getWritableDatabase() or getReadableDatabase() to get a database object.

val dbHelper = MyDatabaseHelper(context)
val db = dbHelper.writableDatabase

With the database object, you can perform SQL queries to interact with your database.

3. Performing CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations you can perform on a database. Here’s how you can perform these operations using SQLite in an Android app:

Create

To insert data into the database, you can use the insert() method of the SQLiteDatabase class. This method requires a table name and a ContentValues object, which holds the column names and their corresponding values.

val values = ContentValues().apply {
    put(COLUMN_NAME, "Example Name")
}

val newRowId = db.insert(TABLE_NAME, null, values)

Read

To read data from the database, you can use the query() method. This method allows you to specify which columns to return, the selection criteria, and the sort order.

val projection = arrayOf(COLUMN_ID, COLUMN_NAME)

val cursor = db.query(
    TABLE_NAME,
    projection,
    null,
    null,
    null,
    null,
    null
)

with(cursor) {
    while (moveToNext()) {
        val itemId = getLong(getColumnIndexOrThrow(COLUMN_ID))
        val itemName = getString(getColumnIndexOrThrow(COLUMN_NAME))
        // Use the data
    }
}
cursor.close()

Update

To update existing rows in the database, use the update() method. This method requires the table name, a ContentValues object with updated values, and a selection criteria to specify which rows to update.

val values = ContentValues().apply {
    put(COLUMN_NAME, "Updated Name")
}

val selection = "$COLUMN_ID = ?"
val selectionArgs = arrayOf("1")

val count = db.update(
    TABLE_NAME,
    values,
    selection,
    selectionArgs
)

Delete

To delete rows from the database, use the delete() method. This method requires the table name and a selection criteria to specify which rows to delete.

val selection = "$COLUMN_ID = ?"
val selectionArgs = arrayOf("1")

val deletedRows = db.delete(TABLE_NAME, selection, selectionArgs)

4. Closing the Database

It is important to close the database when you are done with it to free up system resources. You can do this by calling the close() method on the database object.

db.close()

5. Best Practices

When working with SQLite databases in Android, consider the following best practices:

• Use a singleton pattern for your database helper class: This ensures that you only have one instance of the database helper open at any time, which can help prevent memory leaks and other issues.
• Perform database operations on a background thread: Database operations can be time-consuming, so it’s best to perform them on a background thread to avoid blocking the UI thread.
• Use transactions for bulk operations: If you need to perform a large number of database operations, consider using a transaction to wrap these operations. This can improve performance and ensure data integrity.

In summary, SQLite is a powerful tool for managing local data in Android applications. By following the steps outlined in this section, you can set up and use SQLite databases in your Kotlin-based Android projects effectively. Remember to adhere to best practices to ensure your app remains responsive and efficient.

Next page of the Free Ebook:

31Working with SQLite Databases: Creating and Managing SQLite Databases

7 minutes