Android SQLite Tip 2 – Use compiled SQL statements

Posted on August 16, 2011

4


In my last article  I talked about SQL Lite inserts on Android being optimised using a transaction. Another method I’ve found that speeds things up dramatically is to use compiled SQL statements with the SQLiteStatement class.

I can’t believe I hadn’t used these before, I actually think working with a compiled statement is much nicer than working with the  standard SQL insert/update methods but there are a couple of points to remember.

  • They only work for INSERTS, UPDATES, DELETES or single long or string SELECTS.
  • They are not syncronized, so if using multiple threads you must roll your own syncronization.
A SQLLiteStatement INSERT definition looks like this:
SQLiteDatabase db = DBHelper.getWritableDatabase();
mInsertAttributeStatement = db.compileStatement("INSERT INTO UserProfileAttributes (UserId, AttributeKey, AttributeValue) VALUES (?,?,?)");
Here we are compiling the INSERT statement, we are adding parameter value place holders using the ‘?’ keyword as you would with an execSql() call rawQuery() call. The mInsertAttributeStatement  can then be reused with different parameter values as follows:
mInsertAttributeStatement.bindLong(1, userId);
mInsertAttributeStatement.bindString(2, key);
mInsertAttributeStatement.bindString(3, value);
mInsertAttributeStatement.execute();
Here we are using a 1 indexed bindings to assign values to the ‘?’ placeholders in the compiled SQL statement, nice ey!?
If a binding already exists at the assigned index then it will be overwritten. Bindings can be cleared at anytime using the clearBindings() method.
Advertisements
Posted in: Android, SQLLite