Android SQLiteStatement 編譯、執行 分析

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

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

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

// SQLiteDatabase.java
public 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.java
public int executeUpdateDelete() {
    acquireReference();
    try {
        return getSession().executeForChangedRowCount(
                getSql(), getBindArgs(), getConnectionFlags(), null);
    } catch (SQLiteDatabaseCorruptException ex) {
        onCorruption();
        throw ex;
    } finally {
        releaseReference();
    }
}

// SQLiteSeesion.java
public int executeForChangedRowCount(String sql, Object[] bindArgs, int connectionFlags,
        CancellationSignal cancellationSignal) {
    ......
    acquireConnection(sql, connectionFlags, cancellationSignal);
    try {
        return mConnection.executeForChangedRowCount(sql, bindArgs,
                cancellationSignal);
    } finally {
        releaseConnection();
    }
}


// SQLiteConnection.java
public 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.java
public void prepare(String sql, int connectionFlags, CancellationSignal cancelationSignal,
        SQLiteStatementInfo outStatementInfo) {
    acquireConnection(sql, connectionFlags, cancellationSignal);
    try {
        mConnection.prepare(sql, outStatementInfo);
    } finally {
        releaseConnection();
    }
}
// SQLiteConnection.java
public 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，然後執行。