Saturday, November 02, 2013

Android SQLite queries and numeric predicates

The typical code for making an Android SQLite query goes something like this:

Uri uri = MyProvider.URI;
String[] projection = ...;
String where = COLUMN_NAME + "=?";
String[] whereArgs = new String[] { someValue };
String sortOrder = SORT_COL + " " + sortDir;
Cursor c = getContentResolver().query(uri, projection, 
  where, whereArgs, sortOrder);

But what if someValue isn't a String? What if you want to query your database on a numeric column? I was bashing my head against this and getting wrong results until I stumbled onto the suggestion that the "?" argument needs to be converted to a number. How to do it? Just add zero.

long someValue = ...;
Uri uri = MyProvider.URI;
String[] projection = ...;
String where = COLUMN_NAME + "=(?+0)";
String[] whereArgs = new String[] { Long.toString(someValue) };
String sortOrder = SORT_COL + " " + sortDir;
Cursor c = getContentResolver().query(uri, projection, 
  where, whereArgs, sortOrder);

You have to pass your selection arguments as an array of strings, so you'll use toString for that. But the query itself can use an addition operation to convert the string argument to a number. See the "+0" in the where string? That's it.

Maybe there's another, even better way, but this solved my problem - my queries now return just what they're supposed to. All you need is plus.