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<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，然后执行。