Flutter - 集成三方庫:數據庫(sqflite)

數據庫

$ flutter pub add sqlite
$ flutter pub get

$ flutter run

運行失敗，看是編譯報錯,打開Xcode工程 ? + B 編譯

2025-05-15 08.47.33.png

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

2025-05-15 19.03.14.png

然后查看 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

2025-05-15 19.33.17.png

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

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

2025-05-15 19.34.50.png

使用

打開/關閉/刪除數據庫

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)

2025-05-17 20.31.58.png

使用工具方法

使用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);}}

2025-05-20 15.33.50.png

數據庫遷移

隨著功能迭代,需要對數據庫的表結構進行修改時,比如增加新字段時,需要對表的結構進行更新。

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());

2025-05-20 16.26.51.png

事務

數據庫的增刪改查可能會失敗,導致數據與預期的不一致,為了保證在執行前后的數據一致性，引入了事務。事務具有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的默認會用代理,但是運行在真機上,手機沒有代理，應該是這樣影響了網絡環境。