SQLiteOpenHelper (Java)

« Prev Next »

Before Room existed, SQLiteOpenHelper was — and still is — Android's built-in way to manage a local SQLite database directly. There is no ORM, no annotations, no generated code: you write the SQL yourself and talk to the database through a small set of Java APIs. Understanding this classic approach makes it much easier to appreciate what Room automates for you in the next lesson.

Subclassing SQLiteOpenHelper

A SQLiteOpenHelper subclass has one job: create the database schema the first time the app runs, and migrate it safely if the schema ever changes. You override two methods: onCreate(), which runs your CREATE TABLE SQL, and onUpgrade(), which runs whenever the database version number increases.

// RecipeDbHelper.java
public class RecipeDbHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "recipebox.db";
    private static final int DB_VERSION = 1;

    public static final String TABLE_RECIPES = "recipes";
    public static final String COL_ID = "id";
    public static final String COL_TITLE = "title";
    public static final String COL_INGREDIENTS = "ingredients";
    public static final String COL_STEPS = "steps";
    public static final String COL_PHOTO_URI = "photo_uri";
    public static final String COL_CATEGORY = "category";
    public static final String COL_PREP_MINUTES = "prep_minutes";

    public RecipeDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_RECIPES + " ("
            + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COL_TITLE + " TEXT NOT NULL, "
            + COL_INGREDIENTS + " TEXT, "
            + COL_STEPS + " TEXT, "
            + COL_PHOTO_URI + " TEXT, "
            + COL_CATEGORY + " TEXT, "
            + COL_PREP_MINUTES + " INTEGER)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Simplest strategy: drop and recreate (loses data — real apps use ALTER TABLE)
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECIPES);
        onCreate(db);
    }
}

Explanation:

onCreate() only runs once, the very first time the database file is created on the device.
onUpgrade() runs when you bump DB_VERSION, letting you migrate old data to a new schema.
This example drops the table for simplicity — a production app would use ALTER TABLE statements to preserve existing rows.

Inserting a Row with ContentValues

Rather than hand-writing an INSERT SQL string (and risking SQL injection or typos), you package the row's values into a ContentValues object and hand it to db.insert().

// Inserting a new recipe
public long insertRecipe(Recipe recipe) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(RecipeDbHelper.COL_TITLE, recipe.getTitle());
    values.put(RecipeDbHelper.COL_INGREDIENTS, recipe.getIngredients());
    values.put(RecipeDbHelper.COL_STEPS, recipe.getSteps());
    values.put(RecipeDbHelper.COL_PHOTO_URI, recipe.getPhotoUri());
    values.put(RecipeDbHelper.COL_CATEGORY, recipe.getCategory());
    values.put(RecipeDbHelper.COL_PREP_MINUTES, recipe.getPrepMinutes());

    long newRowId = db.insert(RecipeDbHelper.TABLE_RECIPES, null, values);
    return newRowId;
}

Explanation:

getWritableDatabase() opens the database for both reading and writing, creating it if needed.
db.insert() returns the new row's id, or -1 if the insert failed.
Using ContentValues avoids manually escaping strings inside SQL text.

Querying with Cursor

Reading data back is where raw SQLite starts to feel tedious. A query doesn't return Java objects — it returns a Cursor, a pointer into the result set that you must manually step through row by row, reading each column by its numeric index.

// Fetching all recipes as a List<Recipe>
public List<Recipe> getAllRecipes() {
    List<Recipe> recipes = new ArrayList<>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();

    Cursor cursor = db.query(
        RecipeDbHelper.TABLE_RECIPES, null, null, null, null, null,
        RecipeDbHelper.COL_TITLE + " ASC"
    );

    if (cursor.moveToFirst()) {
        int idIdx = cursor.getColumnIndex(RecipeDbHelper.COL_ID);
        int titleIdx = cursor.getColumnIndex(RecipeDbHelper.COL_TITLE);
        int ingredientsIdx = cursor.getColumnIndex(RecipeDbHelper.COL_INGREDIENTS);
        int stepsIdx = cursor.getColumnIndex(RecipeDbHelper.COL_STEPS);
        int photoIdx = cursor.getColumnIndex(RecipeDbHelper.COL_PHOTO_URI);
        int categoryIdx = cursor.getColumnIndex(RecipeDbHelper.COL_CATEGORY);
        int prepIdx = cursor.getColumnIndex(RecipeDbHelper.COL_PREP_MINUTES);

        do {
            Recipe recipe = new Recipe();
            recipe.setId(cursor.getInt(idIdx));
            recipe.setTitle(cursor.getString(titleIdx));
            recipe.setIngredients(cursor.getString(ingredientsIdx));
            recipe.setSteps(cursor.getString(stepsIdx));
            recipe.setPhotoUri(cursor.getString(photoIdx));
            recipe.setCategory(cursor.getString(categoryIdx));
            recipe.setPrepMinutes(cursor.getInt(prepIdx));
            recipes.add(recipe);
        } while (cursor.moveToNext());
    }
    cursor.close();
    return recipes;
}

Notice how tedious this is: seven separate getColumnIndex() calls, a manual do/while loop with moveToNext(), and a Java object rebuilt by hand from raw columns. Compare this to a single annotated method in Room — this is exactly the boilerplate an ORM removes.

You can also skip the query builder and write raw SQL with rawQuery() when a query is more complex than db.query() conveniently expresses:

Cursor cursor = db.rawQuery(
    "SELECT * FROM recipes WHERE category = ? ORDER BY prep_minutes ASC",
    new String[]{ "Dessert" }
);

Updating and Deleting Rows

// Update a recipe's title
public int updateRecipeTitle(int recipeId, String newTitle) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(RecipeDbHelper.COL_TITLE, newTitle);

    return db.update(
        RecipeDbHelper.TABLE_RECIPES, values,
        RecipeDbHelper.COL_ID + " = ?",
        new String[]{ String.valueOf(recipeId) }
    );
}

// Delete a recipe
public int deleteRecipe(int recipeId) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    return db.delete(
        RecipeDbHelper.TABLE_RECIPES,
        RecipeDbHelper.COL_ID + " = ?",
        new String[]{ String.valueOf(recipeId) }
    );
}

Explanation:

Both update() and delete() take a "where" clause with ? placeholders, plus a matching array of arguments — never concatenate user input directly into the SQL string.
Both methods return the number of rows affected, useful for confirming the operation actually matched something.

Common Mistakes with Raw SQLiteOpenHelper

Be honest with yourself: this approach is verbose and easy to get subtly wrong.

Forgetting to call cursor.close() leaks memory and can eventually crash the app.
An off-by-one or mistyped column index (or name) silently reads the wrong field — the compiler cannot catch this, since SQL is just a string.
Every query result has to be manually converted back into your Java model — no automatic mapping.
Nothing stops you from writing SQL that references a column you renamed or removed months ago.

None of this is wrong — it's exactly how Android databases worked for years, and it's still valid Java. But it's exactly the pain that Room Database (Java) was built to eliminate: the next lesson shows how Room sits on top of this same SQLite engine while removing almost all of this manual bookkeeping.

« Prev Next »