Android 数据库操作之：SQLiteOpenHelper 与 SQLiteDatabase（整理）

一、基本使用

1、SQLiteOpenHelper

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;

public class NumbersDBHelper extends SQLiteOpenHelper {

    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "dbTest.db";

    interface TABLE {
        String NAME = "Numbers";
        String ID = "id";
        String NUMNAME = "numName";
        String DESCRIPTION = "description";
    }

    NumbersDBHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    /**
     * 负责数据库的创建和初始化，只在第一次生成数据库的时候回调
     * <p>
     * TODO 只有第一次真正使用数据库时才会调用：
     * android.database.sqlite.SQLiteOpenHelper#getWritableDatabase()
     * android.database.sqlite.SQLiteOpenHelper#getReadableDatabase()
     * <p>
     * 文件路径：
     * data/data/[应用包名xxx.xxx.xxx]/databases/dbTest.db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = String.format(
                "create table if not exists %s (%s integer primary key, %s text, %s text)",
                TABLE.NAME, TABLE.ID, TABLE.NUMNAME, TABLE.DESCRIPTION);
        db.execSQL(sql);
    }

    /**
     * 数据库升级的时候才会回调
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 每次成功打开数据库后首先被执行
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }
}

2、通过 Dao 来规范数据的操作（SQLiteDatabase）

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import androidx.annotation.Nullable;
import java.util.List;

public class NumbersDao extends BaseDao<NumbersDBHelper> {

    private static volatile NumbersDao mNumbersDao;

    static NumbersDao getInstance(@Nullable Context context) {
        if (mNumbersDao == null) {
            synchronized (NumbersDao.class) {
                if (mNumbersDao == null) {
                    mNumbersDao = new NumbersDao(context);
                }
            }
        }
        return mNumbersDao;
    }

    private NumbersDao(@Nullable Context context) {
        super(context);
    }

    @Override
    protected NumbersDBHelper initDbHelper(Context context) {
        return new NumbersDBHelper(context);
    }

    public boolean insert(NumberBean number) {
        boolean isSuccess = false;
        SQLiteDatabase writableDatabase = getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(NumbersDBHelper.TABLE.NUMNAME, number.getNumName());
            values.put(NumbersDBHelper.TABLE.DESCRIPTION, number.getDescription());
            writableDatabase.insertOrThrow(NumbersDBHelper.TABLE.NAME, null, values);
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeWritableDatabase(writableDatabase);
        return isSuccess;
    }

    public boolean insert(List<NumberBean> numbers) {
        boolean isSuccess = false;
        SQLiteDatabase writableDatabase = getWritableDatabase();
        // TODO 手动设置开始事务
        writableDatabase.beginTransaction();
        try {
            for (NumberBean number : numbers) {
                ContentValues values = new ContentValues();
                values.put(NumbersDBHelper.TABLE.NUMNAME, number.getNumName());
                values.put(NumbersDBHelper.TABLE.DESCRIPTION, number.getDescription());
                writableDatabase.insertOrThrow(NumbersDBHelper.TABLE.NAME, null, values);
            }
            // TODO 设置事务处理成功，不设置会自动回滚不提交
            writableDatabase.setTransactionSuccessful();
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // TODO 只有执行了endTransaction方法，事务操作才会真正提交到数据库
            writableDatabase.endTransaction();
            // 关闭数据库连接
            // writableDatabase.close();
            closeWritableDatabase(writableDatabase);
        }
        return isSuccess;
    }

    public boolean deleteById(int id) {
        boolean isSuccess = false;
        SQLiteDatabase writableDatabase = getWritableDatabase();
        try {
            writableDatabase.delete(
                    NumbersDBHelper.TABLE.NAME,// 表名
                    String.format("%s=%s", NumbersDBHelper.TABLE.ID, id),// 条件
                    null // 上面条件的占位符（ You may include ?s in the where clause）（可以，也可以不用）
            );
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        closeWritableDatabase(writableDatabase);
        return isSuccess;
    }

    public boolean updateById(int id, NumberBean number) {
        if (number == null) {
            return false;
        }
        boolean isSuccess = false;
        SQLiteDatabase writableDatabase = getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(NumbersDBHelper.TABLE.NUMNAME, number.getNumName());
            values.put(NumbersDBHelper.TABLE.DESCRIPTION, number.getDescription());
            writableDatabase.update(
                    NumbersDBHelper.TABLE.NAME,// 表名
                    values,
                    String.format("%s=%s", NumbersDBHelper.TABLE.ID, id),// 条件
                    null // 上面条件的占位符（ You may include ?s in the where clause）（可以，也可以不用）
            );
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        closeWritableDatabase(writableDatabase);
        return isSuccess;
    }

    public NumberBean queryById(int id) {
        SQLiteDatabase readableDatabase = getReadableDatabase();
        NumberBean number = null;
        try {
            // SELECT * FROM Numbers WHERE id=9
            Cursor cursor = readableDatabase.query(
                    NumbersDBHelper.TABLE.NAME,// 表名
                    null, // 返回的字段，null表示返回全部字段
                    String.format("%s=%s", NumbersDBHelper.TABLE.ID, id), // 查询条件
                    null, // 查询条件若带有问号，这里既是问号所包括数据数组，没有即为null
                    null, // 分组的字段
                    null, // having
                    null // 排序方式
            );
            number = new NumberBean();
            // Move the cursor to the first row.This method will return false if the cursor is empty.
            if (cursor.moveToFirst()) {
                number.setId(cursor.getInt(cursor.getColumnIndex(NumbersDBHelper.TABLE.ID)));
                number.setNumName(cursor.getString(cursor.getColumnIndex(NumbersDBHelper.TABLE.NUMNAME)));
                number.setDescription(cursor.getString(cursor.getColumnIndex(NumbersDBHelper.TABLE.DESCRIPTION)));
            }
            // TODO 关闭cursor，回收资源
            cursor.close();
            closeReadableDatabase(readableDatabase);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return number;
    }
}

Dao 基类

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;

abstract class BaseDao<T extends SQLiteOpenHelper> {

    private final T mDbHelper;
    private int mOpenCounts;
    private SQLiteDatabase mWritableDatabase;

    BaseDao(@Nullable Context context) {
        this.mDbHelper = initDbHelper(context);
    }

    abstract T initDbHelper(Context context);

    synchronized SQLiteDatabase getWritableDatabase() {
        if (mOpenCounts == 0) {
            mWritableDatabase = mDbHelper.getWritableDatabase();
        }
        mOpenCounts++;
        return mWritableDatabase;
    }

    synchronized void closeWritableDatabase(SQLiteDatabase db) {
        mOpenCounts--;
        if (mOpenCounts <= 0) {
            db.close();
        }
    }

    SQLiteDatabase getReadableDatabase() {
        return mDbHelper.getReadableDatabase();
    }

    void closeReadableDatabase(SQLiteDatabase db) {
        db.close();
    }
}

3、其他

Manager 规范使用入口

import android.content.Context;
import androidx.annotation.Nullable;

public class DaoManager {

    public static NumbersDao getNumbersDao(@Nullable Context context) {
        return NumbersDao.getInstance(context.getApplicationContext());
    }

}

Bean 类

public class NumberBean {

    private int id;
    private String numName;
    private String description;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getNumName() {
        return numName;
    }

    public void setNumName(String numName) {
        this.numName = numName;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

二、使用中要注意的问题

1、SQLiteDatabaseLockedException: database is locked

SQLite 是文件级别的锁，多个线程可以同时读，但是同时只能有一个线程写。Android 提供了 SqliteOpenHelper 类来操作数据库，一个 SqliteOpenHelper 绑定一个指定的数据库，可以通过 getWritableDatabase 或者 getReadableDatabase 拿到 SQLiteDatabase 对象，即为数据库连接。当多线程生成多个 SqliteOpenHelper 类的实例，且同时对数据库进行操作时（读/写），就可能出现这个情况。下面模拟一下：

    private void asyncInsert() {
        List<Thread> threads = new ArrayList<>();
        // TODO 因为测试机多核，所以模拟时线程数要超过CPU多核的数量
        for (int i = 0; i < 20; i++) {
            Thread thread = new Thread(new Runnable() {
                @Override
                public void run() {
                    ArrayList<NumberBean> numbers = new ArrayList<>();
                    for (int i = 0; i < 10000; i++) {
                        NumberBean number = new NumberBean();
                        number.setNumName("张" + i);
                        number.setDescription("zhang" + i);
                        numbers.add(number);
                    }
                    DaoManager.getNumbersDao(SQLiteActivity.this).insert(numbers);
                }
            });
            threads.add(thread);
        }
        for (Thread thread : threads) {
            thread.start();
        }
    }

如果数据操作较快，很难重现这个问题（据说读/写操作的阻塞超过5秒钟才会报这个问题），所以上面通过超过 CPU 核数的线程数及较大的数据插入（其实也不大）来达到想要的效果，如下：

E/AndroidRuntime: FATAL EXCEPTION: Thread-24
Process: com.zzq.demo, PID: 17948
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (Sqlite code 5 SQLITE_BUSY), (OS error - 2:No such file or directory)
... ...

这种情况只要保持只有一个 SqliteOpenHelper 对应的数据库连接就行，我的 demo 是通过 dao 类来操作 SqliteOpenHelper，dao 的单例同时实现 SqliteOpenHelper 的单例。

2、attempt to re-open an already-closed object 和 Cannot perform this operation because the connection pool has been closed

同一个 SQLiteOpenHelper 的 getWritableDatabase() 返回同一个 SQLiteDatabase 对象，当多线程情况下使用的是同一个 SqliteOpenHelper 来打开数据库连接时，某个线程在完成数据库操作后关闭数据库连接（database.close()）而另一个线程还在进行数据库的操作，就会出现这个问题（关闭数据库连接是为了回收资源）：线程 A 打开数据库连接使用数据库，这时 CPU 片段分配给线程 B，线程 A 挂起；线程 B 进入执行获取数据库连接时没有问题，线程 B 进行操作，在片段时间内数据操作完成，最后关闭数据库连接（database.close()）线程 B 执行结束；切换到线程 A 执行，这时线程 A 插入数据或者其他操作时就会出现这个情况：

    public boolean insert(List<NumberBean> numbers) {
        ... ...
        SQLiteDatabase writableDatabase = mNumbersDBHelper.getWritableDatabase();
        ... ...
        try {
            ... ...
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ... ...
            // 关闭数据库连接
            writableDatabase.close();
        }
        return isSuccess;
    }

E/AndroidRuntime: FATAL EXCEPTION: Thread-5
Process: com.zzq.demo, PID: 20599
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/com.zzq.demo/databases/dbTest.db
... ...

E/AndroidRuntime: FATAL EXCEPTION: Thread-6
Process: com.zzq.demo, PID: 20599
java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
... ...

通过记录当前的数据库连接打开数量，来决定是否执行关闭操作：

abstract class BaseDao<T extends SQLiteOpenHelper> {

    private final T mDbHelper;
    private int mOpenCounts;
    private SQLiteDatabase mWritableDatabase;

    ... ...

    synchronized SQLiteDatabase getWritableDatabase() {
        if (mOpenCounts == 0) {
            mWritableDatabase = mDbHelper.getWritableDatabase();
        }
        mOpenCounts++;
        return mWritableDatabase;
    }

    synchronized void closeWritableDatabase(SQLiteDatabase db) {
        mOpenCounts--;
        if (mOpenCounts <= 0) {
            db.close();
        }
    }

    ... ...
}

    public boolean insert(List<NumberBean> numbers) {
        ... ...
        SQLiteDatabase writableDatabase = getWritableDatabase();
        ... ...
        try {
            ... ...
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ... ...
            // 关闭数据库连接
            // writableDatabase.close();
            closeWritableDatabase(writableDatabase);
        }
        return isSuccess;
    }

3、事务

事务是原子性的，要么执行成功，要么执行失败，执行一半失败后会回滚，这样就保证了数据的完整性。
当需要向 SQLite 中插入大批量数据时，单独的使用添加方法会导致应用响应缓慢，因为 SQLite 插入数据的时候默认一条语句就是一个事务，有多少条数据就有多少次磁盘操作，会出现频繁调用数据库的情况，同时，为了保证数据的完整性，避免出现数据缺失等情况，使用事务就显得有极大的好处：采用事务后，批量操作会变成一次性提交操作，速度提升极为明显，且数据的完整性会得到保证。

    public boolean insert(List<NumberBean> numbers) {
        ... ...
        SQLiteDatabase writableDatabase = getWritableDatabase();
        // TODO 手动设置开始事务
        writableDatabase.beginTransaction();
        try {
            ... ...
            // TODO 设置事务处理成功，不设置会自动回滚不提交
            writableDatabase.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // TODO 只有执行了endTransaction方法，事务操作才会真正提交到数据库
            writableDatabase.endTransaction();
            ... ...
        }
        return isSuccess;
    }

使用 SQLiteDatabase 的 beginTransaction() 方法可以开启一个事务，程序执行到 endTransaction() 方法时会检查事务的标志是否为成功，如果程序执行到 endTransaction() 之前调用了 setTransactionSuccessful() 方法设置事务的标志为成功则提交事务，如果没有调用 setTransactionSuccessful() 方法则回滚事务。

（1）插入1000个数据：
① 不采用事务且每次使用完马上 close WritableDatabase：大约 13s
② 不采用事务且每次使用完也不 close WritableDatabase：大约 2s
③ 采用事务（关不关闭数据库连接都无所谓）：大约 200ms

    public void transaction() {
        ArrayList<NumberBean> numbers = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            NumberBean number = new NumberBean();
            number.setNumName("张" + i);
            number.setDescription("zhang" + i);
            numbers.add(number);
        }
        for (NumberBean number : numbers) {
            DaoManager.getNumbersDao(this).insert(number);
        }
    }

（2）插入10000个数据：
① 不采用事务且每次使用完马上 close WritableDatabase：大约 2m18s
② 不采用事务且每次使用完也不 close WritableDatabase：大约 13s
③ 采用事务（关不关闭数据库连接都无所谓）：大约 600ms

    public void transaction() {
        ArrayList<NumberBean> numbers = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            NumberBean number = new NumberBean();
            number.setNumName("张" + i);
            number.setDescription("zhang" + i);
            numbers.add(number);
        }
        for (NumberBean number : numbers) {
            DaoManager.getNumbersDao(this).insert(number);
        }
    }

三、其他

1、通过 Device File Explorer 或者 DDMS 导出的 db 文件打开后，数据并未发生变化：

《[随手记系列]SQLite数据库.db | .db-wal | .db-shw到底是个啥,为什么我导出.db里面没数据呢?》

SQLite3.7.0 新特性：预写日志(WAL)

WAL机制的原理是：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。

如果我想要看数据咋办?
当 .db-shw 和 .db-wal 缓存到一定的大小后，就会写入到 .db 里面去。

如果我现在就要看数据咋办?
打开 Device File Explorer，把 data/data/[应用包名]/databases 文件夹中 3个文件全部 Save As 出来，打开 .db，就可以看到所有数据了。

---

参考文章：
1、《记录：Android SQLite的使用》
2、《Android-SQLite常见问题总结》
3、《android sqlite数据库并发问题的详细描述和解决方案》
4、《Android 中 SQLite 性能优化》
5、《Android数据库Sqlite的基本用法及升级策略》