Querying SQLite Database with rawQuery and Cursor in Android

SQLite is built into Android, making it a practical choice for storing structured data directly on a device. A local database can hold shopping lists, saved articles, bookings, inventory records, or user preferences without requiring a constant internet connection.

Two Android classes are central to reading SQLite data: SQLiteDatabase runs the SQL statement, while Cursor provides access to each returned row. The rawQuery() method is useful when a normal query() call cannot express the joins, sorting, calculated fields, or filtering that your application needs.

For an Australian application, local storage can support people travelling through areas with unreliable coverage, commuting on Sydney trains, Melbourne trams, or Brisbane buses, and using an app in shops where connectivity may be interrupted. A well-designed database also keeps screen loading quick because records are read from the device rather than fetched repeatedly from a server.

Security and privacy still matter for offline data. If an app stores names, addresses, orders, or location history for Australian users, its design should take the Privacy Act 1988 and the Australian Privacy Principles into account. Store only what the feature requires, protect sensitive information, and avoid treating a local SQLite file as automatically secure.

Prepare The SQLite Database

A common starting point is an SQLiteOpenHelper subclass. It creates the database and defines the table schema in one place. The following example stores products with an Australian dollar price and a stock count:

public class ShopDbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "shop.db";
    private static final int DATABASE_VERSION = 1;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
            "CREATE TABLE products (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "name TEXT NOT NULL, " +
            "category TEXT NOT NULL, " +
            "price_cents INTEGER NOT NULL, " +
            "stock INTEGER NOT NULL)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Apply migrations here when the schema changes.
    }
}

Storing money as integer cents avoids floating-point rounding problems. For example, $12.50 can be stored as 1250. This is useful for local retail or marketplace apps that display prices in Australian dollars, calculate totals, and need consistent values for GST-related records.

Run A Parameterised Raw Query

rawQuery() accepts an SQL string and an optional array of selection arguments. Each question mark is replaced by a bound value in order. This protects the query from SQL injection and correctly handles values containing apostrophes or other special characters.

SQLiteDatabase db = new ShopDbHelper(context).getReadableDatabase();

String sql =
    "SELECT id, name, price_cents, stock " +
    "FROM products " +
    "WHERE category = ? AND stock > ? " +
    "ORDER BY name COLLATE NOCASE ASC";

String[] args = {"Groceries", "0"};
Cursor cursor = db.rawQuery(sql, args);

Do not build SQL by joining user input into the statement:

// Unsafe
String sql = "SELECT * FROM products WHERE name = '" + enteredName + "'";

The parameterised version is safer and easier to maintain. It also works well when a search field accepts names typed with apostrophes, such as a product or supplier name.

Read Rows With A Cursor

A Cursor points to the result set, initially positioned before the first row. Call moveToFirst() for a single-record lookup or moveToNext() inside a loop for multiple records. Column indexes should be obtained from the returned column names rather than relying on their numeric order.

List<Product> products = new ArrayList<>();

try (Cursor cursor = db.rawQuery(sql, args)) {
    int idIndex = cursor.getColumnIndexOrThrow("id");
    int nameIndex = cursor.getColumnIndexOrThrow("name");
    int priceIndex = cursor.getColumnIndexOrThrow("price_cents");
    int stockIndex = cursor.getColumnIndexOrThrow("stock");

    while (cursor.moveToNext()) {
        Product product = new Product(
            cursor.getLong(idIndex),
            cursor.getString(nameIndex),
            cursor.getInt(priceIndex),
            cursor.getInt(stockIndex)
        );
        products.add(product);
    }
}

The try-with-resources statement closes the cursor automatically. This is important because an open cursor consumes resources and can cause problems when an activity is recreated or a list is refreshed frequently. A query that returns no rows simply leaves the loop unentered, so the application can display an empty state rather than assuming a result exists.

For a single item, use a direct check:

Product product = null;

try (Cursor cursor = db.rawQuery(
        "SELECT id, name, price_cents, stock FROM products WHERE id = ?",
        new String[] {"7"})) {

    if (cursor.moveToFirst()) {
        product = new Product(
            cursor.getLong(cursor.getColumnIndexOrThrow("id")),
            cursor.getString(cursor.getColumnIndexOrThrow("name")),
            cursor.getInt(cursor.getColumnIndexOrThrow("price_cents")),
            cursor.getInt(cursor.getColumnIndexOrThrow("stock"))
        );
    }
}

Filter, Join, And Aggregate Records

The strength of rawQuery() becomes clearer with joins and aggregate functions. Suppose a shop database has orders and customers tables. A query can calculate the total value of each customer’s orders:

String sql =
    "SELECT c.id, c.name, SUM(o.total_cents) AS total_spent " +
    "FROM customers c " +
    "JOIN orders o ON o.customer_id = c.id " +
    "WHERE o.created_at >= ? " +
    "GROUP BY c.id, c.name " +
    "HAVING SUM(o.total_cents) > ? " +
    "ORDER BY total_spent DESC";

String[] args = {"2025-01-01T00:00:00Z", "50000"};

The AS total_spent alias gives the calculated column a stable name for getColumnIndexOrThrow(). Dates should use one consistent format, preferably ISO 8601 in UTC, rather than relying on the device’s display format. The interface can still show dates in Australian formats such as 25/01/2025.

When building optional filters, keep the SQL and argument list synchronised. An empty search term should not produce a malformed WHERE clause. For large datasets, add indexes to columns commonly used in WHERE, JOIN, and ORDER BY expressions. An index on category, stock, or customer_id can significantly reduce query time.

Connect Results To The Android Interface

Database work should not run on the main thread. A slow query can freeze scrolling and trigger an application-not-responding error, particularly when a local database contains many transactions or catalogue items. Use an executor, coroutine, or another background mechanism, then deliver the finished list to the UI thread.

ExecutorService executor = Executors.newSingleThreadExecutor();

executor.execute(() -> {
    List<Product> result = loadProducts(context);

    runOnUiThread(() -> {
        adapter.submitList(result);
    });
});

A RecyclerView is generally more flexible than the older ListView for displaying cursor results. Convert each row into a model object, submit the collection to a ListAdapter, and format values at the presentation layer. For example, convert price_cents into an Australian currency string with NumberFormat rather than storing formatted text in SQLite.

Keep the cursor open only for the time needed to copy values into model objects. Close the database helper when its owner is finished with it, and test empty results, missing columns, null values, large datasets, and database upgrades. These checks are especially valuable for apps used across Australian cities and regional areas, where offline records may remain on a device for longer before synchronisation.

A raw SQL query is a precise tool, but it should remain readable. Use selection arguments, explicit column names, meaningful aliases, indexes where justified, and a clear separation between database code and Android views. With those habits, rawQuery() and Cursor can provide reliable SQLite access for both small learning projects and practical mobile applications.