drift教程-01

drift介绍

• 一个适用于dart/flutter语言的ORM框架
• 内部使用的数据库为sqlite3
• 适用于桌面开发时，关系型数据的存储

官网：Drift (simonbinder.eu)

依赖安装

flutter pub add path path_provider sqlite3_flutter_libs sqflite_common_ffi 

flutter pub add dev:json_serializable dev:drift_dev dev:build_runner dev:test dev:path_provider_platform_interface dev:plugin_platform_interface

提前写个path_provider的fake工具，避免path_provider报错

fake_path_provider_platform.dart

import 'dart:io';

import 'package:flutter_test/flutter_test.dart';
import 'package:path_provider_platform_interface/path_provider_platform_interface.dart';
import 'package:plugin_platform_interface/plugin_platform_interface.dart';
import 'package:path/path.dart' as p;

const String kTemporaryPath = 'temporaryPath';
const String kApplicationSupportPath = 'applicationSupportPath';
const String kDownloadsPath = 'downloadsPath';
const String kLibraryPath = 'libraryPath';
const String kApplicationDocumentsPath = 'applicationDocumentsPath';
const String kExternalCachePath = 'externalCachePath';
const String kExternalStoragePath = 'externalStoragePath';

class FakePathProviderPlatform extends Fake
    with MockPlatformInterfaceMixin
    implements PathProviderPlatform {
  @override
  Future<String?> getTemporaryPath() async {
    return kTemporaryPath;
  }

  @override
  Future<String?> getApplicationSupportPath() async {
    return kApplicationSupportPath;
  }

  @override
  Future<String?> getLibraryPath() async {
    return kLibraryPath;
  }

  @override
  Future<String?> getApplicationDocumentsPath() async {
    return kApplicationDocumentsPath;
  }

  @override
  Future<String?> getExternalStoragePath() async {
    return kExternalStoragePath;
  }

  @override
  Future<List<String>?> getExternalCachePaths() async {
    return <String>[kExternalCachePath];
  }

  @override
  Future<List<String>?> getExternalStoragePaths({
    StorageDirectory? type,
  }) async {
    return <String>[kExternalStoragePath];
  }

  @override
  Future<String?> getDownloadsPath() async {
    return kDownloadsPath;
  }
}

class AllNullFakePathProviderPlatform extends Fake
    with MockPlatformInterfaceMixin
    implements PathProviderPlatform {
  @override
  Future<String?> getTemporaryPath() async {
    return null;
  }

  @override
  Future<String?> getApplicationSupportPath() async {
    return null;
  }

  @override
  Future<String?> getLibraryPath() async {
    return null;
  }

  @override
  Future<String?> getApplicationDocumentsPath() async {
    return null;
  }

  @override
  Future<String?> getExternalStoragePath() async {
    return null;
  }

  @override
  Future<List<String>?> getExternalCachePaths() async {
    return null;
  }

  @override
  Future<List<String>?> getExternalStoragePaths({
    StorageDirectory? type,
  }) async {
    return null;
  }

  @override
  Future<String?> getDownloadsPath() async {
    return null;
  }
}

class FakePathProviderPlatformBaseCurrentProjectRoot extends Fake
    with MockPlatformInterfaceMixin
    implements PathProviderPlatform {
  final pathProviderBaseDir = p.join(Directory.current.path, 'path_provider');
  Directory? baseDir;

  init() async {
    if (baseDir != null) return;

    baseDir = Directory(pathProviderBaseDir);
    await createDir(baseDir!);
  }

  createDir(Directory dir) async {
    bool hasDir = await dir.exists();
    if (hasDir) await dir.delete(recursive: true);
    await dir.create(recursive: true);
  }

  Future<String?> createDirFromBaseDirAndPath(String dirName) async {
    await init();
    String retDir = p.join(pathProviderBaseDir, dirName);
    await createDir(Directory(retDir));
    return retDir;
  }

  @override
  Future<String?> getTemporaryPath() async {
    return createDirFromBaseDirAndPath('tmp');
  }

  @override
  Future<String?> getApplicationSupportPath() async {
    return createDirFromBaseDirAndPath('appSupport');
  }

  @override
  Future<String?> getLibraryPath() async {
    return null;
  }

  @override
  Future<String?> getApplicationDocumentsPath() async {
    return createDirFromBaseDirAndPath('appDocument');
  }

  @override
  Future<String?> getExternalStoragePath() async {
    return null;
  }

  @override
  Future<List<String>?> getExternalCachePaths() async {
    return null;
  }

  @override
  Future<List<String>?> getExternalStoragePaths({
    StorageDirectory? type,
  }) async {
    return null;
  }

  @override
  Future<String?> getDownloadsPath() async {
    return null;
  }
}

建库，建表

drift_database.dart

import 'dart:io';

import 'package:path/path.dart' as p;
import 'package:demo_03/src/utils/logger_util.dart';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';

/*
会将代码生成到 这个文件，文件的命名规则是: 当前文件的文件名.g.dart
如: 当前文件名为 test.dart, 则代码生成的文件名应该是: test.g.dart
 */
part 'drift_database.g.dart';

// 表定义. drift会根据表定义生成建表语句，并建表
class PersonTable extends Table {
  IntColumn get id => integer().nullable().autoIncrement()();
  TextColumn get name => text().withLength(max: 100, min: 1)();
  IntColumn get age => integer().nullable()();
  BoolColumn get male => boolean()();
  DateTimeColumn get birthDay => dateTime()();
}

// 这个 @DriftDatabase 有两个作用，一个时标识 数据库类，另一个是指定该数据库中有哪些表
@DriftDatabase(tables: [PersonTable])
class AppDatabase extends _$AppDatabase {
  static AppDatabase? _instance;

  /// 获取数据库实例(单例模式保持全局仅有一个数据库实例)
  /// [printSql] 当执行sql相关操作时，是否将原始sql语句也打印到控制台. 默认:false
  static AppDatabase getDB([bool printSql = false]) {
    _instance ??= AppDatabase._(printSql);
    return _instance!;
  }

  static Future<void> closeDB() async {
    await _instance?.close();
    _instance = null;
  }

  AppDatabase._(bool printSql) : super(_openConnection(printSql));

  @override
  int get schemaVersion => 1;
}

LazyDatabase _openConnection(bool printSql) {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    // put the database file, called db.sqlite here, into the documents folder
    // for your app.
    final dbFolder = await getApplicationDocumentsDirectory();
    final String dbFilePath = p.join(dbFolder.path, 'db.sqlite');
    logger.d('数据库文件路径:$dbFilePath');
    final file = File(dbFilePath);
    return NativeDatabase.createInBackground(file, logStatements: printSql);
  });
}

单表CRUD操作

import 'package:demo_03/src/utils/logger_util.dart';
import 'package:drift/drift.dart';
import 'package:flutter_test/flutter_test.dart';
import 'package:path_provider_platform_interface/path_provider_platform_interface.dart';
import 'package:path/path.dart' as p;
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

import 'drift_database.dart';
import 'fake_path_provider_platform.dart';
import 'dart:io';

void main() {
  logger.d('main执行');

  // 这是path_provider要能在单元测试中运行，必须加的
  TestWidgetsFlutterBinding.ensureInitialized();

  // 这句代码会在windows环境加载sqlite3.dll文件(内部会自动判断，只有在windows环境才会执行加载dll文件的逻辑)
  sqfliteFfiInit();

  setUp(() {
    // 使用fake实例，替换原本的实例，避免报错
    PathProviderPlatform.instance =
        FakePathProviderPlatformBaseCurrentProjectRoot();
  });

  test('项目的sqlite3目录', () {
    final scriptDir = File(Platform.script.toFilePath()).parent;
    final libraryNextToScript =
        File(p.join(scriptDir.path, 'sqlite3', 'sqlite3.dll'));
    logger.d(libraryNextToScript.path);
    logger.d(Platform.script.resolve("sqlite3/sqlite3.dll").toFilePath());
    logger.d(Platform.script
        .resolve("sqlite3/sqlite3.dll")
        .toFilePath(windows: true));
  });

  test('通过drift创建并获取sqllite数据库', () async {
    // 打开数据库连接
    final database = AppDatabase.getDB(true);

    // 增
    await database.into(database.personTable).insert(
        PersonTableCompanion.insert(
            name: '张三',
            age: const Value(12),
            male: true,
            birthDay: DateTime.now()));

    // 多结果集查询
    List<PersonTableData> list =
        await database.select(database.personTable).get();
    assert(list.isNotEmpty && list.length == 1);

    // 单结果集查询
    PersonTableData? data = await (database.select(database.personTable)
          ..where((tbl) => tbl.id.equals(1)))
        .getSingleOrNull();
    assert(data != null);

    // 更新
    final int updateCount = await (database.update(database.personTable)
          ..where((tbl) => tbl.id.equals(1)))
        .write(const PersonTableCompanion(name: Value('张三丰')));
    assert(updateCount == 1);

    final int updateCount2 = await (database.update(database.personTable)
          ..where((tbl) => tbl.id.equals(2)))
        .write(const PersonTableCompanion(name: Value('张三丰')));
    assert(updateCount2 == 0);

    // 删除
    int delCount = await (database.delete(database.personTable)
          ..where((tbl) => tbl.id.equals(1)))
        .go();

    assert(delCount == 1);

    delCount = await (database.delete(database.personTable)
          ..where((tbl) => tbl.id.equals(2)))
        .go();

    assert(delCount == 0);

    // 分页查询
    list = await (database.select(database.personTable)..limit(10, offset: 0))
        .get();

    // 排序
    list = await (database.select(database.personTable)
          ..orderBy([
            (t) => OrderingTerm(expression: t.age, mode: OrderingMode.desc)
          ]))
        .get();

    // 关闭数据库连接
    await AppDatabase.closeDB();
  });
}

运行结果

在单元测试的情况下，drift打印的sql语句只在调试控制台可见