28.1. Working with SQLite Databases: Introduction to SQLite for Android

In the realm of Android app development, data storage plays a pivotal role in creating efficient and user-friendly applications. Among the various data storage solutions available, SQLite stands out as a popular choice due to its lightweight nature, ease of use, and integration capabilities with Android. This section delves into the fundamentals of working with SQLite databases in Android, providing a comprehensive introduction to help you harness its potential in your app development endeavors.

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Unlike other database management systems, SQLite is serverless, meaning it does not require a separate server process to operate, making it an ideal choice for embedded database solutions, including Android applications.

Why Use SQLite in Android?

There are several compelling reasons to choose SQLite for Android app development:

• Embedded Database: SQLite is embedded within the Android operating system, eliminating the need for separate installation and configuration processes. This integration ensures seamless performance and compatibility with Android devices.
• Lightweight and Efficient: With a minimal footprint, SQLite is designed to be efficient, requiring minimal resources to operate. This makes it particularly suitable for mobile devices with limited storage and processing capabilities.
• ACID Compliance: SQLite adheres to the principles of Atomicity, Consistency, Isolation, and Durability (ACID), ensuring reliable transactions and data integrity even in the event of system crashes or power failures.
• SQL Support: SQLite supports a rich subset of SQL, allowing developers to leverage familiar SQL syntax for querying and managing data.
• Open Source: SQLite is open-source software, providing developers with the flexibility to modify and adapt the code to suit their specific needs.

Setting Up SQLite in Android

To begin working with SQLite in Android, you need to set up a database and define a schema. The schema outlines the structure of your database, including tables, columns, and data types. Here’s a step-by-step guide to getting started:

1. Creating a Database Helper Class

In Android, it is common practice to create a helper class that extends SQLiteOpenHelper. This class manages database creation and version management. The SQLiteOpenHelper class provides two key methods that you need to override:

• onCreate(SQLiteDatabase db): This method is called when the database is created for the first time. Here, you define the SQL statements needed to create the required tables and initial data.
• onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion): This method is invoked when the database needs to be upgraded. It handles changes in the database schema, such as adding or modifying tables.

Here’s an example of a simple database helper class:

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 {
        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"
    }
}

2. Accessing the Database

Once you have your helper class set up, you can access the database by creating an instance of your helper class and calling getWritableDatabase() or getReadableDatabase() methods. These methods provide access to the database for writing and reading operations, respectively.

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

Performing CRUD Operations

CRUD (Create, Read, Update, Delete) operations form the backbone of database interactions. With SQLite, you can perform these operations using SQL statements or by utilizing Android’s ContentValues and Cursor classes.

1. Inserting Data

To insert data into a table, you can use the insert() method provided by the SQLiteDatabase class. This method requires a table name and a ContentValues object containing the data to be inserted.

val values = ContentValues().apply {
    put(COLUMN_NAME, "Sample Name")
}
val newRowId = db.insert(TABLE_NAME, null, values)

2. Querying Data

To retrieve data from a table, you can use the query() method or execute raw SQL queries. The query() method allows you to specify columns, selection criteria, and 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()

3. Updating Data

To update existing data, use the update() method, which requires a ContentValues object with updated data, a selection criteria, and selection arguments.

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
)

4. Deleting Data

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

val selection = "$COLUMN_ID = ?"
val selectionArgs = arrayOf("1")
val deletedRows = db.delete(TABLE_NAME, selection, selectionArgs)

Best Practices for SQLite in Android

When working with SQLite in Android, adhering to best practices can enhance performance and ensure data integrity:

• Use Transactions: Enclose multiple database operations within a transaction to ensure atomicity and improve performance.
• Close Cursors: Always close Cursor objects after use to release resources and prevent memory leaks.
• Optimize Queries: Use indexes and optimize SQL queries to enhance query performance, especially for large datasets.
• Handle Database Upgrades: Implement appropriate logic in the onUpgrade() method to manage schema changes and data migrations.
• Consider Room: For more complex database requirements, consider using Room, a higher-level abstraction over SQLite that provides compile-time checks and easier database management.

In conclusion, SQLite offers a robust and efficient solution for data storage in Android applications. By understanding its core concepts and leveraging its capabilities, you can build powerful apps that efficiently manage and persist data, enhancing user experience and app functionality.

Next page of the Free Ebook:

30Working with SQLite Databases: Setting up SQLite in Android Projects

8 minutes