flutter中数据库(Drift)的使用

前言

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<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.
  @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<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文件

@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<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.
  @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<HomePage> createState() => _HomePageState();
}

class _HomePageState extends State<HomePage> {
  final database = AppDatabase();
  final _tasksController = StreamController<List<EmployeeData>>();
  late StreamSubscription<List<EmployeeData>> _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<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();
    }
  }

  @override
  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
}

数据库管理修改如下：

  @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 < 2) {
          // we added the dueDate property in the change from version 1 to
          // version 2
          await m.addColumn(employee, employee.dueDate);
        }
      },
    );
  }

五.高级用法

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