本文为学习小记，有错的请指正
关于sqlite
开源的，小型的，可嵌入的，关系型数据库
效率高
程序驱动
无数据类型：对字段类型不做检查
事务操作
基本了解
- 数据类型
Integer varchar float double char text ~
- 增删改查
//1：创建表
create table tableName(fieldName dataType 约束, fieldName dataType 约束, ...)
//例：
create table person(_id Integer primary key, name varchar(10), age Integer not null)
//2：删除表
drop table tableName
//例:
drop table person
//3：插入数据
insert into tableName(field, field) values(value, value)
//例:
insert into person(_id, age) values(1, 20)
insert into person values(2, "name", 30)
//4：修改数据
update tableName set field = newValue where 条件
//例：
update person set name="ls" where _id = 1
//5：删除数据
delete from tableName where 条件
//例：
delete from person where _id = 2
//6：查询数据
select field from tableName where 条件 group by 分组字段 having 筛选条件 order by 排序字段
//例：
select * from person
select _id, name from person
select * from person where _id = 1
select * from person where _id<>1 //不等于1
select * from person where _id=1 and age>18
select * from person where name like "%name%"
select * from person where name is null
select * from person where age between 10 and 20
select * from person where age>18 order by _id
select * from person limit ?,? // 第一条index，每页数据量
...
基础开发
数据库
系统提供SqliteOpenHelper用于打开或创建db，需要继承该类实现自定义的helper
getReadableDataBase, getWritableDataBase 都可以用来打开或创建db，该db系统默认指定存储路径：data/data/pakage/database
源码逻辑：getReadableDatabase()和getWriteableDatabase()都是调用getDatabaseLocked(boolean writeable) 方法，传不同的参数
getReadableDatabase() 会获取用于操作SQLiteDatabase的实例。
getReadableDatabase()会先以读写方式打开数据库，若数据库磁盘空间满了，打开失败，会继续尝试以只读方式打开。若磁盘空间有了，会关闭只读数据库对象，返回可读写数据库对象。
getWriteableDatabase()也是会以读写方式打开数据库，如果磁盘满了，会抛异常，不会返回数据库对象。
其实就是getReadableDatabase()会在抛异常的时候以只读模式打开数据库。而getWritableDatabase()不会
helper的onCreate回调db，可用于创建数据表等
也可以打卡指定路径的db：
SQLiteDatabase.openDatabase("path", null, SQLiteDatabase.OPEN_READWRITE)
sql语句
熟能生巧，细心高效
api
db调用系统api，增删改查等，内部是用代码拼接的sql语句，原理一样
api参数：ContentValues 是 HashMap类型，防sql注入，，但是要注意ContentValues扩容问题
查询相关
查询结果是cursor，相当于是查询结果信息的表，需要特定解析转化为bean对应到业务中
SimpleCursorAdapter适配器
针对业务简单的，信息字段不多的内容，可以使用SimpleCursorAdapter适配器，，，跟SimpleAdapter同理，，，就是把cursor数据展示到ui上
注意：使用SimpleCursorAdapter，表中必须有个名称叫"_id"的主键列
CursorAdapter
这个就灵活一点，自定义adapter，可以拿到item_view，并bindView，，跟一般的自定义adapter差不多了
事务
db.execSQL()执行sql语句底层也是基于事务的，所以，批量操作时候，频繁的开关事务，就显得比较耗费性能了，把批量操作放到一个事务中
...
//1：开启事务
db.beginTransaction()
//2：批量操作
注意把处理放到开启事务和提交事务之间就行
//3：提交当前事务
db.setTransactionSuccessful()
//4：关闭事务
db.endTransaction()
...
有种检测方法：在批量处理前后打印起止时间，比较一下加事务前后的时间，事务方式的批量处理，用时明显少很多
数据库升级
原理：
创建db时候，入参version，系统会检查数据库文件中存储的版本号，如果比原版本号大，就会调用onUpgrade回调，在这里进行数据库升级操作
场景：
version_1 》version_2
version_1 》version_3
数据库升级就这两种情况，一种是版本号临近的，一种是版本号跨级的。升级的时候，建议方案是临级别逐个升级，如：version_1 》version_3，循环处理从version_1升级到version_2执行的sql语句，然后处理version_2到version_3执行的sql语句
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
// 历次升级操作都需执行，比如oldVersion为2，newVersion为5，则需将version为3、4、5时的改动都执行一遍
for(int i = 1; i + oldVersion <= newVersion; i++) {
updateOnVersion(db, i + oldVersion);
}
} catch (Exception e) {
Logger.saveException(context, e, LogLevelEnum.ERROR_LEVEL);
}
}
private synchronized void updateOnVersion(SQLiteDatabase db, int version) {
try {
switch (version) {
case 2:
sqlVersion2(db);
break;
default:
break;
}
} catch (Exception e) {
Logger.saveException(context, e);
}
}
具体升级操作：
数据库升级一般也就下面几种情况：
- 增加表
就是普通的create table 语句
- 删除表
execSQL(db, "DROP TABLE IF EXISTS " + tempTableName, null);
- 表增加字段
db.execSQL("alter table TableName add column field")
表增加字段是在表末尾加字段，没办法指定任意位置，当然位置的意义也不大
注意：sql语句不能同时新增多个字段，只能单次新增一个，所以，可以结合事务批量执行上面的sql语句
- 表修改字段
注意：sqlite是不支持修改字段的，本着只增不减的原则，要么增加字段，要么重建表然后迁移数据
- 表删除字段
alter table TableName drop column field
新建表迁移数据
针对以上手段已经无法满足业务改动需求了，只能新建表了，但是还想要原数据，就需要迁移数据：
-- 把原表改成另外一个名字作为暂存表
ALTER TABLE old_table_name RENAME TO temp_table_name;
-- 如果需要，可以删除原表的索引
DROP INDEX ix_name;
-- 用原表的名字创建新表
CREATE TABLE old_table_name ( field_name INTEGER PRIMARY KEY AUTOINCREMENT , other_field_name text notnull);
-- 如果需要，可以创建新表的索引
CREATE INDEX ix_name ON old_table_name(field_name);
-- 将暂存表数据写入到新表，很方便的是不需要去理会自动增长的 ID
INSERT INTO old_table_name SELECT * FROM temp_table_name
-- 删除暂存表
DROP TABLE temp_table_name;
//~~~~~~~~~~~~~~~~divide line~~~~~~~~~~~~~~~~~
protected void upgradeTables(SQLiteDatabase db, String tableName, String columns)
{
try
{
db.beginTransaction();
// 1, 将原表先重命名为临时表名
String tempTableName = tableName + "_temp";
String sql = "ALTER TABLE " + tableName +" RENAME TO " + tempTableName;
execSQL(db, sql, null);
// 2, 用原名创建新表，，（私有方法）
onCreateTable(db);
// 3, 迁移数据
sql = "INSERT INTO " + tableName +
" (" + columns + ") " +
" SELECT " + columns + " FROM " + tempTableName;
execSQL(db, sql, null);
// 4, 删除临时表
execSQL(db, "DROP TABLE IF EXISTS " + tempTableName, null);
db.setTransactionSuccessful();
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
db.endTransaction();
}
}
数据库加密
需求：对于root的android手机，app下的db是不安全的，网上资料大多就是两种方案，第一种对要加密的表内容加密，一点都不优雅。。第二种，SQLCipher，全网好像也就这一种开源免费的加密方案。。第三种：SSE(SQLite Encryption Extension)，网上说微信也用的SQLCipher，不过不重要。SQLCipher够香
SQLCipher
- 加密性能高、开销小，只要5-15%的开销用于加密
- 完全做到数据库100%加密
- 采用良好的加密方式（CBC加密模式: 学习文章）
- 使用方便，做到应用级别加密
- 采用OpenSSL加密库提供的算法
使用
SQLCipher的api是基于SQLite的，所以使用跟直接使用SQLite很类似，，，注意几点：
- 包名
就是原来使用SQLite的时候引入的包，都换为net.sqlcipher.~即可
android.database.Cursor 为 net.sqlcipher.Cursor
android.database.sqlite.SQLiteDatabase 为 net.sqlcipher.database.SQLiteDatabase
android.database.SQLiteOpenHelper 为 net.sqlcipher.database.SQLiteOpenHelper.csdn.net/qq_36699930/article/details/100744874
- 在自定义SQLiteOpenHelper的构造方法中，加载SQLCipher需要的so库：
SQLiteDatabase.loadLibs(context)
- 创建db时，需要提供加密的秘钥
//获取写数据库
SQLiteDatabase db = dbHelper.getWritableDatabase("your_pwd");
//获取可读数据库
SQLiteDatabase db = dbHelper.getReadableDatabase("your_pwd");
数据库多线程方案
学习文章(原文链接：https://blog.csdn.net/u010205141/article/details/44182461)
关于sqlite多线程操作，主要有两类问题，如下，，
SQLite是文件级别的锁，数据库通过数据库级上的独占性和共享锁来实现独立事务处理
多线程访问数据库，需要区分是数据库是单连接还是多连接，意思就是SqliteOpenHelper是单例的还是每个线程初始化一个实例（也即是一个helper实例表示一个数据库连接）
- 单连接多线程并发写
数据库写操作，是有独占锁的，所以多线程写操作，也是挨个执行的。。
- 单连接多线程并发读
同写差不多
- 多连接多线程并发读
数据库读操作，也是有独占锁的，所以，要实现多线程并发读，得使用多连接
- 多连接多线程并发写
使用多个SQLiteDatabase对象同时插入，会绕过数据库同步锁，报异常：database is locked
- 多线程读写
似乎这才是想要的多线程数据库操作，能一边读一边写，由上面几种情况也可知道，实现多线程读写，写操作需要是单连接，读操作需要是多连接。但是依然异常：database is locked
android 11后，数据库可以设置预写操作，enableWriteAheadLogging (源码注释建议用打开数据库的参数配置ENABLE_WRITE_AHEAD_LOGGING，效率更高)，打开该设置，以实现单连接写和多连接读同时操作。下面这段话是源码注释解释的：
当启用了写前日志记录(通过调用此方法)时，写操作发生在一个单独的日志文件中，这允许并发地进行读操作。当写操作正在进行时，其他线程上的读取器将感知到数据库的状态与写操作开始前一样。当写入完成时，其他线程上的读取器就会感知到数据库的新状态
可以看到，该方案依然不是同事读写，只是把读写分开，写完，合并，才能读到新写的内容
attempt to re-open an already-closed object
该问题跟上面说的单连接多线程访问不是一回事儿：
SQLiteOpenHelper 单例模式，多线程打开的是同一个数据库的连接，在多线程并发读写操作的时候，在Thread2还在使用数据库连接时，Thread1可能已经把它给关闭。所以，需要控制db关闭的时机，两种方案：
方案一：记录openDb的次数，打开一次，+1，关闭一次，-1，关闭时候当计数为0，再closeDb。。另外，针对多线程操作，openDb 》 多线程处理 》closeDb，，不要那么死板，非要处理一下关闭一下。
方案二：单线程的线程池 Executors.newSingleThreadExecutor()
针对上面场景的情况，采用CAS机制能解决，思想就是有人在使用db，就定期来看是否用完，用完了才轮到自己：
在并发环境下，某个线程对共享变量先进行操作，如果没有其他线程争用共享数据那操作就成功；如果存在数据的争用冲突，那就采取补偿措施，比如不断的重试机制，直到成功为止，因为这种乐观的并发策略不需要把线程挂起，也就把这种同步操作称为非阻塞同步（操作和冲突检测具备原子性）。在硬件指令集的发展驱动下，使得 "操作和冲突检测" 这种看起来需要多次操作的行为只需要一条处理器指令便可以完成
数据库性能
SQLiteStatement
使用方法
StringBuffer sql_insert = new StringBuffer();
sql_insert.append("INSERT INTO users(name,gender,age,phoneNumber,address) ");
sql_insert.append(" VALUES( ?, ?, ?, ?, ?)");
List<User> users = new ArrayList<User>();
for(int i = 0;i<1000;i++){
User user = new User();
user.setId(i);
user.setName("name"+i);
user.setGender(0);
user.setAge(user.getRandomAge());
user.setPhoneNumber("13800138000");
user.setAddress("GuangDong ShenZhen No."+i);
users.add(user);
}
for(User user:users){
SQLiteStatement statement=db.compileStatement(sql_insert.toString());
statement.bindString(1, user.getName());
statement.bindLong(2, user.getGender());
statement.bindLong(3, user.getAge());
statement.bindString(4, user.getPhoneNumber());
statement.bindString(5, user.getAddress());
statement.executeInsert();
}
SQLiteStatement 学习文章(原文链接：https://blog.csdn.net/efeics/article/details/18995433)
注意：如果要复用statement对象，避免数据没有及时清理而导致数据重复，需要清理statement.clearBindings
SQLiteStatement statement=db.compileStatement(sql_insert.toString());
for(User user:users){
statement.clearBindings
statement.bindString(1, user.getName());
...
statement.executeInsert();
}
事务
事务确保每一次操作都具有原子性
事务的实现是依赖于名为rollback journal文件，借助这个临时文件来完成原子操作和回滚功能。既然属于文件，就符合Unix的文件范型(Open-Read/Write- Close)，因而对于批量的修改操作会出现反复打开文件读写再关闭的操作。然而好在，我们可以显式使用事务，将批量的数据库更新带来的journal文件打开关闭降低到1次
事务相比预编译SQLiteStatement性能提升更明显
事务+预编译SQLiteStatement，在sqlite普通使用中，是性能特别高的方案
索引
索引的作用相当于图书的目录，索引会创建新的数据结构来保存索引字段的值和指向对应数据记录的指针，所以，查询的时候直接根据索引数据结构的指针快速查到数据记录，所以效率很高，另外，索引是有序的，所以，二分查找索引，索引查找数据记录。效率更高
使用索引，查询的性能提升非常显著，但是删改性能提升没那么高，因为数据删改，索引也要相应的改变，对于插入，索引可能反而会降低性能，并且索引让db的大小增加了2倍多
针对客户端中的数据库，往往数据量不多，增加索引，反而降低性能。。综合来看，针对数据量大，对查询速度要求高的，可以考虑索引
及时关闭Cursor
关闭Cursor释放资源
不关闭Cursor，并不会造成内存泄露，系统在SQLiteCursor的finalize方法里(GC会调用该方法)做了close
注意：当自定义SQLiteCursor的时候，如果没覆写finalize，那cursor释放不了，一直这样搞，IO资源总会耗尽
异步
对于大数据量的数据操作，也要考虑异步操作，避免导致ANR。总之非ui操作的都要考虑异步
WAL
Write Ahead Logging。上面为了解决多线程读写异常使用的技术，该机制下：
写：wal >>> delete
读：wal > delete
读写：wal模式充分竞争，与单独读/写性能差不多
网友评论