The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Data Persistence with SQLite

Next ►
►

In this module, we will explore how to use SQLite for data persistence in Android applications. SQLite is a lightweight, disk-based database that doesn’t require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. It is a popular choice for local storage in mobile applications.

Key Concepts

  1. SQLite Database: A self-contained, serverless, zero-configuration, transactional SQL database engine.
  2. SQLiteOpenHelper: A helper class to manage database creation and version management.
  3. CRUD Operations: Create, Read, Update, and Delete operations on the database.
  4. ContentValues: A key/value store that Android uses to insert and update database rows.

Setting Up SQLite in Android

Step 1: Create a Database Helper Class

The SQLiteOpenHelper class provides the necessary methods to manage the database. Here’s how to create a helper class:

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

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "example.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }
}

Step 2: Open and Close the Database

You need to open the database to perform operations and close it when done:

DatabaseHelper dbHelper = new DatabaseHelper(context);
SQLiteDatabase db = dbHelper.getWritableDatabase();
// Perform database operations
db.close();

Step 3: Perform CRUD Operations

Insert Data

ContentValues values = new ContentValues();
values.put("name", "John Doe");
values.put("age", 30);

long newRowId = db.insert("users", null, values);

Read Data

String[] projection = {
    "id",
    "name",
    "age"
};

Cursor cursor = db.query(
    "users",   // The table to query
    projection,            // The array of columns to return (pass null to get all)
    null,                  // The columns for the WHERE clause
    null,                  // The values for the WHERE clause
    null,                  // Don't group the rows
    null,                  // Don't filter by row groups
    null                   // The sort order
);

while(cursor.moveToNext()) {
    long itemId = cursor.getLong(cursor.getColumnIndexOrThrow("id"));
    String itemName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
    int itemAge = cursor.getInt(cursor.getColumnIndexOrThrow("age"));
}
cursor.close();

Update Data

ContentValues values = new ContentValues();
values.put("name", "Jane Doe");

String selection = "id = ?";
String[] selectionArgs = { "1" };

int count = db.update(
    "users",
    values,
    selection,
    selectionArgs);

Delete Data

String selection = "id = ?";
String[] selectionArgs = { "1" };

int deletedRows = db.delete("users", selection, selectionArgs);

Practical Exercise

Task

  1. Create a new Android project.
  2. Implement a DatabaseHelper class to manage a database with a table named products with columns id, name, and price.
  3. Add methods to insert, read, update, and delete products.
  4. Create a simple UI to interact with the database (e.g., add a product, display all products).

Solution

// DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "store.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS products");
        onCreate(db);
    }

    public long insertProduct(String name, double price) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("price", price);
        return db.insert("products", null, values);
    }

    public Cursor getAllProducts() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query("products", null, null, null, null, null, null);
    }

    public int updateProduct(int id, String name, double price) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("price", price);
        String selection = "id = ?";
        String[] selectionArgs = { String.valueOf(id) };
        return db.update("products", values, selection, selectionArgs);
    }

    public int deleteProduct(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String selection = "id = ?";
        String[] selectionArgs = { String.valueOf(id) };
        return db.delete("products", selection, selectionArgs);
    }
}

UI Implementation

Create a simple UI with buttons and text fields to add, display, update, and delete products. Use the methods from DatabaseHelper to perform the operations.

Summary

In this module, we learned how to use SQLite for data persistence in Android applications. We covered the creation of a database helper class, opening and closing the database, and performing CRUD operations. We also provided a practical exercise to reinforce the concepts learned. In the next module, we will explore using Room for database management, which provides an abstraction layer over SQLite to allow for more robust database access while harnessing the full power of SQLite.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved