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