Android SQLiteStatement compilation and execution Analysis

1. SQL statement execution process in Android sqlite

All SQL statements in SQLite must be compiled into stmt and then executed.

First, let's take a look at the SQLiteDatabase. update () process.

// SQLiteDatabase. javapublic int update (String table, ContentValues values, String whereClause, String [] whereArgs) {return updateWithOnConflict (table, values, whereClause, whereArgs, CONFLICT_NONE );} public int updateWithOnConflict (String table, ContentValues values, String whereClause, String [] whereArgs, int conflictAlgorithm) {acquireReference (); try {// construct an SQL statement ...... SQLiteStatement stateme Nt = new SQLiteStatement (this, SQL. toString (), bindArgs); try {return statement.exe cuteUpdateDelete ();} finally {statement. close () ;}finally {releaseReference () ;}// SQLiteStamente. javapublic int evaluate () {acquireReference (); try {return getsession(.exe cuteForChangedRowCount (getSql (), getBindArgs (), getConnectionFlags (), null);} catch (writable ex ){ Onituption (); throw ex;} finally {releaseReference () ;}// SQLiteSeesion. javapublic int executeForChangedRowCount (String SQL, Object [] bindArgs, int connectionFlags, CancellationSignal cancellationSignal ){...... acquireConnection (SQL, connectionFlags, cancellationSignal); try {return mConnection.exe cuteForChangedRowCount (SQL, bindArgs, cancellationSignal);} finally {releaseConnection ();} // SQLiteConnection. javapublic int executeForChangedRowCount (String SQL, Object [] bindArgs, CancellationSignal cancellationSignal ){...... try {final PreparedStatement statement = acquirePreparedStatement (SQL); try {...... try {//!!! The execution starts by calling executeNonQuery 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 );}}}

We can see that the SQLiteStatement object is constructed first, and then executed using this object. The session is used to call the execute method of a connection in the connection pool.
Here, the object is actually directed to the stmt in sqlite after the PreparedStatement is re-built in connection.
The involved data structure is as follows.

2. Data Structure

Public final class SQLiteStatement extends SQLiteProgram {SQLiteStatement (SQLiteDatabase db, String SQL, Object [] bindArgs) {super (db, SQL, bindArgs, null );}} public abstract class SQLiteProgram extends SQLiteClosable {private static final String [] EMPTY_STRING_ARRAY = new String [0]; private final SQLiteDatabase mDatabase; private final String mSql; private final boolean mReadOnly; private final String [] mColumnNames; private final int mNumParameters; private final Object [] mBindArgs;} private static final class PreparedStatement {public PreparedStatement mPoolNext; public String mSql; public int mStatementPtr; // pointer to stmt in native public int mNumParameters; public int mType; public boolean mReadOnly; public boolean mInCache; public boolean mInUse ;}

We can see that the PreparedStatement contains a pointer to stmt in native. What is the role of SQLiteStament?

3. Check the SQLiteProgram constructor before compiling statement.

SQLiteProgram(SQLiteDatabase db, String sql, Object[] bindArgs,        CancellationSignal cancellationSignalForPrepare) {    mDatabase = db;    mSql = sql.trim();    int n = DatabaseUtils.getSqlStatementType(mSql);    switch (n) {        case DatabaseUtils.STATEMENT_BEGIN:        case DatabaseUtils.STATEMENT_COMMIT:        case DatabaseUtils.STATEMENT_ABORT:            mReadOnly = false;            mColumnNames = EMPTY_STRING_ARRAY;            mNumParameters = 0;            break;        default:            boolean assumeReadOnly = (n == DatabaseUtils.STATEMENT_SELECT);            SQLiteStatementInfo info = new SQLiteStatementInfo();            db.getThreadSession().prepare(mSql,                    db.getThreadDefaultConnectionFlags(assumeReadOnly),                    cancellationSignalForPrepare, info);            mReadOnly = info.readOnly;            mColumnNames = info.columnNames;            mNumParameters = info.numParameters;            break;    }    if (bindArgs != null && bindArgs.length > mNumParameters) {        throw new IllegalArgumentException("Too many bind arguments.  "                + bindArgs.length + " arguments were provided but the statement needs "                + mNumParameters + " arguments.");    }    if (mNumParameters != 0) {        mBindArgs = new Object[mNumParameters];        if (bindArgs != null) {            System.arraycopy(bindArgs, 0, mBindArgs, 0, bindArgs.length);        }    } else {        mBindArgs = null;    }}

As you can see, the key is to initialize other member variables after prepare. Ps: If the begin commit abort statement is read-only, the connection required for the corresponding statement should be primar connection. Other statements should be determined based on the prepare result.
For prepare (), if it is a select statement, acquireConnection is assumed to be a non-primary connection, and other primary connections.

// SQLiteSeesion. javapublic void prepare (String SQL, int connectionFlags, CancellationSignal cancelationSignal, SQLiteStatementInfo outStatementInfo) {acquireConnection (SQL, connectionFlags, cancellationSignal); try {mConnection. prepare (SQL, outStatementInfo);} finally {releaseConnection () ;}// SQLiteConnection. javapublic void prepare (String SQL, SQLiteStatementInfo outStatementInfo) {try {fi Nal PrepraedStatement statement = acquirePreparedStatement (SQL); try {if (outStatementInfo! = Null) {outStatementInfo. numParameters = statement. mNumParameters; outStatementInfo. readOnly = statement. mReadOnly; final int columnCount = nativeGetColumnCount (// Number of columns in the native result obtained mConnectionPtr, statement. mStatementPtr); if (columnCount = 0) {outStatementInfo. columnNames = EMPTY_STRING_ARRAY;} else {outStatementInfo. columnNames = new String [olumnCount]; // native obtains the column name for (int I = 0; I <col UmnCount; I ++) {outStatementInfo. columnNames [I] = nativeGetColumnName (mConnectionPtr, statement. mStatementPtr, I) ;}}} finally {releasePreparedStatement (statement) ;}} catch (RuntimeException ex) {mRecentOperations. failOperation (cookie, ex); throw ex;} finally {mRecentOperations. endOperation (cookie) ;}} private PreparedStatement acquirePreparedStatement (String SQL) {PreparedStatement st Atement = mPreparedStatementCache. get (SQL); boolean skipCache = false; if (statement! = Null) {// if the cache contains if (! Statement. mInUse) {// return statement if not in use; // return this statement} skipCache = true; // if you are using another backup and no longer caching} final int statementPtr = nativePrepareStatement (mConnectionPtr, SQL); // native 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); // put the statement into the cache. mInCache = true ;}} catch (RuntimeException ex) {if (statement = null |! Statement. mInCache) {nativeFinalizeStatement (mConnectionPtr, statementPtr);} throw ex;} statement. mInUse = true; return statement ;}

MPreparedStatementCache is a cache composed of strong references. First, get it from the cache. If it cannot be obtained, create a new stmt in the native layer, and obtain it from the pool mPreparedStatementPool and build it as PreparedStatement.

Private final class PreparedStatementCache extends LruCache
 
  
{Public PreparedStatementCache (int size) {super (size) ;}} private PreparedStatement obtainPreparedStatement (String SQL, int statementPtr, int numParameters, int type, boolean readOnly) {// obtain a statement from the pool and remove it from the pool. PreparedStatement statement = mPreparedStatementPool; if (statement! = Null) {// mPreparedStatementPool = statement. mPoolNext; statement. mPoolNext = null; statement. mInCache = false;} else {statement = new PreparedStatement ();} statement. mSql = SQL; statement. mStatementPtr = statementPtr; statement. mNumParameters = numParameters; statement. mType = type; statement. mReadOnly = readOnly; return statement ;}
 

So what is the statement in the mPreparedStatementPool? Where did they come from? ReleasePreparedStatement is required after acquirePreparedStatement.

Private void releasePreparedStatement (PreparedStatement statement) {statement. mInUse = false; if (statement. mInCache) {// If stmt nativeResetStatementAndClearBindings (mConnectionPtr, statement. mStatementPtr);} else {// if it is not in the cache, the cache already has the same finalizePreparedStatement (statement);} private void finalizePreparedStatement (PreparedStatement statement) {// destroy the stmt nativeFinalizeStatement (mConnectionPtr, statement. mStatementPtr); // put the statement into mPreparedStatementPool recyclePreparedStatement (statement);} private void recyclePreparedStatement (PreparedStatement statement) {statement. mSql = null; statement. mPoolNext = mPreparedStatementPool; mPreparedStatementPool = statement ;}

Obviously: In release, if the statement is obtained from the cache, it resets the corresponding stmt and is still a member of the cache. If it is not in the cache, it destroys the corresponding stmt, place the statement shell in the mPreparedStatementPool to save resources.
However, when SQLiteStament is constructed, the PreparedStatement constructed through prepare () belongs to SQLiteConnection, SQLiteStament is not associated with it, or even SQLiteConnection.
At the same time, when statement.exe cute () is executed, the acquirePreparedStatement () operation still exists in the connection. Why?
Think of the previous section Open and execute Analysis on Android SQLiteWhen acquireConnection is enabled, select the connection whose cache contains the corresponding PreparedStatement. When SQLiteStatement = new SQLiteStatement (), a connection of the SQLiteDatabase has a corresponding PreparedStatement. Statement.exe cute (), the connection containing the PreparedStatement is found in multiple connections of SQLiteDatabase. If the connection happens to be used by other threads, the other connection will be returned, and it will re-act quirepreparedstatement.

4. Summary

① Each connection maintains multiple PreparedStatement, which can be directly used in the cache. Only the shell that has not been destroyed by stmt is stored in the pool.

② When SQLiteStatement prepare is called to the connection for multiple times, the PreparedStatement is obtained in the cache first. If the PreparedStatement is not obtained in the pool, the PreparedStatement with only the shell is obtained to re-build it.

③ When SQLiteStatement is executed, the optimal connection is obtained through acquireConnection and the corresponding PreparedStatement is executed through connection. If the optimal connection is stolen by other threads, The PreparedStatement is constructed and executed without the corresponding PreparedStatement.