前言
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
(
include: {'student.drift'},//引入表文件,多张表只需在这里添加即可
)
class DBManager extends _$DBManager {
DBManager() : super(_openConnection());
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<List<StudentData>> getStudent() async {
return await select(student).get();
}
Future<int> saveStudent(StudentCompanion companion) async {
return await into(student).insert(companion);
}
Future<int> deleteEmployee(int id) async {
return (delete(student)
..where(student.id.equals(id) as Expression<bool> Function(Student tbl)))
.go();
}
Future<int> deleteAllEmployee() async {
return await delete(student).go();
}
Future<int> 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.
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
(tables: [Employee])
class AppDatabase extends _$AppDatabase{
AppDatabase() : super(_openConnection());
int get schemaVersion => 1;
//插入一条数据
Future<int> 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文件
(tables: [Employee])
class AppDatabase extends _$AppDatabase{
AppDatabase(QueryExecutor e) : super(e);
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});
Widget build(BuildContext context) {
final database = AppDatabase(QueryExecutor.native(Isolate())..open('app_database.db'));
return Provider<AppDatabase>(
create: (_) => database,
dispose: (_, db) => db.close(),
child: child,
);
}
}
在顶层view中初始化
void main() {
runApp(
DatabaseProvider(
child: MyApp(),
),
);
}
程序中调用
final database = Provider.of<AppDatabase>(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.
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});
State<HomePage> createState() => _HomePageState();
}
class _HomePageState extends State<HomePage> {
final database = AppDatabase();
final _tasksController = StreamController<List<EmployeeData>>();
late StreamSubscription<List<EmployeeData>> _tasksSubscription;
int count = 0;
void initState() {
super.initState();
//监听数据库数据变化
_tasksSubscription =
database.select(database.employee).watch().listen((tasks) {
_tasksController.add(tasks);
});
}
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<List<EmployeeData>> 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();
}
}
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('数据库'),
),
body: StreamBuilder<List<EmployeeData>>(
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
}
数据库管理修改如下:
get schemaVersion => 2;
MigrationStrategy get migration {
return MigrationStrategy(
onCreate: (Migrator m) async {
await m.createAll();
},
onUpgrade: (Migrator m, int from, int to) async {
if (from < 2) {
// we added the dueDate property in the change from version 1 to
// version 2
await m.addColumn(employee, employee.dueDate);
}
},
);
}
int
五.高级用法
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<List<BookTableData>> findAll() async {
final db = DBSingleton().db;
return await db.bookTable.select().get();
}
//查询单个
static Future<BookTableData> 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<BookTableCompanion> list=[];
for(int i=0;i<size;i++){
final companion=BookTableCompanion(name: Value('书名${i+1}'));
list.add(companion);
}
batch.insertAllOnConflictUpdate(db.bookTable, list);
});
}
2.分页查询以及多表联查
如果数据量大,必然要进行分页查询,代码如下:
查询每个班级有多少学生:表结构如下
///班级表
class GradeTable extends Table {
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<List<dynamic>> 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();