lead
The SQLiteDatabase class is a familiar one. Add, delete, change and check the database, it is inevitable to deal with it, including:
int delete(String table, String whereClause, String[] whereArgs))Cursor query(String table, String[] columns, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit))int update(String table, ContentValues values, String whereClause, String[] whereArgs))
Delete, modify and check the three operations often need to be qualified. The qualifier is specified with the String whereClause, String[] whereArgs argument.
The value of whereClause is _id =? AND condition1 >= ? OR condition2 ! =? , among them? For parameter binding, fill the whereArgs array, in order.
But to be honest, using this approach requires:
- Write the SQL statement of the qualified part first, and replace the qualified parameter with
?; - Remember the order and fill in the parameters array.
This is fine for once, but it can be annoying to write too much. If you make changes later, you need to be careful to make sure that the SQL statement is written correctly, and to make sure that you don’t change the parameters in the wrong order. If I get it wrong? We’ll have to debug it slowly.
For convenience, some students simply pass in a complete SQL qualified string as the value of the whereClause parameter. Instead of parameterized, they pass in null in the whereClause parameter.
It works just as well and is even easier to use, so why does the SDK have to be so unnecessarily complicated? A: It’s all about performance.
The source code to explore
Let’s take a look at the source code, explore the process, and see where the SDK optimizes performance.
Int delete(String table, String whereClause, String[] whereArgs)
/**
* Convenience method for deleting rows in the database.
*
* @param table the table to delete from
* @param whereClause the optional WHERE clause to apply when deleting.
* Passing null will delete all rows.
* @paramwhereArgs You may include ? s in the where clause, which * will be replaced by the values from whereArgs. The values * will be bound as Strings. *@return the number of rows affected if a whereClause is passed in, 0
* otherwise. To remove all rows and get a count pass "1" as the
* whereClause.
*/
public int delete(String table, String whereClause, String[] whereArgs) {
acquireReference();
try {
// Assemble a complete SQL statement to instantiate SQLiteStatement
SQLiteStatement statement = new SQLiteStatement(this."DELETE FROM "+ table + (! TextUtils.isEmpty(whereClause) ?" WHERE " + whereClause : ""), whereArgs);
try {
// Execute the SQL statement
return statement.executeUpdateDelete();
} finally{ statement.close(); }}finally{ releaseReference(); }}Copy the codeThere is nothing magical about the source code, just assemble a complete SQL statement, instantiate SQLiteStatement with the array of parameters, and execute the statement.
The procedure is implemented in SQLiteStatement. Java.
/**
* Execute this SQL statement, if the the number of rows affected by execution of this SQL
* statement is of any importance to the caller - for example, UPDATE / DELETE SQL statements.
*
* @return the number of rows affected by this SQL statement execution.
* @throws android.database.SQLException If the SQL string is invalid for
* some reason
*/
public int executeUpdateDelete(a) {
acquireReference();
try {
// Get parameters: SQL statement, parameters to bind, etc
// Then there is the actual execution
return getSession().executeForChangedRowCount(
getSql(), getBindArgs(), getConnectionFlags(), null);
} catch (SQLiteDatabaseCorruptException ex) {
onCorruption();
throw ex;
} finally{ releaseReference(); }}Copy the codeTo obtain parameters, and then call executeForChangedRowCount method. This method is in SQLiteSession. Java.
/**
* Executes a statement that returns a count of the number of rows
* that were changed. Use for UPDATE or DELETE SQL statements.
*
* @param sql The SQL statement to execute.
* @param bindArgs The arguments to bind, or null if none.
* @param connectionFlags The connection flags to use if a connection must be
* acquired by this operation. Refer to {@link SQLiteConnectionPool}.
* @param cancellationSignal A signal to cancel the operation in progress, or null if none.
* @return The number of rows that were changed.
*
* @throws SQLiteException if an error occurs, such as a syntax error
* or invalid number of bind arguments.
* @throws OperationCanceledException if the operation was canceled.
*/
public int executeForChangedRowCount(String sql, Object[] bindArgs, int connectionFlags,
CancellationSignal cancellationSignal) {
if (sql == null) {
throw new IllegalArgumentException("sql must not be null.");
}
// bindArgs is passed in, but not used
if (executeSpecial(sql, bindArgs, connectionFlags, cancellationSignal)) {
return 0;
}
acquireConnection(sql, connectionFlags, cancellationSignal); // might throw
try {
// Where to use SQL and bindArgs
return mConnection.executeForChangedRowCount(sql, bindArgs,
cancellationSignal); // might throw
} finally {
releaseConnection(); // might throw}}Copy the codeOk, let’s go further and go to sqliteconnection.java.
/**
* Executes a statement that returns a count of the number of rows
* that were changed. Use for UPDATE or DELETE SQL statements.
*
* @param sql The SQL statement to execute.
* @param bindArgs The arguments to bind, or null if none.
* @param cancellationSignal A signal to cancel the operation in progress, or null if none.
* @return The number of rows that were changed.
*
* @throws SQLiteException if an error occurs, such as a syntax error
* or invalid number of bind arguments.
* @throws OperationCanceledException if the operation was canceled.
*/
public int executeForChangedRowCount(String sql, Object[] bindArgs, CancellationSignal cancellationSignal) {
if (sql == null) {
throw new IllegalArgumentException("sql must not be null.");
}
int changedRows = 0;
final int cookie = mRecentOperations.beginOperation("executeForChangedRowCount",
sql, bindArgs);
try {
// Get the pre-compiled SQL
final PreparedStatement statement = acquirePreparedStatement(sql);
try {
throwIfStatementForbidden(statement);
// Parameter binding
bindArguments(statement, bindArgs);
applyBlockGuardPolicy(statement);
attachCancellationSignal(cancellationSignal);
try {
// To SQLiteEngine
changedRows = nativeExecuteForChangedRowCount(
mConnectionPtr, statement.mStatementPtr);
return changedRows;
} finally{ detachCancellationSignal(cancellationSignal); }}finally{ releasePreparedStatement(statement); }}catch (RuntimeException ex) {
mRecentOperations.failOperation(cookie, ex);
throw ex;
} finally {
if (mRecentOperations.endOperationDeferLog(cookie)) {
mRecentOperations.logOperation(cookie, "changedRows="+ changedRows); }}}Copy the codeAcquirePreparedStatement acquirePreparedStatement acquirePreparedStatement acquirePreparedStatement acquirePreparedStatement acquirePreparedStatement
private PreparedStatement acquirePreparedStatement(String sql) {
PreparedStatement statement = mPreparedStatementCache.get(sql);
boolean skipCache = false;
if(statement ! =null) {
if(! statement.mInUse) {return statement;
}
// The statement is already in the cache but is in use (this statement appears
// to be not only re-entrant but recursive!). So prepare a new copy of the
// statement but do not cache it.
skipCache = true;
}
final long statementPtr = nativePrepareStatement(mConnectionPtr, sql);
try {
final int numParameters = nativeGetParameterCount(mConnectionPtr, statementPtr);
final int type = DatabaseUtils.getSqlStatementType(sql);
final boolean readOnly = nativeIsReadOnly(mConnectionPtr, statementPtr);
statement = obtainPreparedStatement(sql, statementPtr, numParameters, type, readOnly);
if(! skipCache && isCacheable(type)) { mPreparedStatementCache.put(sql, statement); statement.mInCache =true; }}catch (RuntimeException ex) {
// Finalize the statement if an exception occurred and we did not add
// it to the cache. If it is already in the cache, then leave it there.
if (statement == null| |! statement.mInCache) { nativeFinalizeStatement(mConnectionPtr, statementPtr); }throw ex;
}
statement.mInUse = true;
return statement;
}Copy the codeAs you can see, there is a mPreparedStatementCache that is used to cache preparedStatements that have been previously generated. If the PreparedStatement is the same as the SQL statement, it will be reused. The cache is essentially a LruCache
, and the key is an SQL statement.
That is, if we use whereClause and whereArgs to manipulate the database, the same whereClause, with different values of whereClause, will take advantage of the cache. However, if the qualified statement is concatenated directly, as the parameter value is variable, it will become a different statement once changed, which will not match the cache and waste the compiled PreparedStatement instance.
Incidentally, post the code for binding parameters:
private void bindArguments(PreparedStatement statement, Object[] bindArgs) {
final intcount = bindArgs ! =null ? bindArgs.length : 0;
if(count ! = statement.mNumParameters) {throw new SQLiteBindOrColumnIndexOutOfRangeException(
"Expected " + statement.mNumParameters + " bind arguments but "
+ count + " were provided.");
}
if (count == 0) {
return;
}
final long statementPtr = statement.mStatementPtr;
for (int i = 0; i < count; i++) {
final Object arg = bindArgs[i];
switch (DatabaseUtils.getTypeOfObject(arg)) {
case Cursor.FIELD_TYPE_NULL:
nativeBindNull(mConnectionPtr, statementPtr, i + 1);
break;
case Cursor.FIELD_TYPE_INTEGER:
nativeBindLong(mConnectionPtr, statementPtr, i + 1,
((Number)arg).longValue());
break;
case Cursor.FIELD_TYPE_FLOAT:
nativeBindDouble(mConnectionPtr, statementPtr, i + 1,
((Number)arg).doubleValue());
break;
case Cursor.FIELD_TYPE_BLOB:
nativeBindBlob(mConnectionPtr, statementPtr, i + 1, (byte[])arg);
break;
case Cursor.FIELD_TYPE_STRING:
default:
if (arg instanceof Boolean) {
// Provide compatibility with legacy applications which may pass
// Boolean values in bind args.
nativeBindLong(mConnectionPtr, statementPtr, i + 1,
((Boolean)arg).booleanValue() ? 1 : 0);
} else {
nativeBindString(mConnectionPtr, statementPtr, i + 1, arg.toString());
}
break; }}}Copy the codeThe code is so simple that I won’t explain it much.
Data validation
The source code is posted AD nauseam just to prove that whereClause paired with whereArgs makes sense. That said, but how much worse is the actual optimization performance?
The demo verifies that the query is executed 1000 times: simple statement _ID =? :
| Without a parameterized | A parameterized |
|---|---|
| 112 | 65 |
Increase the complexity. _ID >=? :
| Without a parameterized | A parameterized |
|---|---|
| 150 | 71 |
_ID >=? AND COLUMN_CATEGORY LIKE ? :
| Without a parameterized | A parameterized |
|---|---|
| 190 | 87 |
Conclusion:
- As the complexity of qualified statements increases, the time required to compile a SQL statement increases.
- Use parameterized statements to improve performance effectively, and use parameterized statements to roughly double performance (the more complex the scenario, the more significant the effect)
So… For performance reasons, don’t write code that concatenates strings directly to generate qualified statements.
One more things…
However, the first mentioned in the way of inconvenient use, can only be silently endured? The answer, of course, is not that simple abstraction and encapsulation can achieve the following effects:
Statement statement =
Statement.where(UPDATE_TIME).lessOrEqual(now)
.and(EXPIRY_TIME).moreThan(now)
.or(AGE).eq(23)
.end();
statement.sql(); // Generate an SQL statement
statement.whereClause(); // Generate the whereClause statement
statement.args(); // The corresponding parameter arrayCopy the codeThis is a wheel I tried to build to define and generate whereClause and whereArgs semantically. It is almost as natural to use as a SQL statement, and avoids spelling errors caused by manual SQL writing. The resulting whereClause parameters are in exactly the same order as the whereArgs parameter array.
Write more bad, do not send out to show off. Ha ha. If there is a wrong place, please correct the gods!