How do I use prepared statements in SQlite in Android?

Question

How do I use prepared statements in SQlite in Android?

Chock

Answer 1

I use prepared statements in Android all the time, it's quite simple:

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO Country (code) VALUES (?)");
stmt.bindString(1, "US");
stmt.executeInsert();

Gardener

Answer 2

For prepared SQLite statements in Android there is SQLiteStatement. Prepared statements help you speed up performance (especially for statements that need to be executed multiple times) and also help avoid against injection attacks. See this article for a general discussion on prepared statements.

SQLiteStatement is meant to be used with SQL statements that do not return multiple values. (That means you wouldn't use them for most queries.) Below are some examples:

Create a table

String sql = "CREATE TABLE table_name (column_1 INTEGER PRIMARY KEY, column_2 TEXT)";
SQLiteStatement stmt = db.compileStatement(sql);
stmt.execute();

The execute() method does not return a value so it is appropriate to use with CREATE and DROP but not intended to be used with SELECT, INSERT, DELETE, and UPDATE because these return values. (But see this question.)

Insert values

String sql = "INSERT INTO table_name (column_1, column_2) VALUES (57, 'hello')";
SQLiteStatement statement = db.compileStatement(sql);
long rowId = statement.executeInsert();

Note that the executeInsert() method is used rather than execute(). Of course, you wouldn't want to always enter the same things in every row. For that you can use bindings.

String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
SQLiteStatement statement = db.compileStatement(sql);

int intValue = 57;
String stringValue = "hello";

statement.bindLong(1, intValue); // 1-based: matches first '?' in sql string
statement.bindString(2, stringValue);  // matches second '?' in sql string

long rowId = statement.executeInsert();

Usually you use prepared statements when you want to quickly repeat something (like an INSERT) many times. The prepared statement makes it so that the SQL statement doesn't have to be parsed and compiled every time. You can speed things up even more by using transactions. This allows all the changes to be applied at once. Here is an example:

String stringValue = "hello";
try {

    db.beginTransaction();
    String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
    SQLiteStatement statement = db.compileStatement(sql);

    for (int i = 0; i < 1000; i++) {
        statement.clearBindings();
        statement.bindLong(1, i);
        statement.bindString(2, stringValue + i);
        statement.executeInsert();
    }

    db.setTransactionSuccessful(); // This commits the transaction if there were no exceptions

} catch (Exception e) {
    Log.w("Exception:", e);
} finally {
    db.endTransaction();
}

Check out these links for some more good info on transactions and speeding up database inserts.

• Atomic Commit In SQLite (Great in depth explanation, go to Part 3)
• Database transactions
• Android SQLite bulk insert and update example
• Android SQLite Transaction Example with INSERT Prepared Statement
• Turbocharge your SQLite inserts on Android
• https://mcmap.net/q/169703/-android-database-transaction

Update rows

This is a basic example. You can also apply the concepts from the section above.

String sql = "UPDATE table_name SET column_2=? WHERE column_1=?";
SQLiteStatement statement = db.compileStatement(sql);

int id = 7;
String stringValue = "hi there";

statement.bindString(1, stringValue);
statement.bindLong(2, id);

int numberOfRowsAffected = statement.executeUpdateDelete();

Delete rows

The executeUpdateDelete() method can also be used for DELETE statements and was introduced in API 11. See this Q&A.

Here is an example.

try {

    db.beginTransaction();
    String sql = "DELETE FROM " + table_name +
            " WHERE " + column_1 + " = ?";
    SQLiteStatement statement = db.compileStatement(sql);

    for (Long id : words) {
        statement.clearBindings();
        statement.bindLong(1, id);
        statement.executeUpdateDelete();
    }

    db.setTransactionSuccessful();

} catch (SQLException e) {
    Log.w("Exception:", e);
} finally {
    db.endTransaction();
}

Query

Normally when you run a query, you want to get a cursor back with lots of rows. That's not what SQLiteStatement is for, though. You don't run a query with it unless you only need a simple result, like the number of rows in the database, which you can do with simpleQueryForLong()

String sql = "SELECT COUNT(*) FROM table_name";
SQLiteStatement statement = db.compileStatement(sql);
long result = statement.simpleQueryForLong();

Usually you will run the query() method of SQLiteDatabase to get a cursor.

SQLiteDatabase db = dbHelper.getReadableDatabase();
String table = "table_name";
String[] columnsToReturn = { "column_1", "column_2" };
String selection = "column_1 =?";
String[] selectionArgs = { someValue }; // matched to "?" in selection
Cursor dbCursor = db.query(table, columnsToReturn, selection, selectionArgs, null, null, null);

See this answer for better details about queries.

Stoneware

Just a reminder: the .bindString / .bindLong / ... methods are all 1-based. – Angelinaangeline

I was looking under the hood of Android convenience methods such as .query, .insert and .delete and noticed that they use SQLiteStatement under the hood. Wouldn't it be easier to just use convenience methods instead of building your own statements? – Gorham

@NicolásCarrasco, it has been a while since I have worked on this so I am a little rusty now. For queries and single inserts, updates and deletes, I would definitely use the convenience methods. However, if you are doing mass inserts, updates, or deletes, I would consider prepared statements along with a transaction. As to SQLiteStatement being used under the hood and whether the convenience methods are good enough, I can't speak to that. I guess I would say, if the convenience methods are performing fast enough for you, then use them. – Stoneware

Why do you use clearBindings()? You bind all your values without any condition. It doesn't make sense to me to set them null first and the to the real value. – Cantlon

@TheincredibleJan, I don't know for sure. It might not be necessary and you can try it without clearing the bindings to see if it makes a difference. However, that said, calling clearBindings() doesn't just set them to null (see the source code). I look at it as clearing the state so that nothing is influencing it from the previous loop. Maybe that's not necessary, though. I'd be glad for someone who knows to comment. – Stoneware

Modern (API 30) clearBindings() is just Arrays.fill. I think that calling clearBindings() makes sense after execute() to unleak bind arguments. – Trickery

I work on an old app where instead of clearBindings() every value is checked and if it's null the other developer used bindNull() in else clause. Now I will use clearBindings() after executeInsert() and do it without the else clause. – Cantlon

Answer 3

If you want a cursor on return, then you might consider something like this:

SQLiteDatabase db = dbHelper.getWritableDatabase();

public Cursor fetchByCountryCode(String strCountryCode)
{
    /**
     * SELECT * FROM Country
     *      WHERE code = US
     */
    return cursor = db.query(true, 
        "Country",                        /**< Table name. */
        null,                             /**< All the fields that you want the 
                                                cursor to contain; null means all.*/
        "code=?",                         /**< WHERE statement without the WHERE clause. */
        new String[] { strCountryCode },    /**< Selection arguments. */
        null, null, null, null);
}

/** Fill a cursor with the results. */
Cursor c = fetchByCountryCode("US");

/** Retrieve data from the fields. */
String strCountryCode = c.getString(cursor.getColumnIndex("code"));

/** Assuming that you have a field/column with the name "country_name" */
String strCountryName = c.getString(cursor.getColumnIndex("country_name"));

See this snippet Genscripts in case you want a more complete one. Note that this is a parameterized SQL query, so in essence, it's a prepared statement.

Calorific

Small mistake in the code above: It should be "new String[] { strCountryCode }," instead of "new String { strCountryCode }". – Spoliate

You need to move the cursor before you can retrieve the data – Mouflon

This isn't really a prepared statement though. A true prepared statement can offer some performance advantages, as it can pre-compile and cache the query plan instead of needing to calculate it again for each query. – Judson

Answer 4

jasonhudgins example won't work. You can't execute a query with stmt.execute() and get a value (or a Cursor) back.

You can only precompile statements that either returns no rows at all (such as an insert, or create table statement) or a single row and column, (and use simpleQueryForLong() or simpleQueryForString()).

Lalo

Answer 5

To get a cursor, you can't use a compiledStatement. However, if you want to use a full prepared SQL statement, I recommend an adaptation of jbaez's method... Using db.rawQuery() instead of db.query().

Wycliffite