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<String, PreparedStatement> {    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，然后执行。