Sqlite 源码分析 -- SQLiteDatabase CRUD 操作 (API 24)

注意事项:

如果 SQLiteOpenHelper 使用的是单例，SQLiteDatabase 对 CRUD 操作都是从同一个连接池中获取连接. 默认情况下, 连接池中只有一条主连接, 所以同一时间只能进行一项操作，多线程读写几乎是无用功；
enableWriteAheadLogging() 方法可以使得多线程并发查询可行，但默认没有开启该功能, 该方法会根据配置在连接池中创建多条连接；
android sqlite 不支持多 SQLiteOpenHelper 实例、多线程并发写入操作，会抛出异常“database is locked”;
插入单条数据不需要开启事务;
全局引用一个 SQLiteDatabase 时，是否存在不主动调用 close() 但被动 close() 的情况？
SQLiteCursor 的获取与触发对数据库的真正查询是分离的，获取 SQLiteCursor 后、查询数据库前数据库的状态发生变化(如“被关闭”)，是否会出现问题？（真正查询时获取不到连接）
执行 sql 语句的正确方式：

db.beginTransaction();
try {
  ...
  // 注意该语句放在 try 语句块的最后，表明最终的操作成功
  db.setTransactionSuccessful();
} finally {
  // 注意该语句放在 finally 语句块中，确定进行 roll back 或 commit
  db.endTransaction();
}

一、添加数据操作(C:增)

1. 第一种添加数据方式：调用 SQLiteDatabase 中的 insert() 方法

/**
 * 在 values == null 或者 values.size() == 0 的情况下 nullColumnHack 才会起作用，
 * nullColumnHack 的作用是插入数据时 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. 第二种添加数据方式：调用 SQLiteDatabase 中的 replace() 方法

/**
 * 在 initialValues == null 或者 initialValues.size() == 0 的情况下 nullColumnHack 才会起作用，
 * nullColumnHack 的作用是插入数据时 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. 两种方式都会调用 insertWithOnConflict()

public long insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm) {
    // 增加引用次数
    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 语句结束

        /**
         * 创建 SQLiteStatement 对象:
         * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
         * 2. 从连接池获取一条连接(可能会阻塞当前线程), 若未开启并发功能，则连接池中只存在一条主连接
         * 3. 增、删、改获取的是主连接, 查优先获取非主连接
         * 4. 使用获取的连接 prepare a statement, 只是 prepare, 并不执行 sql
         * 5. 释放连接
         */
        SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
        try {
            /**
             * 通过 SQLiteStatement 进行数据插入操作:
             * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
             * 2. 通过 SQLiteSession 从连接池获取主连接, 此处可能会造成线程阻塞
             * 3. 通过主连接调用 native 方法进行数据插入
             */
            return statement.executeInsert();
        } finally {
            statement.close();
        }
    } finally {
        // 减少引用次数
        releaseReference();
    }
}

二、删除数据操作(D:删)

1. 调用 SQLiteDatabase 中的 delete() 方法

public int delete(String table, String whereClause, String[] whereArgs) {
    // 增加引用次数
    acquireReference();
    try {
        /**
         * 创建 SQLiteStatement 对象:
         * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
         * 2. 从连接池获取主连接(可能会阻塞当前线程)
         * 3. 使用获取的连接 prepare a statement, 只是 prepare, 并不执行 sql
         * 4. 释放连接
         */
        SQLiteStatement statement =  new SQLiteStatement(this, "DELETE FROM " + table + (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
        try {
            return statement.executeUpdateDelete();
        } finally {
            statement.close();
        }
    } finally {
        // 减少引用次数
        releaseReference();
    }
}

2. 获取主连接调用 native 方法执行数据删除

public int executeUpdateDelete() {
    // 增加引用次数
    acquireReference();
    try {
        /**
         * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
         * 2. 获取主连接. 不开启并发功能时，连接池中只有一条主连接(可能会造成线程阻塞)
         * 3. 通过主连接调用 native 方法进行操作
         * 4. 操作完成后释放主连接
         */
        return getSession().executeForChangedRowCount(getSql(), getBindArgs(), getConnectionFlags(), null);
    } catch (SQLiteDatabaseCorruptException ex) {
        onCorruption();
        throw ex;
    } finally {
        // 减少引用次数
        releaseReference();
    }
}

三、更新数据操作(U:改)

1. 调用 SQLiteDatabase 中的 update() 方法

public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
    // 返回被改动的总行数
    return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
}

2. 获取主连接调用 native 方法执行数据更新

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 语句结束

        /**
         * 创建 SQLiteStatement 对象:
         * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
         * 2. 从连接池获取主连接(可能会阻塞当前线程)
         * 3. 使用获取的连接 prepare a statement, 只是 prepare, 并不执行 sql
         * 4. 释放连接
         */
        SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
        try {
            /**
             * 1. 获取当前线程的 SQLiteSession，如果不存在则创建
             * 2. 获取主连接(可能会造成线程阻塞)
             * 3. 通过主连接调用 native 方法进行操作
             * 4. 操作完成后释放主连接
             */
            return statement.executeUpdateDelete();
        } finally {
            statement.close();
        }
    } finally {
        releaseReference();
    }
}

四、查询数据库(R:查)

1. 多个 query() 方法最终都会调用该 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.
 *
 *                 常规使用 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.
 *
 *            指定某一列，对相同字段进行合并，通常用于统计该相同字段另一列的总和
 *            如： 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 语句，调用 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 以下方法执行时，会触发 SQLiteQuery 对数据库的查询：

public int getCount() {
    if (mCount == NO_COUNT) {
        // 触发对数据库的查询
        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())) {
        // 触发对数据库的查询
        fillWindow(newPosition);
    }

    return true;
}

5. 触发 SQLiteQuery 对数据库的查询

 /**
     * 触发 SQLiteQuery 对数据库的查询操作
     * @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);
                // 触发 SQLiteQuery 对数据库的查询操作
                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);
                // 触发 SQLiteQuery 对数据库的查询操作
                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. 从连接池获取连接调用 native 方法查询数据库

int fillWindow(CursorWindow window, int startPos, int requiredPos, boolean countAllRows) {
    acquireReference();
    try {
        window.acquireReference();
        try {
            /**
             * 1. 获取当前线程的 SQLiteSession，不存在则创建
             * 2. 通过 SQLiteSession 获取连接
             * 3. 调用连接的 native 方法进行数据查询操作
             */
            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();
    }
}

五、关闭数据库，本质是关闭连接池

1. 调用 close() 方法

/**
 * 1. 调用该方法，并不一定会关闭数据库
 * 2. 只有在所有引用都被释放的情况下，才会进行真正的 close 操作
 * 3. 多次调用 close() 方法，会导致有别的引用存在的情况下，数据库被意外关闭
 * 
 * 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() {
    // 调用该方法，并不一定会关闭数据库
    // 只有在所有引用都被释放的情况下，才会进行真正的 close 操作
    releaseReference();
}

2. 引用次数自减，若引用次数归零则真正执行关闭数据库

/**
 * 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() 被多次调用导致引用次数归零
        onAllReferencesReleased();
    }
}

protected void onAllReferencesReleased() {
    dispose(false);
}

3. 关闭数据库实际上就是关闭连接池

private void dispose(boolean finalized) {
    final SQLiteConnectionPool pool;
    synchronized (mLock) {
        if (mCloseGuardLocked != null) {
            if (finalized) {
                mCloseGuardLocked.warnIfOpen();
            }
            mCloseGuardLocked.close();
        }

        pool = mConnectionPoolLocked;
        // 把成员变量"连接池"置空
        // 外部对数据库是否已关闭的判断，就是依据 mConnectionPoolLocked 是否为 null
        mConnectionPoolLocked = null;
    }

    if (!finalized) {
        synchronized (sActiveDatabases) {
            sActiveDatabases.remove(this);
        }

        if (pool != null) {
            // 关闭连接池
            // 关闭数据库实际上就是关闭连接池
            pool.close();
        }
    }
}

六、关闭连接池

1. 调用 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) {
            // 超出了最大连接条数的限制，关闭该条连接;
            // 判断条件中减 1, 是因为还存在一条主连接
            closeConnectionAndLogExceptionsLocked(connection);
        } else {
            if (recycleConnectionLocked(connection, status)) {
                mAvailableNonPrimaryConnections.add(connection);
            }
            wakeConnectionWaitersLocked();
        }
    }
}