Build a local database with SQLite

Save notes on-device with parameterized queries and a small SQLiteOpenHelper.

1. Define a small schema

Use a notes table with an integer primary key and a non-null text title. SQLiteOpenHelper manages opening and versioning the database. For larger applications, Room adds compile-time query checking and is often a better abstraction.

2. Create the helper

The source example creates the table in onCreate. This runs when the database is created, not every time the app launches. Increasing the database version triggers onUpgrade; implement a migration that preserves existing data before changing that version.

3. Insert with bound values

ContentValues passes values separately from SQL syntax. Do not concatenate user-entered text into SQL. insertOrThrow reports insertion errors instead of returning a silent failure.

4. Keep I/O off the UI thread

Database opening and queries may block. Call the helper from an ExecutorService, then post UI updates back to the main thread. Keep the helper at application scope and close it only when no operation can still use it.

ExecutorService executor = Executors.newSingleThreadExecutor();
executor.execute(() -> {
    try {
        long id = database.add("Learn SQLite");
        runOnUiThread(() -> label.setText("Saved note " + id));
    } catch (SQLiteException error) {
        runOnUiThread(() -> label.setText("Could not save note"));
    }
});

Import the concurrency and Android SQLite types in your own activity. Shut down an owned executor when its owner is destroyed. A lifecycle-aware repository is more appropriate for a larger app.

Expected output

Each successful insert produces a new row ID. The count query returns the number of saved rows. Data survives process restarts unless the app's data is cleared.

Troubleshooting

• No such table: ensure the helper created this database file; use a migration for existing installations.
• Database locked: keep transactions short and avoid competing long-running writes.
• Cursor leaks: close every Cursor; try-with-resources is used in the example.
• Lost notes after upgrade: never drop production tables as a shortcut for migration.

package com.example.notes;
import android.content.Context;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class NotesDb extends SQLiteOpenHelper {
    public NotesDb(Context context) { super(context, "notes.db", null, 1); }
    @Override public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)");
    }
    @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Add non-destructive migrations when the schema changes.
    }
    public long add(String title) {
        ContentValues values = new ContentValues();
        values.put("title", title);
        return getWritableDatabase().insertOrThrow("notes", null, values);
    }
    public int count() {
        try (Cursor cursor = getReadableDatabase().rawQuery("SELECT COUNT(*) FROM notes", null)) {
            return cursor.moveToFirst() ? cursor.getInt(0) : 0;
        }
    }
}

Keep connecting the dots