SQLite
如果您正在编写一个需要持久化且查询大量本地设备数据的 app，可考虑采用数据库，而不是本地文件夹或关键值库。总的来说，相比于其他本地持久化方案来说，数据库能够提供更为迅速的插入、更新、查询功能。
Flutter应用程序中可以通过 sqflite package 来使用 SQLite 数据库。本文将通过使用 sqflite 来演示插入，读取，更新，删除各种数据。
如果你对于 SQLite 和 SQL 的各种语句还不熟悉，请查看 SQLite 官方的教程 SQLite 教程，在查看本文之前需要掌握基本的SQL语句。
添加sqflite依赖
dependencies:
flutter:
sdk: flutter
sqflite:
path:
封装
MessageDB
MessageDB以单例实现，提供以下功能。
- 数据库初始化
- 表初始化
- 数据库打开关闭
- 是持有表对象【SessionListTable、SessionChatTable、】
class MessageDB {
// 单例公开访问点
factory MessageDB() => _sharedInstance();
static MessageDB get instance => _sharedInstance();
// 静态私有成员，没有初始化
static MessageDB _instance;
// 私有构造函数
MessageDB._() {
// 具体初始化代码
}
// 静态、同步、MessageDB
static MessageDB _sharedInstance() {
if (_instance == null) {
_instance = MessageDB._();
}
return _instance;
}
/// init db
Future<Database> database;
final SessionListTable sessionListTable = SessionListTable();
final SessionChatTable sessionChatTable = SessionChatTable();
initDB() async {
try {
String uid = UserManager.instance.userInfo.uid;
database = openDatabase(
// Set the path to the database.
// 需要升级可以直接修改数据库名称
join(await getDatabasesPath(), 'message_database_v1_$uid.db'),
// When the database is first created, create a table to store dogs.
onCreate: (db, version) {
// Run the CREATE TABLE statement on the database.
try {
Batch batch = db.batch();
_createTable().forEach((element) {
batch.execute(element);
});
batch.commit();
} catch (err) {
throw (err);
}
},
onUpgrade: (Database db, int oldVersion, int newVersion) {
try {
Batch batch = db.batch();
_createTable().forEach((element) {
batch.execute(element);
});
batch.commit();
} catch (err) {
throw (err);
}
},
// Set the version. This executes the onCreate function and provides a
// path to perform database upgrades and downgrades.
version: 1,
);
sessionListTable.database = database;
sessionChatTable.database = database;
} catch (err) {
throw (err);
}
}
/// close db
closeDB() async {
await database
..close();
}
/// create table
List<String> _createTable() {
return [sessionListTable.createTable(), sessionChatTable.createTable()];
}
}
SessionTableListener
SessionTableListener作为抽象类，提供以下接口。
abstract class SessionTableListener {
Future<Database> database;
createTable();
insertList(List values);
queryListMaxVersionId({num id});
queryList({num id});
deleteItem(num id);
}
SessionListTable和SessionChatTable继承SessionTableListener实现对表数据的增删查改操作。
SessionListTable实现如下。
class SessionListTable implements SessionTableListener {
@override
Future<Database> database;
final String sessionListTable = 'session_list';
@override
createTable() {
return "CREATE TABLE IF NOT EXISTS $sessionListTable ("
"peer_id INTEGER PRIMARY KEY,"
" last_msg_id INTEGER,"
" unread_count INTEGER,"
" version_id INTEGER,"
" update_time INTEGER,"
" last_msg TEXT,"
" contact_type INTEGER,"
" contact_user TEXT,"
" unread_gift_count INTEGER,"
" del_status INTEGER,"
" top_weight INTEGER,"
" sort_key INTEGER,"
" stick INTEGER)";
}
///插入会话列表
@override
insertList(List values) async {
// Get a reference to the database.
final Database db = await database;
// Insert the Dog into the correct table. You might also specify the
// `conflictAlgorithm` to use in case the same maps is inserted twice.
//
// In this case, replace any previous data.
try {
Batch batch = db.batch();
values.forEach((element) {
Map<String, dynamic> values = Map<String, dynamic>();
values['peer_id'] = element['peer_id'];
values['last_msg_id'] = element['last_msg_id'];
values['unread_count'] = element['unread_count'];
values['version_id'] = element['version_id'];
values['update_time'] = element['update_time'];
values['last_msg'] = element['last_msg'];
values['contact_type'] = element['contact_type'];
values['contact_user'] = element['contact_user'];
values['unread_gift_count'] = element['unread_gift_count'];
values['del_status'] = element['del_status'];
values['top_weight'] = element['top_weight'];
values['sort_key'] = element['sort_key'];
values['stick'] = element['stick'];
batch.insert(
sessionListTable,
values,
conflictAlgorithm: ConflictAlgorithm.replace,
);
});
await batch.commit();
} catch (err) {
throw err;
}
}
///查询会话列表最大的version id
@override
queryListMaxVersionId({num id}) async {
final Database db = await database;
List<Map> maps = await db.query(sessionListTable, orderBy: 'version_id DESC', limit: 1);
if (maps.length>0) {
return maps[0]['version_id'];
}
else {
return 0;
}
}
///查询所有的会话列表
@override
queryList({num id}) async {
// Get a reference to the database.
final Database db = await database;
//联系人类型 0 普通 1 打招呼 100 系统
// Query the table for all The maps.
final List<Map<String, dynamic>> maps = await db.query(sessionListTable,
where: 'del_status = ? AND contact_type != ?',
whereArgs: [0, 100],
orderBy: 'sort_key DESC');
return maps;
}
///会话删除
@override
deleteItem(num id) async {
// Get a reference to the database.
final Database db = await database;
await db.delete(sessionListTable, where: 'peer_id = ?', whereArgs: [id]);
}
///所有会话未读数
querySessionAllUnRead() async {
// Get a reference to the database.
final Database db = await database;
//联系人类型 0 普通 1 打招呼 100 系统
final List<Map<String, dynamic>> maps = await db.query(sessionListTable,
columns: ['unread_count'],
where: 'unread_count > ? AND contact_type != ?',
whereArgs: [0, 100]);
int count = maps.fold(0, (previousValue, element) => previousValue+element['unread_count']);
return count;
}
///所有会话标记已读
updateSessionMarkAllRead() async {
// Get a reference to the database.
final Database db = await database;
await db.update(sessionListTable, {'unread_count':0});
}
///会话标记已读
updateSessionMarkRead(num id) async {
// Get a reference to the database.
final Database db = await database;
await db.update(sessionListTable, {'unread_count':0}, where: 'peer_id = ?', whereArgs: [id]);
}
}
问题
异步初始化数据库
//初始化数据库
MessageDB.instance.initDB();
登录成功之后，初始化数据库为异步操作。同时会拉取会话列表，这时可能数据库还未初始化完成。解决办法，延迟2s后再拉取会话列表。
Future.delayed(Duration(seconds: 2), () {
MessageDB.instance.sessionChatTable.queryList(id: _peerUser.id)
});
数据库升级
升级表或字段，修改version，会调用onUpgrade方法，可以在此处升级。
// Set the version. This executes the onCreate function and provides a
// path to perform database upgrades and downgrades.
version: 1,
如果业务比较简单，本地数据不是很重要，服务端会同步数据。不如简单粗暴，直接更改数据库名称，重新创建数据库。
database = openDatabase(
// Set the path to the database.
// 需要升级可以直接修改数据库名称
join(await getDatabasesPath(), 'message_database_v1_$uid.db'),
v1改为v2
添加新的字段
insertList(List values) async {
// Get a reference to the database.
final Database db = await database;
// Insert the Dog into the correct table. You might also specify the
// `conflictAlgorithm` to use in case the same maps is inserted twice.
//
// In this case, replace any previous data.
try {
Batch batch = db.batch();
values.forEach((element) {
Map<String, dynamic> values = Map<String, dynamic>();
values['peer_id'] = element['peer_id'];
values['last_msg_id'] = element['last_msg_id'];
values['unread_count'] = element['unread_count'];
values['version_id'] = element['version_id'];
values['update_time'] = element['update_time'];
values['last_msg'] = element['last_msg'];
values['contact_type'] = element['contact_type'];
values['contact_user'] = element['contact_user'];
values['unread_gift_count'] = element['unread_gift_count'];
values['del_status'] = element['del_status'];
values['top_weight'] = element['top_weight'];
values['sort_key'] = element['sort_key'];
values['stick'] = element['stick'];
batch.insert(
sessionListTable,
values,
conflictAlgorithm: ConflictAlgorithm.replace,
);
});
await batch.commit();
} catch (err) {
throw err;
}
}
不要把List values直接传入table，如果map包含table不存在的数据，就会插入失败。为了保证传入的map包含的key都存在table的字段，对values的map需要的字段做一次解析，再传入sql。
网友评论