Flutter - 集成三方库:数据库(sqflite)

数据库

$ flutter pub add sqlite
$ flutter pub get

$ flutter run

运行失败，看是编译报错,打开Xcode工程 ⌘ + B 编译

对比 GSYGithubAppFlutter 的Xcode工程Build Phases > [CP] Embed Pods Frameworks 有sqfite.framework。本地默认的Flutter工程默认未生成Podfile

然后查看 GSYGithubAppFlutter

...
require File.expand_path(File.join('packages', 'flutter_tools', 'bin', 'podhelper'), flutter_root)flutter_ios_podfile_setuptarget 'Runner' douse_frameworks!use_modular_headers!flutter_install_all_ios_pods File.dirname(File.realpath(__FILE__))
end
...

看代码是引入了Flutter提供的工具的,从flutter的安装目录下找到podhelper.rb这个文件

# 方法: flutter_install_all_ios_pods
# 安装Flutter在iOS平台上的引擎和插件
def flutter_install_all_ios_pods(ios_application_path = nil)# 创建Flutter引擎的.podspec文件flutter_install_ios_engine_pod(ios_application_path)flutter_install_plugin_pods(ios_application_path, '.symlinks', 'ios')
end

# 方法: flutter_install_plugin_pods
def flutter_install_plugin_pods(application_path = nil, relative_symlink_dir, platform)# CocoaPods定义了 defined_in_file，获取应用路径,未获取到就中断application_path ||= File.dirname(defined_in_file.realpath) if respond_to?(:defined_in_file)raise 'Could not find application path' unless application_path# Prepare symlinks folder. We use symlinks to avoid having Podfile.lock# referring to absolute paths on developers' machines.# 使用符号链接,避免使用Podfile.lock这个文件# Flutter是在ios目录下创建.symlinks目录,里面有软链接指向Flutter下载包的位置,这样只需要一份即可。# 先删除,再创建对应的目录symlink_dir = File.expand_path(relative_symlink_dir, application_path)system('rm', '-rf', symlink_dir) symlink_plugins_dir = File.expand_path('plugins', symlink_dir)system('mkdir', '-p', symlink_plugins_dir)plugins_file = File.join(application_path, '..', '.flutter-plugins-dependencies')dependencies_hash = flutter_parse_plugins_file(plugins_file)plugin_pods = flutter_get_plugins_list(dependencies_hash, platform)swift_package_manager_enabled = flutter_get_swift_package_manager_enabled(dependencies_hash, platform)plugin_pods.each do |plugin_hash|plugin_name = plugin_hash['name']plugin_path = plugin_hash['path']...# 使用path: 的方式本地依赖需要的三方库# 手动添加打印确认下# print "plugin_name:#{plugin_name}\n"pod plugin_name, path: File.join(relative, platform_directory)end
end

$ pod update --verbose

因此Podfile里的target部分就依赖了sqflite_darwin

target 'Runner' douse_frameworks!use_modular_headers!...pod 'sqflite_darwin', path:.symlinks/plugins/sqflite_darwin/darwin
end

使用

打开/关闭/删除数据库

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'finger.db');/// 打开数据库
Database database = await openDatabase(path, version: 1,onCreate: (Database db, int version) async {/// 当创建数据库时创建tableawait db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});

/// 关闭数据库
await db.close();

/// 删除数据库
await deleteDatabase(path);

/// 添加表
await database.execute("CREATE TABLE Test2(id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)",);/// 删除表
await database.execute('DROP TABLE Test2');

使用SQL语句

/// 添加数据
await database.transaction((txn) async {int id1 = await txn.rawInsert('INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');int id2 = await txn.rawInsert('INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',['another name', 12345678, 3.1416]);
});

/// 删除数据
count = await database.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);

/// 更新数据
int count = await database.rawUpdate('UPDATE Test SET name = ?, value = ? WHERE name = ?',['updated name', '9876', 'some name']);

/// 查询数据
List<Map> list = await database.rawQuery('SELECT * FROM Test');
print(list)

使用工具方法

使用Sqflite提供的工具方法来执行数据库操作，而不是直接使用SQL语句

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';final String tName = 'company';
final String columnId = "_id";
final String columnName = "name";class Company {int? id;String? name;Company();Map<String, Object?> toMap() {var map = <String, Object?>{columnName: name};if (id != null) {map[columnId] = id;}return map;}Company.fromMap(Map map) {id = map[columnId];name = map[columnName];}
}class CompanyProvider {Database? db;Future<Database?> open() async {if (db == null) {var databasesPath = await getDatabasesPath();String path = join(databasesPath, 'demo.db');db = await openDatabase(path,version: 1,onCreate: (Database db, int version) async {await db.execute('''create table $tName ($columnId integer primary key autoincrement,$columnName text not null)''');},);}return db;}/// 注册企业Future insert(Company company) async {/// 工具方法: 传表名 + 列信息添加数据到数据库company.id = await db?.insert(tName, company.toMap());return company;}/// 查找企业Future findById(int id) async {List<Map> maps = await db!.query(tName, /// 表名columns: [columnId, columnName], /// 查找的列where: '$columnId = ?', /// 查找条件whereArgs: [id], /// 每个问号填充的值);if (maps.isNotEmpty) {return Company.fromMap(maps.first);}return null;}/// 查找所有的企业Future<List<Company>> find() async {List<Company> companys = [];List<Map> maps = await db!.query(tName, columns: [columnId, columnName]);for (var map in maps) {Company c = Company.fromMap(map);companys.add(c);}return companys;}/// 删除企业Future delete(int id) async {/// 根据id列删除企业return await db?.delete(tName, where: '$columnId = ?', whereArgs: [id]);}/// 更新企业信息Future update(Company company) async {return await db?.update(tName,company.toMap(),where: '$columnId = ?',whereArgs: [company.id],);}
}

void test() async {/// 添加2条测试数据CompanyProvider cp = CompanyProvider();await cp.open();List<Map> maps = [{"name": "Google"},{"name": "Apple"},];/// 新增数据int firstId = 0;for (int i = 0; i < maps.length; ++i) {Company c = Company.fromMap(maps[i]);cp.insert(c);}/// 查找数据List<Company> companys = await cp.find();if (companys.isNotEmpty) {firstId = companys.first.id!;}if (firstId > 0) {Company firstCompany = await cp.findById(firstId);print(firstCompany.toMap());/// 更新数据Company chgCompany = Company();chgCompany.id = firstId;chgCompany.name = DateTime.now().microsecondsSinceEpoch.toString();cp.update(chgCompany);firstCompany = await cp.findById(firstId);print(firstCompany.toMap());/// 删除数据cp.delete(firstId);}}

数据库迁移

随着功能迭代,需要对数据库的表结构进行修改时,比如增加新字段时,需要对表的结构进行更新。

Future<Database?> open() async {if (db == null) {var databasesPath = await getDatabasesPath();String path = join(databasesPath, 'demo.db');db = await openDatabase(path,version: 2,/// 1.新版本发布时改成2onCreate: (db, version) async {/// 2.新安装设备触发onCreate，所以这里添加新的字段await db.execute('''create table $tName ($columnId integer primary key autoincrement,$columnName text not null,$columnDesc text)''');},onUpgrade: (db, oldVersion, newVersion) async {var batch = db.batch();/// [onUpgrade] is called if either of /// the following conditions are met:/// 1. [onCreate] is not specified/// 2. The database already exists and [version] is higher than the last database version/// onUpgrade回调在未指定onCreate回调或者数据库已经存在同时version字段高于已安装的版本,执行完onUpgrade回调后应该会更新关联的版本,设置断点让onUpgrade执行中断,下次还会会执行这个方法/// 3.对旧版本的设备:判断安装设备已创建的数据库版本if (oldVersion == 1) {_updateTableCompanyV1toV2(batch);}await batch.commit();},);}return db;}

/// 4.添加description字段
void _updateTableCompanyV1toV2(Batch batch) {batch.execute('ALTER TABLE Company ADD description TEXT');
}/// 其它的一些处理
final String columnDesc = "description";
...class Company {int? id;String? name;/// 5.模型增加对应字段 + 列String? description;.../// 6. 更新map和对象的转换方法Map<String, Object?> toMap() {var map = <String, Object?>{columnName: name, columnDesc: description};if (id != null) {...

/// 调用
...
firstCompany.description = "版本2新增的字段";
print(firstCompany.toMap());

事务

数据库的增删改查可能会失败,导致数据与预期的不一致,为了保证在执行前后的数据一致性，引入了事务。事务具有ACID这4个特性：原子性、一致性、隔离性和持久性。

在事务中不要使用数据库,而只需要使用事务对象访问数据库。

await database.transaction((txn) async {// 正确await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');// 不要在事务中使用数据库// 下面会导致死锁await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
});

try {await database.transaction((txn) async {await txn.update('TABLE', {'foo': 'bar'});});// No error, the transaction is committed// 1. 未报错,则事务被提交// cancel the transaction (any error will do)// 2. 取消或执行时报错,则抛出异常在,catch中被捕获// throw StateError('cancel transaction');
} catch (e, st) {// this reliably catch if there is a key conflict// We know that the transaction is rolled back.// 3. 事务被回滚,执行业务相关的操作,比如提示报错
}

批处理

使用 Batch,即批处理,来避免在 Dart 和原生代码之间的反复切换。

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
/// 批处理统一提交
results = await batch.commit();

在事务中,批处理的commit会等到事务提交后

await database.transaction((txn) async {var batch = txn.batch();// ...// commit but the actual commit will happen when the transaction is committed// however the data is available in this transaction/// 当事务被提交时才会真正的提交await batch.commit();//  ...
});

/// 设置批处理出现错误依然提交
await batch.commit(continueOnError: true);

表名和列名

SQLite的关键词,要避免使用作为实体(Entity)名。

"add","all","alter","and","as","autoincrement","between","case","check","collate","commit","constraint","create","default","deferrable","delete","distinct","drop","else","escape","except","exists","foreign","from","group","having","if","in","index","insert","intersect","into","is","isnull","join","limit","not","notnull","null","on","or","order","primary","references","select","set","table","then","to","transaction","union","unique","update","using","values","when","where"

sqflite的工具方法会进行处理,避免与关键字的冲突

db.query('table')
/// 等价于
db.rawQuery('SELECT * FROM "table"');

其它问题

VSCode 无法调试

Error connecting to the service protocol: failed to connect to http://127.0.0.1:51020/Kra7fZnYjeI=/ Error: Failed to register service methods on attached VM Service: registerService: (-32000) Service connection disposed

原来有成功过，后面发现一直都会有问题,前段时间突然不行,在长时间运行后就会报这个错误,但是单独在VSCode外部用flutter run命令能正常运行。

发现终端可以是把本地的端口转发的代理给去掉了。然后发现VSCode的代理有这样的说明,若未设置则会继承环境变量中的http_proxy和https_proxy，我把代理加到.zshrc中，所以VSCode的默认会用代理,但是运行在真机上,手机没有代理，应该是这样影响了网络环境。

1. .zshrc去掉代理的配置
2. 重新打开VSCode && 运行 => 能正常调试

参考

1. SQLite CRUD operations in Flutter
2. sqflite-doc
3. sqflite Migration example