Flutter对象关系映射插件needle_orm的使用

Flutter对象关系映射插件needle_orm的使用

支持的数据库

• ✅ PostgreSQL
• ✅ MariaDB（事务功能尚不可用）

支持的注解

• ✅ @Entity
• ✅ @Column
• ✅ @Transient
• ✅ @Table
• ✅ @ID
• ✅ @Lob
• ✅ @OneToOne
• ✅ @OneToMany
• ✅ @ManyToOne
• ❌ @ManyToMany
• ❌ @Index
• ❌ @OrderBy
• ✅ @Version

其他一些有用的注解，类似于 Ebean ORM for Java/Kotlin：

• ✅ @SoftDelete
• ✅ @WhenCreated
• ✅ @WhenModified
• ❌ @WhoCreated
• ❌ @WhoModified
• ✅ @PreInsert
• ✅ @PreUpdate
• ✅ @PreRemove
• ✅ @PreRemovePermanent
• ✅ @PostInsert
• ✅ @PostUpdate
• ✅ @PostRemove
• ✅ @PostRemovePermanent
• ✅ @PostLoad

定义模型

[@Entity](/user/Entity)()
abstract class _BaseModel {
  [@ID](/user/ID)()
  int? _id; // 主键

  [@Version](/user/Version)()
  int? _version; // 版本号

  [@SoftDelete](/user/SoftDelete)()
  bool? _deleted; // 软删除标志

  [@WhenCreated](/user/WhenCreated)()
  DateTime? _createdAt; // 创建时间

  [@WhenModified](/user/WhenModified)()
  DateTime? _updatedAt; // 更新时间

  [@WhoCreated](/user/WhoCreated)()
  String? _createdBy; // 创建者登录名

  [@WhoModified](/user/WhoModified)()
  String? _lastUpdatedBy; // 最后更新者登录名

  [@Column](/user/Column)()
  String? _remark; // 备注

  _BaseModel();
}

[@Table](/user/Table)(name: 'tbl_user')
[@Entity](/user/Entity)()
class _User extends _BaseModel {
  [@Column](/user/Column)()
  String? _name; // 用户名

  [@Column](/user/Column)()
  String? _loginName; // 登录名

  [@Column](/user/Column)()
  String? _address; // 地址

  [@Column](/user/Column)()
  int? _age; // 年龄

  [@OneToMany](/user/OneToMany)(mappedBy: "_author")
  List<_Book>? books; // 用户拥有的书籍

  _User();
}

[@Table](/user/Table)()
[@Entity](/user/Entity)()
class _Book extends _BaseModel {
  [@Column](/user/Column)()
  String? _title; // 书名

  [@Column](/user/Column)()
  double? _price; // 价格

  [@ManyToOne](/user/ManyToOne)()
  _User? _author; // 作者

  [@Lob](/user/Lob)()
  List<int>? _image; // 图片 (BLOB)

  [@Lob](/user/Lob)()
  String? _content; // 内容 (CLOB)

  _Book();
}

增强业务逻辑

extension Biz_User on User {
  bool isAdmin() {
    return name!.startsWith('admin');
  }

  void beforeInsert() {
    _version = 1;
    _deleted = false;
    print('before insert user ....');
  }

  void afterInsert() {
    print('after insert user ....');
  }
}

使用示例

Future<Database> initPostgreSQL() async {
  return PostgreSqlPoolDatabase(PgPool(
    PgEndpoint(
      host: 'localhost',
      port: 5432,
      database: 'appdb',
      username: 'postgres',
      password: 'postgres',
    ),
    settings: PgPoolSettings()
      ..maxConnectionAge = Duration(hours: 1)
      ..concurrency = 5,
  ));
}

void main() async {
  Database.register("dbPostgres", await initPostgreSQL());

  // 创建或更新用户
  {
    var user = User();
    user
      ..name = 'administrator'
      ..address = 'abc'
      ..age = 23
      ..save(); // 或者 insert()

    print('user saved, id= ${user.id}');

    user
      ..name = 'another name'
      ..save(); // 或者 update()

    // 调用业务方法
    print('is admin? ${user.isAdmin()}');

    // 转换为Map，也可以用于生成JSON
    var valueMap = user.toMap();
    // 或者只输出某些字段
    valueMap = user.toMap(fields:'id,name');

    // 从Map加载数据
    user.loadMap({"name": 'admin123', "xxxx": 'xxxx'});

    var book = Book();
    book
      ..author = user
      ..title = 'Dart'
      ..price = 14.99
      ..insert();

    // toMap支持嵌套字段：'author(id,name)'
    valueMap = book.toMap(fields:'id,title,price,author(id,name)');
  }

  // 类型查询
  {
    Book.query()
      ..title.startsWith('dart')
      ..price.between(10.0, 20.0)
      ..author.apply((author) {
        author
          ..age.ge(18)
          ..address.startsWith('China Shanghai');
      })
      ..orders = [Book.query().price.desc()]
      ..offset = 10
      ..maxRows = 20  // limit
      ..findList();
  }

  // 软删除
  {
    var q = Book.query()
      ..price.between(18, 19)
      ..title.endsWith('test');
    var total = await q.count();  // 不包括已删除记录
    var totalWithDeleted = await q.count(includeSoftDeleted: true);
    print('found books , total: $total, totalWithDeleted: $totalWithDeleted');

    int deletedCount = await q.delete();
    print('soft deleted books: $deletedCount');

    total = await q.count();
    totalWithDeleted = await q.count(includeSoftDeleted: true);
    print('found books after soft delete , total: $total, totalWithDeleted: $totalWithDeleted');
  }

  // 永久删除
  {
    var q = Book.query()
    ..price.between(100, 1000);
    var total = await q.count();

    print('found expensive books, total count: $total');

    int deletedCount = await q.deletePermanent();
    print('permanent deleted books : $deletedCount');
  }

  // 批量插入
  {
    var n = 10;
    var users = <User>[];
    for (int i = 0; i < n; i++) {
      var user = User()
        ..name = 'name_$i'
        ..address = 'China Shanghai street_$i'
        ..age = (n * 0.1).toInt();
      users.add(user);
    }
    print('users created');
    await User.query().insertBatch(users, batchSize: 5);
    print('users saved');
    var idList = users.map((e) => e.id).toList();
    print('ids: $idList');
  }

  // 在同一查询中的模型缓存
  {
    var user = User()..name = 'cach_name';
    await user.save();

    var book1 = Book()
      ..author = user
      ..title = 'book title1';
    var book2 = Book()
      ..author = user
      ..title = 'book title2';
    await book1.save();
    await book2.save();

    var q = Book.query()..id.IN([book1.id!, book2.id!]);
    var books = await q.findList();
    // books[0].author 应该与 books[1].author 相同
    print('used cache? ${books[0].author == books[1].author}');
  }

  // 事务：仅在PostgreSQL上工作，在MariaDB上仍存在问题
  {
    var q = User.query();
    print('count before insert : ${await q.count()}');
    var db2 = await initPostgreSQL();
    await db2.transaction((db) async {
      var n = 50;
      for (int i = 1; i < n; i++) {
        var user = User()
          ..name = 'tx_name_$i'
          ..address = 'China Shanghai street_$i ' * i
          ..age = n;
        await user.save(db: db); // 第10次循环时因为地址太长而抛出回滚异常
        print('\t used saved with id: ${user.id}');
      }
    });

    // 因为回滚异常，下一行永远不会执行。
    // print('count after insert : ${await q.count()}');
  }
}