SQL parameterized queries in Android

Question

I've been looking over what interfaces/classes exist in Android for parametarized queries and it's a bit of a mess.

Can someone make it clear what is the best (and safest) among:

• using SQLiteDatabase.query()
• using SQLiteDatabase.rawQuery()
• using SQLiteStatement
• using SQLiteQueryBuilder
• using compileStatement()
• using managedQuery()
• using ContentValues
• using something else, e.g. execSQL()

Can anyone explain why so many exist, what's the difference between them and what's the best way to have bind parameters for querys?

Answer 1

I've been looking over what interfaces/classes exist in Android for parametarized queries and it's a bit of a mess.

Not really.

Can someone make it clear what is the best (and safest) among:

No, because you have not defined what you think is the criteria for "best".

using SQLiteDatabase.query() using SQLiteDatabase.rawQuery()

Personally, of these, I use rawQuery(), as I find it to be the most readable. IMHO, query() is more for cases where you are trying to construct a SELECT statement from individual pieces (e.g., table name, list of columns), because perhaps some of it is variable (e.g., different tables for different users). At the end of the day, they both do the same thing. query() uses SQLiteQueryBuilder under the covers (see below).

using SQLiteStatement using compileStatement()

These are the same thing. SQLiteStatement is what Java refers to as a "class". compileStatement() is what Java refers to as a "method". The compileStatement() method returns an instance of the SQLiteStatement class.

SQLiteStatement is not usually that useful for queries, as it cannot return complete result sets, except for single-column/single-row responses.

using SQLiteQueryBuilder

This is another way of constructing a query from individual pieces. It is useful in cases where multiple parties get to decide what goes in the query, such as a ContentProvider and a consumer of that ContentProvider. Beyond that, though, in the end, it executes a rawQuery(). Hence, there is no significant difference in results from using SQLiteQueryBuilder or using rawQuery() directly.

using managedQuery()

This is deprecated and is not related to SQLiteDatabase in any event.

using ContentValues

This has nothing to do with queries against a SQLiteDatabase.

Can anyone explain why so many exist

Some serve different roles, in many cases having nothing to do with queries against a SQLiteDatabase.

With respect to the three valid options out of your roster (rawQuery(), query(), and SQLiteQueryBuilder), they all do the same thing: query() uses SQLiteQueryBuilder, and SQLiteQueryBuilder uses rawQuery(). The only difference is in how the SQL SELECT statement is constructed.