When developing Android applications, one of the most common requirements is to store and manage data efficiently. SQLite databases provide a lightweight, disk-based database that doesn’t require a separate server process and allows access to the database using a non-proprietary SQL query language. In this section, we'll delve into how you can work with SQLite databases in Kotlin for Android app development, covering the essentials from database creation to data manipulation.
SQLite is embedded within Android, and you can interact with it using the SQLiteOpenHelper class. This helper class provides a way to manage database creation and version management. Let's start by understanding how to set up and use this class.
Setting Up SQLiteOpenHelper
The SQLiteOpenHelper class is abstract, so you'll need to create a subclass to implement its methods. The two primary methods you must override are onCreate() and onUpgrade().
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 AUTOINCREMENT, " +
"$COLUMN_NAME TEXT, " +
"$COLUMN_AGE INTEGER)"
db.execSQL(createTableSQL)
}
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_VERSION = 1
private const val DATABASE_NAME = "MyDatabase.db"
private const val TABLE_NAME = "Users"
private const val COLUMN_ID = "id"
private const val COLUMN_NAME = "name"
private const val COLUMN_AGE = "age"
}
}
In this example, we define a simple database with a single table named Users. The table has three columns: id, name, and age. The onCreate() method is called when the database is created for the first time, and onUpgrade() is used to handle database version upgrades.
Opening and Closing the Database
Once you have your helper class set up, you can open and close the database using the getWritableDatabase() or getReadableDatabase() methods. These methods return an instance of SQLiteDatabase, which you can use to perform database operations.
val dbHelper = MyDatabaseHelper(context)
val db = dbHelper.writableDatabase
// Perform database operations...
db.close()
It's important to close the database after you're done with it to release resources and avoid memory leaks.
Inserting Data
To insert data into the database, you can use the insert() method provided by SQLiteDatabase. This method takes the table name, a null column hack, and a ContentValues object that contains the values to insert.
val values = ContentValues().apply {
put(COLUMN_NAME, "John Doe")
put(COLUMN_AGE, 30)
}
val newRowId = db.insert(TABLE_NAME, null, values)
The insert() method returns the row ID of the newly inserted row, or -1 if an error occurred.
Querying Data
Querying data from an SQLite database involves using the query() method. This method allows you to specify the table, columns, selection criteria, and more. Here's a simple example:
val projection = arrayOf(COLUMN_ID, COLUMN_NAME, COLUMN_AGE)
val selection = "$COLUMN_AGE > ?"
val selectionArgs = arrayOf("25")
val cursor = db.query(
TABLE_NAME,
projection,
selection,
selectionArgs,
null,
null,
null
)
with(cursor) {
while (moveToNext()) {
val itemId = getLong(getColumnIndexOrThrow(COLUMN_ID))
val itemName = getString(getColumnIndexOrThrow(COLUMN_NAME))
val itemAge = getInt(getColumnIndexOrThrow(COLUMN_AGE))
// Use the data...
}
}
cursor.close()
The query() method returns a Cursor object, which is used to iterate over the result set. Remember to close the cursor after use to free up resources.
Updating Data
To update data in the database, use the update() method. This method requires the table name, a ContentValues object with the new data, and a selection criteria to specify which rows to update.
val values = ContentValues().apply {
put(COLUMN_NAME, "Jane Doe")
}
val selection = "$COLUMN_NAME LIKE ?"
val selectionArgs = arrayOf("John Doe")
val count = db.update(
TABLE_NAME,
values,
selection,
selectionArgs
)
The update() method returns the number of rows affected by the update operation.
Deleting Data
Deleting data is performed using the delete() method, which requires the table name and a selection criteria.
val selection = "$COLUMN_AGE < ?"
val selectionArgs = arrayOf("20")
val deletedRows = db.delete(TABLE_NAME, selection, selectionArgs)
This code deletes all rows where the age is less than 20. The delete() method returns the number of rows deleted.
Managing Transactions
SQLite supports transactions, allowing you to execute a series of database operations atomically. Use the beginTransaction(), setTransactionSuccessful(), and endTransaction() methods to manage transactions.
db.beginTransaction()
try {
// Perform multiple database operations
db.setTransactionSuccessful() // Mark the transaction as successful
} finally {
db.endTransaction() // End the transaction
}
If setTransactionSuccessful() is not called, the transaction will be rolled back when endTransaction() is called.
Using Raw Queries
While the helper methods like insert(), query(), update(), and delete() are convenient, you can also execute raw SQL queries using the execSQL() method for non-query operations or rawQuery() for query operations.
// Example of execSQL
db.execSQL("INSERT INTO $TABLE_NAME ($COLUMN_NAME, $COLUMN_AGE) VALUES ('Alice', 25)")
// Example of rawQuery
val rawCursor = db.rawQuery("SELECT * FROM $TABLE_NAME WHERE $COLUMN_AGE > 25", null)
Note that execSQL() does not return any data, while rawQuery() returns a Cursor object.
Closing Thoughts
Working with SQLite databases in Android using Kotlin is straightforward once you understand the basic operations. By mastering these techniques, you can efficiently manage data storage and retrieval in your applications. Remember to handle database operations on a background thread to avoid blocking the UI and ensure a responsive user experience.
SQLite's simplicity and integration with Android make it an excellent choice for local data storage in mobile applications. As you develop more complex applications, you may explore additional features like indexing, triggers, and using SQLite with other Android components to build robust and scalable applications.
