What’s good about selectionArgs in SQLite queries
The Android API for querying SQLite databases supports two styles of queries:
query(uri, projection, selection = "column=" + value, selectionArgs = null, sortOrder)query(uri, projection, selection = "column=?", selectionArgs = { value_as_string }, sortOrder)
Obviously, the first one is more straightforward and convenient. Then what’s good about the second one?
Let me try to sell the goodness of selectionArgs via a simple example.
Suppose you are querying contacts with phone number, say, “+8612345678901″. With the first style, you get the WHERE clause "number=+8612345678901" which is the result of string concatenation ‘"number=" + "+8612345678901"‘.
It is easy to see that phone numbers are not pure numbers, i.e., they are not numeric. For example, the number “(010) 87654321 ” and “010-87654321-001″, are valid phone numbers, but are neither valid integer nor real numbers ^(Of course, you don’t want to do the subtraction of the latter.)$. Thus, the the type of the number column (storage class in SQLite’s idiom) is TEXT.
From this nonnumeric phone number string raises a problem, a quite subtle one. With its dynamic type system、column affinity and type conversion, SQLite will try to convert “+8612345678901″ to a text string. But the express “+8612345678901″ is numeric originally, because it has no quote marks surrounding it, and numeric values are operated according numeric rules first. So it is first normalized to “8612345678901″, and then the normalized value is converted to a text string “‘8612345678901‘“.
So the query that is really fed into the SQLite engine is something like this:
SELECT * FROM contacts_table WHERE number='8612345678901'
But as a text string, ’8612345678901′ can not be matched with ‘+8612345678901′, and the query fails.
We were suffering this kind of adversity until selectionArgs came to save the day.
With the second style, Android SQL query builder will replace ?s in selection with the values from selectionArgs, in order that they appear in the selection. The values must be String and will be treated as text strings by SQLite, i.e., they must be converted to their String representation first if they are not String, and will be quoted automatically when replacing the ?s. So the query that is fed into the SQLite engine is something like this:
<SELECT * FROM contacts_table WHERE number='+8612345678901'
At this point, it doesn’t matter whether the underlying column type is really TEXT or not, because whatever the TEXT value is derived from, it can be correctly cast back to the original column type. The key point is that TEXT can be losslessly converted to any other types, but not necessarily vice versa as shown above.
In fact, we can stick to the first style by manually simulating the works done by Android SQL query builder this way:
query(uri, projection, selection = "column=" + "'" + value + "'", selectionArgs = null, sortOrder)
But as you can see, it is such a tedious concatenation with just one query column, you don’t want to imagine the mess with more complex queries, do you?