28.12. Working with SQLite Databases: Working with Cursors

In Android app development, SQLite databases offer a powerful and efficient way to store and retrieve data locally. SQLite is a self-contained, serverless, and zero-configuration database engine, making it an ideal choice for mobile applications. When working with SQLite databases in Kotlin, understanding how to work with cursors is crucial, as they play a significant role in navigating and manipulating the database results.

A Cursor is a data access object that provides random read-write access to the result set returned by a database query. It acts as an iterator over the result set, allowing you to traverse through rows of data. Cursors are essential when you need to handle query results efficiently, especially when dealing with large datasets.

To begin working with SQLite databases and cursors in Kotlin, you first need to establish a connection to the database. This is typically done using a subclass of SQLiteOpenHelper. This helper class provides methods to create, open, and manage the database. Here’s a basic example of setting up a database helper:

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

    override fun onCreate(db: SQLiteDatabase) {
        // Create table SQL execution
        db.execSQL("CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Drop older table if existed, all data will be gone!
        db.execSQL("DROP TABLE IF EXISTS my_table")
        // Create tables again
        onCreate(db)
    }

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

Once the database is set up, you can perform various operations such as inserting, updating, deleting, and querying data. When querying data, the query() method of SQLiteDatabase returns a Cursor object. This object allows you to iterate over the result set.

Here is an example of querying data from the database and using a cursor to process the results:

fun getAllUsers(): List {
    val users = mutableListOf()
    val db = this.readableDatabase
    val cursor = db.query("my_table", arrayOf("id", "name", "age"), null, null, null, null, null)

    if (cursor != null) {
        if (cursor.moveToFirst()) {
            do {
                val id = cursor.getInt(cursor.getColumnIndexOrThrow("id"))
                val name = cursor.getString(cursor.getColumnIndexOrThrow("name"))
                val age = cursor.getInt(cursor.getColumnIndexOrThrow("age"))
                users.add(User(id, name, age))
            } while (cursor.moveToNext())
        }
        cursor.close()
    }
    return users
}

In this example, the query() method is used to select all rows from my_table. The cursor iterates over each row, extracting the values of the id, name, and age columns. These values are then used to create User objects, which are added to a list.

It is important to note that cursors must be closed after use to free up resources. Failure to close a cursor can lead to memory leaks and performance issues in your application.

In addition to the basic operations, cursors provide several methods to navigate through the data, such as:

• moveToFirst(): Moves the cursor to the first row.
• moveToLast(): Moves the cursor to the last row.
• moveToNext(): Moves the cursor to the next row.
• moveToPrevious(): Moves the cursor to the previous row.
• moveToPosition(int position): Moves the cursor to the specified row index.
• isAfterLast(): Checks if the cursor is after the last row.
• isBeforeFirst(): Checks if the cursor is before the first row.

These methods allow you to control the cursor's position within the result set, providing flexibility in how you access and process data.

Another powerful feature of cursors is the ability to handle data types. SQLite supports several data types, including NULL, INTEGER, REAL, TEXT, and BLOB. The cursor provides methods to retrieve values of these types, such as:

• getInt(int columnIndex): Retrieves an integer value from the specified column index.
• getString(int columnIndex): Retrieves a string value from the specified column index.
• getFloat(int columnIndex): Retrieves a floating-point value from the specified column index.
• getBlob(int columnIndex): Retrieves a blob value from the specified column index.
• getDouble(int columnIndex): Retrieves a double value from the specified column index.

When working with cursors, it is important to handle exceptions and errors gracefully. Common issues include attempting to access a column index that does not exist or failing to close the cursor after use. Implementing proper error handling and resource management ensures that your application remains robust and efficient.

Furthermore, in some cases, you may want to use Loader classes, such as CursorLoader, to manage cursors more efficiently, especially when dealing with background data loading. Loaders help in managing the lifecycle of cursors, preventing memory leaks, and ensuring smooth data loading operations.

In conclusion, working with cursors is a fundamental aspect of handling SQLite databases in Android app development. Understanding how to effectively navigate, retrieve, and manage data using cursors is essential for building robust and efficient applications. By leveraging the capabilities of cursors, you can optimize data access and manipulation, ensuring that your app performs smoothly even when handling large datasets.

Next page of the Free Ebook:

41Working with SQLite Databases: Data Security in SQLite

6 minutes