Sqlite Tutorial

SQLite is a lightweight, open-source relational database that is embedded within Android. This means you can use it to store, manipulate, and retrieve persistent data without needing a separate database server or complex setup. Understanding how to integrate SQLite into your Android applications is crucial for effective data management, enabling your apps to handle user data efficiently.

In this tutorial, we will explore how to use SQLite in Kotlin for Android development, covering essential concepts, syntax, and practical examples for implementing CRUD (Create, Read, Update, Delete) operations.

Why Use SQLite in Android?

• ✅ Lightweight: SQLite is a small library that supports a full-featured SQL implementation.
• ✅ Embedded: No external server is needed; it runs in the same process as your application.
• ✅ Reliable: It can handle a significant amount of data and is ACID compliant, ensuring data integrity.
• ✅ Easy to Use: With built-in support in Android, setting up SQLite is straightforward.

Concept Explanation

SQLiteOpenHelper Class

The SQLiteOpenHelper class in Android is a helper class that manages database creation and version management. It provides a simple way to create and manage your SQLite database with minimal boilerplate code.

Think of SQLiteOpenHelper as a tool that helps you build the foundation of your data management system. Just like a carpenter uses tools to create a sturdy structure, this class allows developers to manage the database lifecycle without hassle.

Key Methods

• ✅ onCreate: Called when the database is created for the first time. You will define the structure of your tables here.
• ✅ onUpgrade: Called when the database needs to be upgraded, such as when the app version changes.
• ✅ close: Closes the database connection when it is no longer needed.

Syntax Section

Here's a basic example of how to create a class that extends SQLiteOpenHelper:

class MyDatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        // Create table structure
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Handle database upgrade
    }
    
    companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "MyDatabase.db"
    }
}

Explanation of the Syntax

• ✅ MyDatabaseHelper: Your custom database helper class.
• ✅ DATABASENAME and DATABASEVERSION: Constants that define the database name and its version.
• ✅ onCreate: You will implement the SQL commands to create your tables here.
• ✅ onUpgrade: Logic to handle upgrading the database schema.

Working Examples

Let's build a simple application that performs CRUD operations on a user database.

Example 1: Setting Up the Database

This example demonstrates how to create a database and a user table.

package com.example.kotlinsqldemo

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class UserDatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    
    override fun onCreate(db: SQLiteDatabase) {
        val createTableSQL = """
            CREATE TABLE Users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL
            )
        """.trimIndent()
        db.execSQL(createTableSQL)
    }

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

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

Output:

No output, but the database with the Users table is created.

---

Example 2: Inserting a User

This example shows how to insert a user into the database.

fun addUser(name: String, email: String): Long {
    val db = this.writableDatabase
    val contentValues = ContentValues().apply {
        put("name", name)
        put("email", email)
    }
    val result = db.insert("Users", null, contentValues)
    db.close()
    return result
}

Expected Output:

Returns the row ID of the newly inserted user or -1 if an error occurred.

---

Example 3: Retrieving Users

Now let's retrieve and display all users from the database.

fun getAllUsers(): List<User> {
    val userList = mutableListOf<User>()
    val db = this.readableDatabase
    val cursor = db.rawQuery("SELECT * FROM Users", null)

    if (cursor.moveToFirst()) {
        do {
            val user = User(
                id = cursor.getInt(cursor.getColumnIndex("id")),
                name = cursor.getString(cursor.getColumnIndex("name")),
                email = cursor.getString(cursor.getColumnIndex("email"))
            )
            userList.add(user)
        } while (cursor.moveToNext())
    }
    cursor.close()
    db.close()
    return userList
}

Expected Output:

Returns a list of User objects containing user details.

---

Example 4: Updating a User

Here’s how to update a user's information based on their ID.

fun updateUser(id: Int, name: String, email: String): Int {
    val db = this.writableDatabase
    val contentValues = ContentValues().apply {
        put("name", name)
        put("email", email)
    }
    val result = db.update("Users", contentValues, "id=?", arrayOf(id.toString()))
    db.close()
    return result
}

Expected Output:

Returns the number of rows affected (1 if successful, 0 if no rows matched).

---

Example 5: Deleting a User

Finally, let’s implement a method to delete a user.

fun deleteUser(id: Int): Int {
    val db = this.writableDatabase
    val result = db.delete("Users", "id=?", arrayOf(id.toString()))
    db.close()
    return result
}

Expected Output:

Returns the number of rows deleted.

---

Common Mistakes

1. Forgetting to Close the Database

Always close your database connection. Failing to do so can lead to memory leaks and unexpected behavior.

Incorrect:

val db = this.writableDatabase
// some operations
// forgot to close

Correct:

val db = this.writableDatabase
// some operations
db.close() // Always close after operations

2. Not Handling Database Exceptions

Wrap your database operations in try-catch blocks to gracefully handle exceptions.

Example:

try {
    // Database operations
} catch (e: SQLiteException) {
    // Handle exception
}

Best Practices

• ✅ Use Transactions: For bulk insertions or updates, use transactions to improve performance.
• ✅ Data Validation: Always validate user input before inserting into the database to avoid SQL injection.
• ✅ Backup Data: Regularly backup your database to prevent data loss.

Practice Exercises

1. Create a new table: Implement a method to create a Products table with fields like id, name, and price.
2. Implement Search: Write a function to search for a user by their email.
3. Add UI: Build a simple Android user interface that allows users to enter data, view records, and update/delete users.

By following this guide, you now have a solid understanding of how to work with SQLite in Kotlin for Android applications. Start experimenting with your own projects to reinforce these concepts!