28.3. Working with SQLite Databases: Creating and Managing SQLite Databases

When developing Android applications, managing data efficiently is crucial, and SQLite databases offer a robust solution for local data storage. SQLite is a lightweight, embedded database engine that provides a relational database management system, which is perfect for mobile applications due to its simplicity and efficiency. This section delves into the essentials of creating and managing SQLite databases in Kotlin for Android app development.

Understanding SQLite in Android

SQLite is embedded within the Android operating system, which means there's no need for separate database installation or configuration. It supports standard relational database features such as tables, indexes, and transactions. SQLite is particularly well-suited for applications that require a small footprint, making it ideal for mobile environments where resources are limited.

Key Features of SQLite

• Zero Configuration: SQLite doesn’t require a separate server process or system configuration, making it incredibly easy to set up.
• Self-contained: It is a complete database system that operates as a single library, which can be easily integrated into applications.
• Transactional: SQLite transactions are fully ACID-compliant, ensuring reliable data storage and retrieval.
• Cross-platform: The same database file can be accessed across different platforms, enhancing portability.

Creating an SQLite Database

To create an SQLite database in an Android application using Kotlin, you typically extend the SQLiteOpenHelper class. This class provides the necessary methods to manage database creation and version management. The two primary methods you must override are onCreate() and onUpgrade().

Step-by-Step Guide to Creating a Database

1. Define a Database Helper Class:

   Create a class that extends SQLiteOpenHelper. This class will manage database creation and version management.

   
   class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
   
       override fun onCreate(db: SQLiteDatabase?) {
           db?.execSQL(SQL_CREATE_ENTRIES)
       }
   
       override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
           db?.execSQL(SQL_DELETE_ENTRIES)
           onCreate(db)
       }
   
       companion object {
           const val DATABASE_VERSION = 1
           const val DATABASE_NAME = "SampleDatabase.db"
           private const val SQL_CREATE_ENTRIES = "CREATE TABLE SampleTable (ID INTEGER PRIMARY KEY, Name TEXT)"
           private const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS SampleTable"
       }
   }
           

2. Initialize the Database:

   In your activity or fragment, create an instance of your DatabaseHelper to access the database.

   
   val dbHelper = DatabaseHelper(this)
           

Managing SQLite Databases

Once the database is created, you can perform various operations such as inserting, updating, deleting, and querying data. SQLite provides a rich set of APIs to perform these operations efficiently.

Inserting Data

To insert data into an SQLite database, you can use the insert() method provided by the SQLiteDatabase class. This method requires a table name and a ContentValues object that holds the data.

val db = dbHelper.writableDatabase

val values = ContentValues().apply {
    put("Name", "John Doe")
}

val newRowId = db.insert("SampleTable", null, values)

Querying Data

To retrieve data from the database, you can use the query() method or raw SQL queries. The query() method is safer and prevents SQL injection.

val db = dbHelper.readableDatabase

val projection = arrayOf("ID", "Name")

val cursor = db.query(
    "SampleTable",
    projection,
    null,
    null,
    null,
    null,
    null
)

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

Updating Data

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

val db = dbHelper.writableDatabase

val values = ContentValues().apply {
    put("Name", "Jane Doe")
}

val selection = "ID = ?"
val selectionArgs = arrayOf("1")

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

Deleting Data

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

val db = dbHelper.writableDatabase

val selection = "ID = ?"
val selectionArgs = arrayOf("1")

val deletedRows = db.delete("SampleTable", selection, selectionArgs)

Best Practices for SQLite Database Management

• Use Transactions: Wrap multiple database operations in a transaction to ensure atomicity and improve performance.
• Close Cursors: Always close Cursor objects after use to free up resources.
• Use Projections: Specify the columns you need in your queries to reduce memory usage.
• Handle Version Changes: Implement the onUpgrade() method to handle changes in database schema across different versions.

By following these guidelines and utilizing the powerful features of SQLite, you can efficiently manage local data storage in your Android applications. SQLite's integration with Android, combined with Kotlin's expressive syntax, provides a seamless and efficient way to handle databases, ensuring your applications remain performant and reliable.

Next page of the Free Ebook:

32Working with SQLite Databases: SQLiteOpenHelper Class

9 minutes