前言
Flutter 目前比较好用的 sqlite 数据库 orm 框架就是drift (以前叫作moor),由于dart语言不支持反射,这个框架使用了dart代码生成器,自动生成代码。这个数据库框架的优点是支持全平台使用,此框架通过使用datr语言的 ffi 功能(相当于JAVA的jni)来调用 sqlite 动态库 实现数据库操作,
Web平台是通过 wasm(浏览器可以运行的二进制文件,可由C、C++、rust、go 等语言编译而来)来调用sqlite, Web平台的sqlite数据库文件则通过 indexed db 虚拟文件系统保存。
官方文档
https://drift.simonbinder.eu/docs/platforms/
一.基本使用
先导入包
dependencies: drift: ^2.4.2 sqlite3_flutter_libs: ^0.5.0 #sqlcipher_flutter_libs: ^0.5.1 path_provider: ^2.0.0 path: ^1.8.2 dev_dependencies: drift_dev: ^2.4.1 build_runner: ^2.3.3
对每个包的说明
- drift: 这是定义大多数 api 的核心包。
- sqlite3_flutter_libs: 提供 sqlite 动态库,如果要加密数据库,请使用sqlcipher_flutter_libs。
- sqlcipher_flutter_libs: 提供 sqlcipher (加密版sqlite)
动态库,如果要加密数据库请添加此依赖,并移除sqlite3_flutter_libs依赖(共存会冲突)。
- path_provider 和 path:用于寻找合适的位置来存放数据库。 由 Flutter 和 Dart 团队维护。
- drift_dev:drift自动生成代码工具、 不会包含在最终应用程序中。
- build_runner: 代码生成的通用工具,由 Dart 团队维护。
使用方式一:通过drift文件,编写数据库sql语句创建表和字段
创建一个名为student的drift文件 “student.drift”,并在文件中写入以下代码:创建一个学生表
CREATE TABLE student ( id INT NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT );
创建文件db_manager.dart
import 'package:drift/drift.dart'; import 'dart:io'; import 'package:drift/native.dart'; import 'package:path_provider/path_provider.dart'; import 'package:path/path.dart' as p; part 'db_manager.g.dart';//这里会报错,不过没关系,执行 flutter pub run build_runner build @DriftDatabase( include: {'student.drift'},//引入表文件,多张表只需在这里添加即可 ) class DBManager extends _$DBManager { DBManager() : super(_openConnection()); @override int get schemaVersion => 1; } LazyDatabase _openConnection() { return LazyDatabase(() async { final dbFolder = await getApplicationDocumentsDirectory(); final file = File(p.join(dbFolder.path, 'test.db'));//数据库名字 return NativeDatabase(file); }); }
编写增删改查方法
Future getStudent() async { return await select(student).get(); } Future saveStudent(StudentCompanion companion) async { return await into(student).insert(companion); } Future deleteEmployee(int id) async { return (delete(student) ..where(student.id.equals(id) as Expression Function(Student tbl))) .go(); } Future deleteAllEmployee() async { return await delete(student).go(); } Future updateEmployee(StudentCompanion companion) async { return await update(student).write(StudentCompanion( name: Value(companion.name as String?) )); }
运行下面代码,点击一下添加一条数据
import 'package:flutter/material.dart'; import 'package:flutter_lib/db/db_manager.dart'; import 'package:drift/drift.dart' as d; DBManager? dbManager; void main(){ dbManager=DBManager(); runApp(const MyApp()); } class MyApp extends StatelessWidget { const MyApp({super.key}); // This widget is the root of your application. @override Widget build(BuildContext context) { return MaterialApp( title: 'Flutter Demo', theme: ThemeData( primarySwatch: Colors.blue, ), home: Scaffold( body: Center( child: IconButton( icon: const Icon(Icons.thumb_up), onPressed: () => { dbManager!.saveStudent( const StudentCompanion( name: d.Value("添加一条数据"), ), ) }, ), ), ), ); } }
可以看到生成了一个名为test的数据库
导出数据库文件,可以看到有张表student
使用方式二:通过继承Table类,生成表和字段
创建employee.dart文件,写入以下代码
class Employee extends Table{ IntColumn get id => integer().autoIncrement()(); TextColumn get title => text().withLength(min: 6, max: 32)(); TextColumn get content => text().named('body')(); IntColumn get category => integer().nullable()(); }
创建db_manager.dart文件,写入以下代码
import 'package:drift/drift.dart'; import 'dart:io'; import 'package:drift/native.dart'; import 'package:flutter_lib/db/employee.dart'; import 'package:path_provider/path_provider.dart'; import 'package:path/path.dart' as p; part 'db_manager.g.dart';//这里会报错,不过没关系,执行 flutter pub run build_runner build @DriftDatabase(tables: [Employee]) class AppDatabase extends _$AppDatabase{ AppDatabase() : super(_openConnection()); @override int get schemaVersion => 1; //插入一条数据 Future saveEmployee(EmployeeData companion) async { return await into(employee).insert(companion); } } LazyDatabase _openConnection() { return LazyDatabase(() async { final dbFolder = await getApplicationDocumentsDirectory(); final file = File(p.join(dbFolder.path, 'test.db')); return NativeDatabase(file); //return NativeDatabase.createInBackground(file); }); }
NativeDatabase.createInBackground(file)
用于在后台线程中创建一个数据库文件并返回一个数据库实例。它的主要作用是异步创建数据库文件,以避免在主线程中执行耗时的数据库创建操作,从而确保应用的响应性能。
二.初始化数据库
适合全局调用的方式如下,注意全局调用虽然方便,但也会带来不必要的开销。如果数据库使用场景不是很复杂,也可不必全局调用。
方式一:
AppDatabase? dbManager; void main(){ dbManager=AppDatabase(); runApp(const MyApp()); }
方式二:使用provider库
创建db_manager.dart文件
@DriftDatabase(tables: [Employee]) class AppDatabase extends _$AppDatabase{ AppDatabase(QueryExecutor e) : super(e); @override int get schemaVersion => 1; }
编写DatabaseProvider
import 'package:flutter/material.dart'; import 'package:provider/provider.dart'; import 'package:drift/native.dart'; class DatabaseProvider extends StatelessWidget { final Widget child; DatabaseProvider({required this.child}); @override Widget build(BuildContext context) { final database = AppDatabase(QueryExecutor.native(Isolate())..open('app_database.db')); return Provider( create: (_) => database, dispose: (_, db) => db.close(), child: child, ); } }
在顶层view中初始化
void main() { runApp( DatabaseProvider( child: MyApp(), ), ); }
程序中调用
final database = Provider.of(context);
三.实现一个简单的demo
demo功能如下图所示,实现数据库数据添加和删除
import 'dart:async'; import 'package:drift/drift.dart'; import 'package:flutter/material.dart'; import 'package:flutter_demo/db/db_manager.dart'; void main() { runApp(const MyApp()); } class MyApp extends StatelessWidget { const MyApp({super.key}); // This widget is the root of your application. @override Widget build(BuildContext context) { return MaterialApp( title: 'Flutter Demo', theme: ThemeData( colorScheme: ColorScheme.fromSeed(seedColor: Colors.deepPurple), useMaterial3: true, ), home: const HomePage(), ); } } class HomePage extends StatefulWidget { const HomePage({super.key}); @override State createState() => _HomePageState(); } class _HomePageState extends State { final database = AppDatabase(); final _tasksController = StreamController(); late StreamSubscription _tasksSubscription; int count = 0; @override void initState() { super.initState(); //监听数据库数据变化 _tasksSubscription = database.select(database.employee).watch().listen((tasks) { _tasksController.add(tasks); }); } @override void dispose() { _tasksSubscription.cancel(); _tasksController.close(); super.dispose(); } //添加数据 void addTask(String title) async { final task = EmployeeCompanion( title: Value(title), content: const Value('内容') ); await database.into(database.employee).insert(task); } //删除数据 void releaseTask(task) async{ await database.delete(database.employee).delete(task); } // 封装 StreamBuilder 方法 Widget buildTaskList(BuildContext context, AsyncSnapshot snapshot) { if (snapshot.hasData) { final tasks = snapshot.data; return ListView.builder( itemCount: tasks?.length, itemBuilder: (context, index) { final task = tasks![index]; return ListTile( title: Text(task.title), trailing: ElevatedButton( onPressed: () { releaseTask(task); }, child: const Text('删除数据'), ), ); }, ); } else if (snapshot.hasError) { return Text('Error: ${snapshot.error}'); } else { return const CircularProgressIndicator(); } } @override Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: const Text('数据库'), ), body: StreamBuilder( stream: _tasksController.stream, builder: buildTaskList, // 使用封装的方法 ), floatingActionButton: FloatingActionButton( onPressed: () { count++; addTask('添加第$count条数据'); }, // 悬浮按钮上的图标 backgroundColor: Colors.blue, child: const Icon(Icons.add), // 悬浮按钮的背景颜色 ) ); } }
四.数据库升级
如果表中需要添加字段,那么数据库需要升级,假如数据库版本从1升级到2,代码如下所示:
//表中添加新字段 dueDate class Employee extends Table{ IntColumn get id => integer().autoIncrement()(); TextColumn get title => text().withLength(min: 3, max: 32)(); TextColumn get content => text().named('body')(); IntColumn get category => integer().nullable()(); DateTimeColumn get dueDate => dateTime().nullable()(); // new, added column in v2 }
数据库管理修改如下:
@override int get schemaVersion => 2; @override MigrationStrategy get migration { return MigrationStrategy( onCreate: (Migrator m) async { await m.createAll(); }, onUpgrade: (Migrator m, int from, int to) async { if (from
五.高级用法
1.增删改查
import 'package:drift/drift.dart'; import 'package:flutter_drift/db/db_manager.dart'; import 'package:flutter_drift/db/db_singleton.dart'; ///图书表 class BookTable extends Table{ IntColumn get id => integer().autoIncrement()(); //书名 TextColumn get name => text()(); DateTimeColumn get createDate => dateTime().withDefault(currentDateAndTime)(); ///插入方法汇总 //当调用insertReturning方法时,它会执行插入操作,并返回插入的行数据。 // 如果插入操作中有自动生成的字段(例如自增主键),则这些字段的值也会包含在返回的行数据中 addReturning(String name) async { final db = DBSingleton().db; final companion = BookTableCompanion(name: Value(name)); BookTableData data = await db.bookTable.insertReturning(companion); //BookTableData? data=await db.bookTable.insertReturningOrNull(companion); } //插入一条数据,result:返回主键id static addOne(name) async { final db = DBSingleton().db; final companion = BookTableCompanion(name: Value(name)); int result = await db.bookTable.insertOne(companion); print('addOne result=$result'); } //用于将一行数据插入到数据库表中。如果已经存在具有相同主键的行,则会替换它。 //result:如果是新增返回主键id,如果是替换返回0 static addOnConflictUpdate(int id, String name) async { final db = DBSingleton().db; final companion = BookTableCompanion(id: Value(id), name: Value(name)); int result = await db.bookTable.insertOnConflictUpdate(companion); print('addOnConflictUpdate result=$result'); } ///修改方法汇总 //result:修改成功返回1 static updateWrite(int id, String name) async { final db = DBSingleton().db; final companion = BookTableCompanion(id: Value(id), name: Value(name)); final result = await (db.bookTable.update()..where((tbl) => tbl.id.equals(id))).write(companion); print('updateWrite result=$result'); } //result:返回修改后的数据实例 static updateWriteReturning(int id, String name) async { final db = DBSingleton().db; final companion = BookTableCompanion(id: Value(id), name: Value(name)); final result = await (db.bookTable.update()..where((tbl) => tbl.id.equals(id))).writeReturning(companion); print('updateWriteReturning result=$result'); } ///删除方法汇总 //result:删除成功返回1 static delete(int id) async { final db = DBSingleton().db; final result = await (db.bookTable.delete()..where((tbl) => tbl.id.equals(id))).go(); print('delete result=$result'); } //result:返回删除总数 static deleteAll() async { final db = DBSingleton().db; final result = await db.bookTable.delete().go(); print('deleteAll result=$result'); } //result:删除成功返回true static deleteOne(int id) async { final db = DBSingleton().db; final companion = BookTableCompanion(id: Value(id)); final result = await db.bookTable.deleteOne(companion); print('delete result=$result'); } //result:删除成功返回1 static deleteWhere(int id) async { final db = DBSingleton().db; final result = await db.bookTable.deleteWhere((tbl) => tbl.id.equals(id)); print('delete result=$result'); } ///查询方法汇总 //查询所有 static Future findAll() async { final db = DBSingleton().db; return await db.bookTable.select().get(); } //查询单个 static Future findSingle() async { final db = DBSingleton().db; return await db.bookTable.select().getSingle(); } } //批量插入图书 static insertAll(int size) async{ final db = DBSingleton().db; db.batch((batch){ List list=[]; for(int i=0;i final companion=BookTableCompanion(name: Value('书名${i+1}')); list.add(companion); } batch.insertAllOnConflictUpdate(db.bookTable, list); }); } IntColumn get id = integer().autoIncrement()(); //班级名称 TextColumn get name => text()(); DateTimeColumn get createDate => dateTime().withDefault(currentDateAndTime)(); } ///学生表 class StudentTable extends Table { IntColumn get id => integer().autoIncrement()(); //名字长度设置范围 TextColumn get name => text().withLength(min: 1, max: 10)(); //年龄 IntColumn get age => integer()(); //设置默认值 TextColumn get content => text().named('body')(); //班级外键 IntColumn get gradeId => integer().references(GradeTable, #id)(); DateTimeColumn get createDate => dateTime().withDefault(currentDateAndTime)(); }
查询代码如下:
///分页查询多表联查 static Future findAll(int length) async { final db = DBSingleton().db; //页码查询 final count = countAll(); final query = db .selectOnly(db.gradeTable) .join([leftOuterJoin(db.studentTable, db.studentTable.gradeId.isNotExp(db.gradeTable.id))]) ..addColumns([count]); //数据查询 final dataQuery = db .select(db.gradeTable) .join([leftOuterJoin(db.studentTable, db.studentTable.gradeId.isNotExp(db.gradeTable.id))]) ..limit(10, offset: length); //总数 final allCount = await query.map((row) => row.read(count)!).getSingle(); //获取查询后的数据,可以将两个表的数据融合 final datas = await dataQuery.map((row) { final gradeBean = row.readTable(db.gradeTable); final studentBean = row.readTable(db.studentTable); return {}; }).get(); return datas; }final db = DBSingleton().db; //页码查询 final count = countAll(); final query = db .selectOnly(db.speciesTree) .join([leftOuterJoin(db.researchCommon, db.researchCommon.treeNumber.isNotExp(db.speciesTree.serialNumber))]) ..addColumns([count]); //数据查询 final dataQuery = db.select(db.speciesTree).join([ leftOuterJoin(db.researchCommon, db.researchCommon.treeNumber.isNotExp(db.speciesTree.serialNumber)), ]) ..limit(100); if (isGroupRow) { query.where(db.speciesTree.serialNumber.like('%$keyword%') & db.speciesTree.ancientTreeGroupNumber.isNotNull()); dataQuery .where(db.speciesTree.serialNumber.like('%$keyword%') & db.speciesTree.ancientTreeGroupNumber.isNotNull()); } else { query.where(db.speciesTree.serialNumber.like('%$keyword%') & db.speciesTree.ancientTreeGroupNumber.isNull()); dataQuery.where(db.speciesTree.serialNumber.like('%$keyword%') & db.speciesTree.ancientTreeGroupNumber.isNull()); } //总数 final allCount = await query.map((row) => row.read(count)!).getSingle(); //获取查询后的数据,可以将两个表的数据融合 final datas = await dataQuery.map((row) { final bean = row.readTable(db.speciesTree); return bean; }).get();
3.各种查询条件
//相等条件 await (db.productsTable.select()..where((tbl) => tbl.id.equals(1))).getSingle(); // 查询id大于 20的数据 await (db.productsTable.select()..where((tbl) => tbl.id.isBiggerThanValue(20))).get(); // 查询id小于 20的数据 await (db.productsTable.select()..where((tbl) => tbl.id.isSmallerThanValue(20))).get(); // 包含条件 await (db.productsTable.select()..where((tbl) => tbl.id.isIn([1,2,3]))).get(); // 模糊查询 await (db.productsTable.select()..where((tbl) => tbl.name.like('%'))).get(); // 空条件查询 await (db.productsTable.select()..where((tbl) => tbl.name.isNull())).get(); await (db.productsTable.select()..where((tbl) => tbl.name.isNotNull())).get();
还没有评论,来说两句吧...