How do I use prepared statements in SQlite in Android?

Question

How do I use prepared statements in SQlite in Android?

Answer 1

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

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("SELECT * FROM Country WHERE code = ?");
stmt.bindString(1, "US");
stmt.execute();

Answer 2

Nice explanation. Thnx.

Answer 3

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()).

Answer 4

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.

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().