Android SQLiteStatement 编译、执行 分析

from:http://blog.youkuaiyun.com/efeics/article/details/18995433

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、总结

1.每个connection维护着多个PreparedStatement，可以直接使用的在缓存中，只有外壳没有stmt已被销毁的放在池中。

2.SQLiteStatement prepare时，多次调用到connection中，先在缓存中获取相应PreparedStatement，如果获取不到在池中获取只有外壳的PreparedStatement对其重新构建。

3.SQLiteStatement 执行时，通过acquireConnection获取到最佳connection，通过connection执行相应PreparedStatement；如果不巧最佳connection被其他线程抢走，不含相应PreparedStatement的connection会先行构建PreparedStatement，然后执行。