28.7. Working with SQLite Databases: Executing SQL Queries

In the realm of Android app development, managing data efficiently is crucial for creating responsive and robust applications. One of the most common data storage solutions in Android is SQLite, a lightweight database engine that is built into the Android operating system. It provides a powerful means to store, retrieve, and manipulate structured data directly on the device. In this section, we will delve into the intricacies of executing SQL queries using SQLite databases in the context of Kotlin for Android app development.

SQLite databases are particularly well-suited for mobile applications due to their simplicity and low overhead. They allow developers to perform complex queries and transactions without the need for an external server, making them ideal for offline applications or those that require local data storage.

Setting Up SQLite in Your Android Project

Before executing SQL queries, you need to set up an SQLite database in your Android project. Typically, this involves creating a subclass of SQLiteOpenHelper, which provides the necessary methods to create, update, and manage your database schema. Here is a basic example:

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

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

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

    companion object {
        private const val DATABASE_NAME = "mydatabase.db"
        private const val DATABASE_VERSION = 1
        private const val TABLE_NAME = "mytable"
        private const val COLUMN_ID = "id"
        private const val COLUMN_NAME = "name"
    }
}

In this example, we define a database with a single table that has two columns: id and name. The onCreate method is used to create the table, while onUpgrade handles schema changes.

Executing SQL Queries

Once the database is set up, you can execute SQL queries to interact with the data. SQLite provides a range of SQL commands for data manipulation, including INSERT, SELECT, UPDATE, and DELETE. Let's explore each of these operations in detail.

Inserting Data

To insert data into an SQLite database, you use the insert method provided by the SQLiteDatabase class. This method takes a table name and a ContentValues object, which maps column names to values. Here's how you can insert a new row into the database:

fun insertData(name: String) {
    val db = writableDatabase
    val values = ContentValues().apply {
        put(COLUMN_NAME, name)
    }
    db.insert(TABLE_NAME, null, values)
    db.close()
}

In this method, we first obtain a writable instance of the database. We then create a ContentValues object to specify the data to be inserted, and finally, we call insert to add the data to the table.

Selecting Data

Retrieving data from an SQLite database involves executing a SELECT query. You can use the query method of SQLiteDatabase, which provides a flexible way to specify the columns, selection criteria, and sort order. Here's an example:

fun getAllData(): List<String> {
    val db = readableDatabase
    val cursor = db.query(
        TABLE_NAME,
        arrayOf(COLUMN_NAME),
        null,
        null,
        null,
        null,
        null
    )

    val items = mutableListOf<String>()
    with(cursor) {
        while (moveToNext()) {
            val itemName = getString(getColumnIndexOrThrow(COLUMN_NAME))
            items.add(itemName)
        }
    }
    cursor.close()
    db.close()
    return items
}

This method retrieves all names from the table and returns them as a list of strings. The query method returns a Cursor object, which you can iterate over to access the results of the query. It's important to close the cursor and the database once you're done to free up resources.

Updating Data

To update existing data in an SQLite database, you use the update method. This method requires specifying the table name, the new values, and a selection criteria to identify which rows to update. Here's an example:

fun updateData(id: Int, newName: String) {
    val db = writableDatabase
    val values = ContentValues().apply {
        put(COLUMN_NAME, newName)
    }
    val selection = "$COLUMN_ID = ?"
    val selectionArgs = arrayOf(id.toString())
    db.update(TABLE_NAME, values, selection, selectionArgs)
    db.close()
}

In this example, we update the name of a row with a specific ID. The selection and selectionArgs parameters are used to specify which rows to update, using a SQL WHERE clause.

Deleting Data

Deleting data from an SQLite database involves using the delete method. Similar to the update operation, you must specify a selection criteria to identify which rows to delete. Here's how you can delete a row by its ID:

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

This method deletes a row from the table based on its ID. Again, the selection and selectionArgs are used to form a WHERE clause.

Handling Transactions

For operations that involve multiple steps, such as inserting multiple rows or performing a series of updates, it's often beneficial to use transactions. Transactions ensure that all operations are completed successfully before committing the changes, providing a mechanism to roll back changes in case of an error. Here's an example of using transactions in SQLite:

fun performTransaction() {
    val db = writableDatabase
    db.beginTransaction()
    try {
        // Perform multiple database operations
        insertData("Item 1")
        insertData("Item 2")
        db.setTransactionSuccessful()
    } catch (e: Exception) {
        // Handle exception
    } finally {
        db.endTransaction()
    }
    db.close()
}

In this example, we wrap multiple insert operations in a transaction. If all operations succeed, we mark the transaction as successful using setTransactionSuccessful. If an exception occurs, the transaction is automatically rolled back when endTransaction is called.

Best Practices for SQLite in Android

When working with SQLite databases in Android, there are several best practices to follow to ensure optimal performance and reliability:

• Use Prepared Statements: To prevent SQL injection attacks and improve performance, use prepared statements with the SQLiteStatement class for executing SQL commands.
• Close Cursors and Databases: Always close Cursor objects and database instances when they are no longer needed to free up resources.
• Optimize Queries: Use indexing and efficient query structures to improve the performance of data retrieval operations.
• Handle Concurrency: Consider using content providers or other mechanisms to handle concurrent database access in multi-threaded applications.
• Backup and Restore: Implement mechanisms to backup and restore database data, especially for applications that store critical information.

By adhering to these best practices, you can ensure that your application makes the most of SQLite's capabilities while maintaining high performance and data integrity.

In conclusion, SQLite databases are a powerful tool for managing local data in Android applications. By understanding how to execute SQL queries and manage database operations effectively, you can create applications that are both functional and efficient. Whether you're building a simple note-taking app or a complex data-driven application, mastering SQLite in Kotlin will enhance your Android development skills significantly.

Next page of the Free Ebook:

36Working with SQLite Databases: Handling Transactions in SQLite

7 minutes