Android SQLiteStatement 編譯、執行 分析

1、Android sqlite 中 sql語句執行流程

SQLite中所有SQL語句都需要先編譯為stmt，然後執行。

首先看一個SQLiteDatabase.update()的過程。

// 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 {        // 構造sql語句        ......        SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);        try {            return statement.executeUpdateDelete();        } finally {            statement.close();        }    } finally {        releaseReference();    }}// SQLiteStamente.javapublic int executeUpdateDelete() {    acquireReference();    try {        return getSession().executeForChangedRowCount(                getSql(), getBindArgs(), getConnectionFlags(), null);    } catch (SQLiteDatabaseCorruptException ex) {        onCorruption();        throw ex;    } finally {        releaseReference();    }}// SQLiteSeesion.javapublic int executeForChangedRowCount(String sql, Object[] bindArgs, int connectionFlags,        CancellationSignal cancellationSignal) {    ......    acquireConnection(sql, connectionFlags, cancellationSignal);    try {        return mConnection.executeForChangedRowCount(sql, bindArgs,                cancellationSignal);    } finally {        releaseConnection();    }}// SQLiteConnection.javapublic int executeForChangedRowCount(String sql, Object[] bindArgs,        CancellationSignal cancellationSignal) {    ......    try {        final PreparedStatement statement = acquirePreparedStatement(sql);        try {            ......            try {                // !!! 開始執行 實質為調用jni中的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);        }    }}

可以看到，首先構造SQLiteStatement對象，然後使用該對象執行，通過session調用到串連池中某個connection的execute相關方法。
其中，在connection中重新構建PreparedStatement，其實該對象才真正指向sqlite中的stmt。
涉及到的資料結構如下。

2、相應資料結構

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; // 指向native中stmt的指標    public int mNumParameters;    public int mType;    public boolean mReadOnly;    public boolean mInCache;    public boolean mInUse;}

可以看出，PreparedStatement中才含有指向native中stmt的指標，那麼SQLiteStament的作用是什麼呢？

3、statement 編譯過程先看下SQLiteProgram建構函式。

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;    }}

可以看到，關鍵是prepare()後對其他成員變數進行了初始化操作。ps： begin commit abort語句為唯讀型，那麼相應statement所需的connection應為primar connection，其他語句應根據prepare結果決定。
prepare()時，如果為select語句，acquireConnection假定為非主串連，其他為主串連。

//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 {        final PrepraedStatement statement = acquirePreparedStatement(sql);        try {            if (outStatementInfo != null) {                outStatementInfo.numParameters = statement.mNumParameters;                outStatementInfo.readOnly = statement.mReadOnly;                final int columnCount = nativeGetColumnCount(// native得到結果的列的個數                        mConnectionPtr, statement.mStatementPtr);                if (columnCount == 0) {                    outStatementInfo.columnNames = EMPTY_STRING_ARRAY;                } else {                    outStatementInfo.columnNames = new String[olumnCount]; //native擷取列的名稱                    for (int i = 0; i < columnCount; 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 statement = mPreparedStatementCache.get(sql);    boolean skipCache = false;    if (statement != null) { // 如果緩衝中存在        if (!statement.mInUse) { // 並且不在使用中            return statement;  // 返回該statement        }        skipCache = true; // 如果已在使用 另備一份並不再緩衝    }    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); // 將statement放入緩衝中            statement.mInCache = true;        }    } catch (RuntimeException ex) {        if (statement == null || !statement.mInCache) {            nativeFinalizeStatement(mConnectionPtr, statementPtr);        }        throw ex;    }    statement.mInUse = true;    return statement;}

這裡出現了mPreparedStatementCache，其實是一種強引用組成的緩衝。先從緩衝中擷取，擷取不到就在native層建立stmt，從池mPreparedStatementPool中擷取並構建為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) {    // 從池中擷取一個statement，並將其從池中移除    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;}

那麼mPreparedStatementPool中是什麼statement呢？從哪裡來的呢？acquirePreparedStatement之後需要releasePreparedStatement。

private void releasePreparedStatement(PreparedStatement statement) {    statement.mInUse = false;    if (statement.mInCache) {            // 如果在緩衝中重設stmt            nativeResetStatementAndClearBindings(mConnectionPtr, statement.mStatementPtr);    } else {// 如果不在緩衝，即緩衝中已經有相同一份        finalizePreparedStatement(statement);    }}private void finalizePreparedStatement(PreparedStatement statement) {    // 銷毀指向的stmt    nativeFinalizeStatement(mConnectionPtr, statement.mStatementPtr);    // 將statement放入mPreparedStatementPool    recyclePreparedStatement(statement);}private void recyclePreparedStatement(PreparedStatement statement) {    statement.mSql = null;    statement.mPoolNext = mPreparedStatementPool;    mPreparedStatementPool = statement;}

很明顯了：release時，如果該statement是從緩衝中獲得的將相應stmt重設，仍作為緩衝中一員；如果原來不在緩衝中，銷毀相應stmt，將statement只留外殼放入mPreparedStatementPool中，以待下次使用，節省資源。
然而，到此時，SQLiteStament構造時，通過prepare()構建出來的PreparedStatement歸屬於SQLiteConnection，SQLiteStament與其沒有聯絡，甚至與SQLiteConnection都沒有聯絡。
同時，注意statement.execute()時，最終在connection中，仍有acquirePreparedStatement()的操作。這是為什麼呢？
聯想到上一節 Android SQLite 開啟、執行分析 acquireConnection時，優先選取其緩衝中含有對應PreparedStatement的connection，就好理解了。SQLiteStatement statement = new SQLiteStatement()時，使得該SQLiteDatabase的某個connection擁有對應的PreparedStatement。statement.execute()時，會在SQLiteDatabase的多個connection中找到含有對應PreparedStatement的connection來使用。假如該connection恰巧被其他線程使用了，得到的是另一個connection，其會重新acquirePreparedStatement。

4、總結

① 每個connection維護著多個PreparedStatement，可以直接使用的在緩衝中，只有外殼沒有stmt已被銷毀的放在池中。

② SQLiteStatement prepare時，多次調用到connection中，先在緩衝中擷取相應PreparedStatement，如果擷取不到在池中擷取只有外殼的PreparedStatement對其重新構建。

③ SQLiteStatement 執行時，通過acquireConnection擷取到最佳connection，通過connection執行相應PreparedStatement；如果不巧最佳connection被其他線程搶走，不含相應PreparedStatement的connection會先行構建PreparedStatement，然後執行。