views:

2288

answers:

5

How do I use prepared statements in SQlite in Android?

+6  A: 

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();
Is it possible with prepared statements to put arguments that are not values? like "SELECT * FROM ?"?
J. Pablo Fernández
A: 

Nice explanation. Thnx.

A: 

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

+2  A: 

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.

jbaez
A: 

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

Aaron