注意事項:
如果 SQLiteOpenHelper 使用的是單例，SQLiteDatabase 對 CRUD 操作都是從同一個連接池中獲取連接. 默認情況下, 連接池中只有一條主連接, 所以同一時間只能進行一項操作，多線程讀寫幾乎是無用功；
enableWriteAheadLogging() 方法可以使得多線程并發(fā)查詢可行，但默認沒有開啟該功能, 該方法會根據(jù)配置在連接池中創(chuàng)建多條連接；
android sqlite 不支持多 SQLiteOpenHelper 實例、多線程并發(fā)寫入操作，會拋出異?！癲atabase is locked”;
插入單條數(shù)據(jù)不需要開啟事務;
全局引用一個 SQLiteDatabase 時，是否存在不主動調(diào)用 close() 但被動 close() 的情況？
SQLiteCursor 的獲取與觸發(fā)對數(shù)據(jù)庫的真正查詢是分離的，獲取 SQLiteCursor 后、 查詢數(shù)據(jù)庫前數(shù)據(jù)庫的狀態(tài)發(fā)生變化(如“被關閉”)，是否會出現(xiàn)問題？（真正查詢時獲取不到連接）
執(zhí)行 sql 語句的正確方式：
db.beginTransaction();
try {
...
// 注意該語句放在 try 語句塊的最后，表明最終的操作成功
db.setTransactionSuccessful();
} finally {
// 注意該語句放在 finally 語句塊中，確定進行 roll back 或 commit
db.endTransaction();
}
一、添加數(shù)據(jù)操作(C:增)
1. 第一種添加數(shù)據(jù)方式：調(diào)用 SQLiteDatabase 中的 insert() 方法
/**
* 在 values == null 或者 values.size() == 0 的情況下 nullColumnHack 才會起作用，
* nullColumnHack 的作用是插入數(shù)據(jù)時 nullColumnHack 所在列的 value 為 NULL
*
* Convenience method for inserting a row into the database.
*
* @param table the table to insert the row into
* @param nullColumnHack optional; may be <code>null</code>.
* SQL doesn't allow inserting a completely empty row without
* naming at least one column name. If your provided <code>values</code> is
* empty, no column names are known and an empty row can't be inserted.
* If not set to null, the <code>nullColumnHack</code> parameter
* provides the name of nullable column name to explicitly insert a NULL into
* in the case where your <code>values</code> is empty.
* @param values this map contains the initial column values for the
* row. The keys should be the column names and the values the
* column values
* @return the row ID of the newly inserted row, or -1 if an error occurred
*/
public long insert(String table, String nullColumnHack, ContentValues values) {
try {
// 注意此處為 CONFLICT_NONE
return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values, e);
return -1;
}
}
2. 第二種添加數(shù)據(jù)方式：調(diào)用 SQLiteDatabase 中的 replace() 方法
/**
* 在 initialValues == null 或者 initialValues.size() == 0 的情況下 nullColumnHack 才會起作用，
* nullColumnHack 的作用是插入數(shù)據(jù)時 nullColumnHack 所在列的 value 為 NULL
* Convenience method for replacing a row in the database.
*
* @param table the table in which to replace the row
* @param nullColumnHack optional; may be <code>null</code>.
* SQL doesn't allow inserting a completely empty row without
* naming at least one column name. If your provided <code>initialValues</code> is
* empty, no column names are known and an empty row can't be inserted.
* If not set to null, the <code>nullColumnHack</code> parameter
* provides the name of nullable column name to explicitly insert a NULL into
* in the case where your <code>initialValues</code> is empty.
* @param initialValues this map contains the initial column values for
* the row.
* @return the row ID of the newly inserted row, or -1 if an error occurred
*/
public long replace(String table, String nullColumnHack, ContentValues initialValues) {
try {
// 注意此處為 CONFLICT_REPLACE
return insertWithOnConflict(table, nullColumnHack, initialValues, CONFLICT_REPLACE);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + initialValues, e);
return -1;
}
}
3. 兩種方式都會調(diào)用 insertWithOnConflict()
public long insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm) {
// 增加引用次數(shù)
acquireReference();
try {
StringBuilder sql = new StringBuilder();
sql.append("INSERT");
sql.append(CONFLICT_VALUES[conflictAlgorithm]);
sql.append(" INTO ");
sql.append(table);
sql.append('(');
Object[] bindArgs = null;
int size = (initialValues != null && initialValues.size() > 0) ? initialValues.size() : 0;
if (size > 0) {
bindArgs = new Object[size];
int i = 0;
for (String colName : initialValues.keySet()) {
sql.append((i > 0) ? "," : "");
sql.append(colName);
bindArgs[i++] = initialValues.get(colName);
}
sql.append(')');
sql.append(" VALUES (");
for (i = 0; i < size; i++) {
sql.append((i > 0) ? ",?" : "?");
}
} else {
// 在 initialValues == null 或者 initialValues.size() == 0 的情況下 nullColumnHack 才會起作用
// 設置 nullColumnHack 所在列的值為 NULL
sql.append(nullColumnHack + ") VALUES (NULL");
}
sql.append(')'); // 拼接 sql 語句結(jié)束
/**
* 創(chuàng)建 SQLiteStatement 對象:
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 從連接池獲取一條連接(可能會阻塞當前線程), 若未開啟并發(fā)功能，則連接池中只存在一條主連接
* 3. 增、刪、改獲取的是主連接, 查優(yōu)先獲取非主連接
* 4. 使用獲取的連接 prepare a statement, 只是 prepare, 并不執(zhí)行 sql
* 5. 釋放連接
*/
SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
try {
/**
* 通過 SQLiteStatement 進行數(shù)據(jù)插入操作:
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 通過 SQLiteSession 從連接池獲取主連接, 此處可能會造成線程阻塞
* 3. 通過主連接調(diào)用 native 方法進行數(shù)據(jù)插入
*/
return statement.executeInsert();
} finally {
statement.close();
}
} finally {
// 減少引用次數(shù)
releaseReference();
}
}
二、刪除數(shù)據(jù)操作(D:刪)
1. 調(diào)用 SQLiteDatabase 中的 delete() 方法
public int delete(String table, String whereClause, String[] whereArgs) {
// 增加引用次數(shù)
acquireReference();
try {
/**
* 創(chuàng)建 SQLiteStatement 對象:
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 從連接池獲取主連接(可能會阻塞當前線程)
* 3. 使用獲取的連接 prepare a statement, 只是 prepare, 并不執(zhí)行 sql
* 4. 釋放連接
*/
SQLiteStatement statement = new SQLiteStatement(this, "DELETE FROM " + table + (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
try {
return statement.executeUpdateDelete();
} finally {
statement.close();
}
} finally {
// 減少引用次數(shù)
releaseReference();
}
}
2. 獲取主連接調(diào)用 native 方法執(zhí)行數(shù)據(jù)刪除
public int executeUpdateDelete() {
// 增加引用次數(shù)
acquireReference();
try {
/**
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 獲取主連接. 不開啟并發(fā)功能時，連接池中只有一條主連接(可能會造成線程阻塞)
* 3. 通過主連接調(diào)用 native 方法進行操作
* 4. 操作完成后釋放主連接
*/
return getSession().executeForChangedRowCount(getSql(), getBindArgs(), getConnectionFlags(), null);
} catch (SQLiteDatabaseCorruptException ex) {
onCorruption();
throw ex;
} finally {
// 減少引用次數(shù)
releaseReference();
}
}
三、更新數(shù)據(jù)操作(U:改)
1. 調(diào)用 SQLiteDatabase 中的 update() 方法
public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
// 返回被改動的總行數(shù)
return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
}
2. 獲取主連接調(diào)用 native 方法執(zhí)行數(shù)據(jù)更新
public int updateWithOnConflict(String table, ContentValues values, String whereClause, String[] whereArgs, int conflictAlgorithm) {
if (values == null || values.size() == 0) {
throw new IllegalArgumentException("Empty values");
}
acquireReference();
try {
StringBuilder sql = new StringBuilder(120);
sql.append("UPDATE ");
sql.append(CONFLICT_VALUES[conflictAlgorithm]);
sql.append(table);
sql.append(" SET ");
// move all bind args to one array
int setValuesSize = values.size();
int bindArgsSize = (whereArgs == null) ? setValuesSize : (setValuesSize + whereArgs.length);
Object[] bindArgs = new Object[bindArgsSize];
int i = 0;
for (String colName : values.keySet()) {
sql.append((i > 0) ? "," : "");
sql.append(colName);
bindArgs[i++] = values.get(colName);
sql.append("=?");
}
if (whereArgs != null) {
for (i = setValuesSize; i < bindArgsSize; i++) {
bindArgs[i] = whereArgs[i - setValuesSize];
}
}
if (!TextUtils.isEmpty(whereClause)) {
sql.append(" WHERE ");
sql.append(whereClause);
}
// 拼裝 sql 語句結(jié)束
/**
* 創(chuàng)建 SQLiteStatement 對象:
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 從連接池獲取主連接(可能會阻塞當前線程)
* 3. 使用獲取的連接 prepare a statement, 只是 prepare, 并不執(zhí)行 sql
* 4. 釋放連接
*/
SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
try {
/**
* 1. 獲取當前線程的 SQLiteSession，如果不存在則創(chuàng)建
* 2. 獲取主連接(可能會造成線程阻塞)
* 3. 通過主連接調(diào)用 native 方法進行操作
* 4. 操作完成后釋放主連接
*/
return statement.executeUpdateDelete();
} finally {
statement.close();
}
} finally {
releaseReference();
}
}
四、查詢數(shù)據(jù)庫(R:查)
1. 多個 query() 方法最終都會調(diào)用該 query() 方法
/**
* Query the given URL, returning a {@link Cursor} over the result set.
*
* @param distinct true if you want each row to be unique, false otherwise.
*
* 常規(guī)使用 query() 方法時為 false，用于對某個字段去重
* 如：SELECT DISTINCT name FROM COMPANY; 將對名字進行去重后展示
*
* @param table The table name to compile the query against.
* @param columns A list of which columns to return. Passing null will
* return all columns, which is discouraged to prevent reading
* data from storage that isn't going to be used.
* @param selection A filter declaring which rows to return, formatted as an
* SQL WHERE clause (excluding the WHERE itself). Passing null
* will return all rows for the given table.
* @param selectionArgs You may include ?s in selection, which will be
* replaced by the values from selectionArgs, in order that they
* appear in the selection. The values will be bound as Strings.
* @param groupBy A filter declaring how to group rows, formatted as an SQL
* GROUP BY clause (excluding the GROUP BY itself). Passing null
* will cause the rows to not be grouped.
*
* 指定某一列，對相同字段進行合并，通常用于統(tǒng)計該相同字段另一列的總和
* 如： SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME
* 把具有相同名字的 SALARY 加和后展示
*
* @param having A filter declare which row groups to include in the cursor,
* if row grouping is being used, formatted as an SQL HAVING
* clause (excluding the HAVING itself). Passing null will cause
* all row groups to be included, and is required when row
* grouping is not being used.
*
* 只有使用 groupBy 的情況下才能使用 having，否則會拋出異常
* 使用范例：
* SELECT column1, column2
* FROM table1, table2
* WHERE [ conditions ]
* GROUP BY column1, column2
* HAVING [ conditions ] (FC: having 后面跟的是條件判斷語句)
* ORDER BY column1, column2
* 如：SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2
*
* @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
* (excluding the ORDER BY itself). Passing null will use the
* default sort order, which may be unordered.
* @param limit Limits the number of rows returned by the query,
* formatted as LIMIT clause. Passing null denotes no LIMIT clause.
* @return A {@link Cursor} object, which is positioned before the first entry. Note that
* {@link Cursor}s are not synchronized, see the documentation for more details.
* @see Cursor
*/
public Cursor query(boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit) {
return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
groupBy, having, orderBy, limit, null);
}
2. 拼裝 sql 語句，調(diào)用 rawQueryWithFactory()
public Cursor queryWithFactory(CursorFactory cursorFactory,
boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit, CancellationSignal cancellationSignal) {
acquireReference();
try {
// 拼裝 sql 語句
String sql = SQLiteQueryBuilder.buildQueryString(distinct, table, columns, selection, groupBy, having, orderBy, limit);
return rawQueryWithFactory(cursorFactory, sql, selectionArgs, findEditTable(table), cancellationSignal);
} finally {
releaseReference();
}
}
3. 返回 SQLiteCursor 對象，但并沒有進行真正的查詢
public Cursor rawQueryWithFactory(CursorFactory cursorFactory, String sql, String[] selectionArgs,
String editTable, CancellationSignal cancellationSignal) {
acquireReference();
try {
// 生成 SQLiteDirectCursorDriver 對象，不存在耗時
SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable, cancellationSignal);
// 生成 SQLiteQuery 對象時，會從連接池獲取連接 prepare sql, 隨后釋放(可能存在線程阻塞)
// 返回 SQLiteCursor 對象
return driver.query(cursorFactory != null ? cursorFactory : mCursorFactory, selectionArgs);
} finally {
releaseReference();
}
}
4. SQLiteCursor 以下方法執(zhí)行時，會觸發(fā) SQLiteQuery 對數(shù)據(jù)庫的查詢：
public int getCount() {
if (mCount == NO_COUNT) {
// 觸發(fā)對數(shù)據(jù)庫的查詢
fillWindow(0);
}
return mCount;
}
@Override
public boolean onMove(int oldPosition, int newPosition) {
// Make sure the row at newPosition is present in the window
if (mWindow == null || newPosition < mWindow.getStartPosition() || newPosition >= (mWindow.getStartPosition() + mWindow.getNumRows())) {
// 觸發(fā)對數(shù)據(jù)庫的查詢
fillWindow(newPosition);
}
return true;
}
5. 觸發(fā) SQLiteQuery 對數(shù)據(jù)庫的查詢
/**
* 觸發(fā) SQLiteQuery 對數(shù)據(jù)庫的查詢操作
* @param requiredPos
*/
private void fillWindow(int requiredPos) {
// 如果 mWindow==null,則 new CursorWindow(name, true),否則 mWindow.clear()
clearOrCreateWindow(getDatabase().getPath());
try {
if (mCount == NO_COUNT) {
int startPos = DatabaseUtils.cursorPickFillWindowStartPosition(requiredPos, 0);
// 觸發(fā) SQLiteQuery 對數(shù)據(jù)庫的查詢操作
mCount = mQuery.fillWindow(mWindow, startPos, requiredPos, true);
mCursorWindowCapacity = mWindow.getNumRows();
if (Log.isLoggable(TAG, Log.DEBUG)) {
Log.d(TAG, "received count(*) from native_fill_window: " + mCount);
}
} else {
int startPos = DatabaseUtils.cursorPickFillWindowStartPosition(requiredPos, mCursorWindowCapacity);
// 觸發(fā) SQLiteQuery 對數(shù)據(jù)庫的查詢操作
mQuery.fillWindow(mWindow, startPos, requiredPos, false);
}
} catch (RuntimeException ex) {
// Close the cursor window if the query failed and therefore will
// not produce any results. This helps to avoid accidentally leaking
// the cursor window if the client does not correctly handle exceptions
// and fails to close the cursor.
closeWindow();
throw ex;
}
}
6. 從連接池獲取連接調(diào)用 native 方法查詢數(shù)據(jù)庫
int fillWindow(CursorWindow window, int startPos, int requiredPos, boolean countAllRows) {
acquireReference();
try {
window.acquireReference();
try {
/**
* 1. 獲取當前線程的 SQLiteSession，不存在則創(chuàng)建
* 2. 通過 SQLiteSession 獲取連接
* 3. 調(diào)用連接的 native 方法進行數(shù)據(jù)查詢操作
*/
int numRows = getSession().executeForCursorWindow(getSql(), getBindArgs(),
window, startPos, requiredPos, countAllRows, getConnectionFlags(),
mCancellationSignal);
return numRows;
} catch (SQLiteDatabaseCorruptException ex) {
onCorruption();
throw ex;
} catch (SQLiteException ex) {
Log.e(TAG, "exception: " + ex.getMessage() + "; query: " + getSql());
throw ex;
} finally {
window.releaseReference();
}
} finally {
releaseReference();
}
}
五、關閉數(shù)據(jù)庫，本質(zhì)是關閉連接池
1. 調(diào)用 close() 方法
/**
* 1. 調(diào)用該方法，并不一定會關閉數(shù)據(jù)庫
* 2. 只有在所有引用都被釋放的情況下，才會進行真正的 close 操作
* 3. 多次調(diào)用 close() 方法，會導致有別的引用存在的情況下，數(shù)據(jù)庫被意外關閉
*
* Releases a reference to the object, closing the object if the last reference
* was released.
*
* Calling this method is equivalent to calling {@link #releaseReference}.
*
* @see #releaseReference()
* @see #onAllReferencesReleased()
*/
public void close() {
// 調(diào)用該方法，并不一定會關閉數(shù)據(jù)庫
// 只有在所有引用都被釋放的情況下，才會進行真正的 close 操作
releaseReference();
}
2. 引用次數(shù)自減，若引用次數(shù)歸零則真正執(zhí)行關閉數(shù)據(jù)庫
/**
* Releases a reference to the object, closing the object if the last reference
* was released.
*
* @see #onAllReferencesReleased()
*/
public void releaseReference() {
boolean refCountIsZero = false;
synchronized(this) {
// 引用自減
refCountIsZero = --mReferenceCount == 0;
}
if (refCountIsZero) {
// 所有引用已被全部釋放，或者 close() 被多次調(diào)用導致引用次數(shù)歸零
onAllReferencesReleased();
}
}
protected void onAllReferencesReleased() {
dispose(false);
}
3. 關閉數(shù)據(jù)庫實際上就是關閉連接池
private void dispose(boolean finalized) {
final SQLiteConnectionPool pool;
synchronized (mLock) {
if (mCloseGuardLocked != null) {
if (finalized) {
mCloseGuardLocked.warnIfOpen();
}
mCloseGuardLocked.close();
}
pool = mConnectionPoolLocked;
// 把成員變量"連接池"置空
// 外部對數(shù)據(jù)庫是否已關閉的判斷，就是依據(jù) mConnectionPoolLocked 是否為 null
mConnectionPoolLocked = null;
}
if (!finalized) {
synchronized (sActiveDatabases) {
sActiveDatabases.remove(this);
}
if (pool != null) {
// 關閉連接池
// 關閉數(shù)據(jù)庫實際上就是關閉連接池
pool.close();
}
}
}
六、關閉連接池
1. 調(diào)用 close()
/**
* 1. 連接池關閉后，將不會再接收獲取連接的請求
* 2. 可用的連接會被立即關閉
* 3. 使用中的連接釋放到連接池后會被關閉
*
* Closes the connection pool.
* <p>
* When the connection pool is closed, it will refuse all further requests
* to acquire connections. All connections that are currently available in
* the pool are closed immediately. Any connections that are still in use
* will be closed as soon as they are returned to the pool.
* </p>
*
* @throws IllegalStateException if the pool has been closed.
*/
public void close() {
dispose(false);
}
2. 關閉所有空閑連接
private void dispose(boolean finalized) {
if (mCloseGuard != null) {
if (finalized) {
mCloseGuard.warnIfOpen();
}
mCloseGuard.close();
}
if (!finalized) {
// Close all connections. We don't need (or want) to do this
// when finalized because we don't know what state the connections
// themselves will be in. The finalizer is really just here for CloseGuard.
// The connections will take care of themselves when their own finalizers run.
synchronized (mLock) {
throwIfClosedLocked();
mIsOpen = false;
// 關閉所有可用連接（空閑連接）
closeAvailableConnectionsAndLogExceptionsLocked();
final int pendingCount = mAcquiredConnections.size();
if (pendingCount != 0) {
// 當前有使用中的連接，打印提示日志
Log.i(TAG, "The connection pool for " + mConfiguration.label
+ " has been closed but there are still "
+ pendingCount + " connections in use. They will be closed "
+ "as they are released back to the pool.");
}
wakeConnectionWaitersLocked();
}
}
}
3. 使用中的連接回歸連接池后被關閉
public void releaseConnection(SQLiteConnection connection) {
synchronized (mLock) {
SQLiteConnectionPool.AcquiredConnectionStatus status = mAcquiredConnections.remove(connection);
if (status == null) {
throw new IllegalStateException("Cannot perform this operation "
+ "because the specified connection was not acquired "
+ "from this pool or has already been released.");
}
if (!mIsOpen) {
// 連接池已被關閉，關閉當前連接
closeConnectionAndLogExceptionsLocked(connection);
} else if (connection.isPrimaryConnection()) {
// 主連接的情況
if (recycleConnectionLocked(connection, status)) {
assert mAvailablePrimaryConnection == null;
// 為 mAvailablePrimaryConnection 賦值，主連接被占用后該值為 null
mAvailablePrimaryConnection = connection;
}
wakeConnectionWaitersLocked();
} else if (mAvailableNonPrimaryConnections.size() >= mMaxConnectionPoolSize - 1) {
// 超出了最大連接條數(shù)的限制，關閉該條連接;
// 判斷條件中減 1, 是因為還存在一條主連接
closeConnectionAndLogExceptionsLocked(connection);
} else {
if (recycleConnectionLocked(connection, status)) {
mAvailableNonPrimaryConnections.add(connection);
}
wakeConnectionWaitersLocked();
}
}
}
