Flutter数据库管理插件sqlcool的使用

Sqlcool 是一个用于 Flutter 的数据库助手库，基于 Sqflite。它帮助开发者专注于业务逻辑，而无需关心底层实现细节。

特性

• 简单：提供简单的 CRUD 操作 API。
• 响应式：支持数据库变更流和响应式查询。
• 可适配：可以将自定义模型与数据库集成。

更多详细信息请参考 官方文档 或 API 文档。

简单的 CRUD 操作

定义数据库模式

import 'package:sqlcool/sqlcool.dart';

Db db = Db();
// 定义数据库表结构
DbTable category = DbTable("category")..varchar("name", unique: true);
DbTable product = DbTable("product")
   ..varchar("name", unique: true)
   ..integer("price")
   ..text("description", nullable: true)
   ..foreignKey("category", onDelete: OnDelete.cascade)
   ..index("name");
List<DbTable> schema = [category, product];

初始化数据库

String dbPath = "db.sqlite"; // 数据库文件路径（相对于文档目录）
try {
  await db.init(path: dbPath, schema: schema);
} catch(e) {
  rethrow;
}

插入数据

final Map<String, String> row = {"name": "My Item"};
try {
  int id = await db.insert(table: "category", row: row);
} catch(e) {
  rethrow;
}

查询数据

try {
  List<Map<String, dynamic>> rows = await db.select(
    table: "product",
    limit: 20,
    columns: "id,name",
    where: "name LIKE '%something%'",
    orderBy: "name ASC",
  );
} catch (e) {
  rethrow;
}

更新数据

try {
  int numRowsUpdated = await db.update(table: "category", 
   row: row, where: "id=1");
} catch(e) {
  rethrow;
}

删除数据

try {
  await db.delete(table: "category", where: "id=3");
} catch(e) {
  rethrow;
}

联合查询

try {
  final data = await db.join(
   table: "product",
   columns: "product.name,price,category.name as category_name",
   joinTable: "category",
   joinOn: "product.category=category.id");
} catch(e) {
  rethrow;
}

多表联合查询

try {
  final data = db.mJoin(table: "product", joinsTables: [
   "category",
   "manufacturer"
 ], joinsOn: [
   "product.category=category.id",
   "product.manufacturer=manufacturer.id"
 ]);
} catch(e) {
  rethrow;
}

响应式功能

变更流（Changefeed）

import 'dart:async';
import 'package:sqlcool/sqlcool.dart';

StreamSubscription changefeed;

changefeed = db.changefeed.listen((change) {
   print("Change in the database:");
   print("Query: ${change.query}");
   if (change.type == DatabaseChange.update) {
     print("${change.value} items updated");
   }
 });
// 停止监听时取消订阅
changefeed.cancel();

响应式查询（Reactive Select Bloc）

import 'package:flutter/material.dart';
import 'package:sqlcool/sqlcool.dart';

class _PageSelectBlocState extends State<PageSelectBloc> {
  SelectBloc bloc;

  [@override](/user/override)
  void initState() {
    super.initState();
    this.bloc = SelectBloc(
        table: "items", orderBy: "name", reactive: true);
  }

  [@override](/user/override)
  void dispose() {
    bloc.dispose();
    super.dispose();
  }

  [@override](/user/override)
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text("My App")),
      body: StreamBuilder<List<Map>>(
          stream: bloc.items,
          builder: (BuildContext context, AsyncSnapshot snapshot) {
            if (snapshot.hasData) {
              // 查询结果为空
              if (snapshot.data.length == 0) {
                return Center(child: const Text("No Data"));
              }
              // 查询结果有数据
              return ListView.builder(
                  itemCount: snapshot.data.length,
                  itemBuilder: (BuildContext context, int index) {
                    var item = snapshot.data[index];
                    return ListTile(
                      title: GestureDetector(
                        child: Text(item["name"]),
                        onTap: () => someFunction()),
                    );
                  });
            } else {
              // 查询正在进行中
              return CircularProgressIndicator();
            }
          }),
    );
  }
}

class PageSelectBloc extends StatefulWidget {
  [@override](/user/override)
  _PageSelectBlocState createState() => _PageSelectBlocState();
}

使用数据库模型

定义数据库模型

在 schema.dart 中定义表结构：

final carTable = DbTable("car")
  ..varchar("name")
  ..integer("max_speed")
  ..real("price")
  ..integer("year")
  ..boolean("is_4wd", defaultValue: false)
  ..foreignKey("manufacturer", onDelete: OnDelete.cascade);

final manufacturerTable = DbTable("manufacturer")..varchar("name");

在 car_model.dart 中定义模型类：

import 'package:sqlcool/sqlcool.dart';
import 'schema.dart';
import 'manufacturer_model.dart';

class Car with DbModel {
  Car({
    this.id,
    this.name,
    this.maxSpeed,
    this.price,
    this.year,
    this.is4wd,
    this.manufacturer,
  });

  final String name;
  final int maxSpeed;
  final double price;
  final DateTime year;
  final bool is4wd;
  Manufacturer manufacturer;

  [@override](/user/override)
  int id;

  [@override](/user/override)
  Db get db => db;

  [@override](/user/override)
  DbTable get table => carTable;

  [@override](/user/override)
  Map<String, dynamic> toDb() {
    assert(manufacturer?.id != null);
    return {
      "name": name,
      "max_speed": maxSpeed,
      "price": price,
      "year": year.millisecondsSinceEpoch,
      "is_4wd": is4wd,
      "manufacturer": manufacturer.id,
    };
  }

  [@override](/user/override)
  Car fromDb(Map<String, dynamic> map) {
    return Car(
      id: map["id"] as int,
      name: map["name"].toString(),
      maxSpeed: map["max_speed"] as int,
      price: map["price"] as double,
      year: DateTime.fromMillisecondsSinceEpoch(map["year"] as int),
      is4wd: (map["is_4wd"].toString() == "true"),
    );
  }

  static Future<List<Car>> selectRelated({String where, int limit}) async {
    final cars = List<Car>.from(await Car().sqlJoin(where: where, limit: limit));
    return cars;
  }
}

使用模型

await car.sqlInsert();
await car.sqlUpdate();
await car.sqlUpsert();
await car.sqlDelete();

final cars = Car.selectRelated(where: "speed>200");
print(cars[0].manufacturer.name);

示例代码

以下是一个完整的示例代码，展示了如何初始化数据库并使用 Sqlcool 进行操作：

import 'package:flutter/material.dart';

import 'conf.dart';
import 'dbmodels/dbmodels.dart';
import 'dbviewer/dbviewer.dart';
import 'init_db.dart';
import 'pages/index.dart';
import 'pages/join.dart';
import 'pages/select_bloc.dart';
import 'pages/upsert.dart';

void main() {
  runApp(MyApp());

  // 异步初始化数据库
  initDb(db: db);
  initDb2(db: db2);
}

final routes = {
  '/': (BuildContext context) => PageIndex(),
  '/select_bloc': (BuildContext context) => PageSelectBloc(),
  '/join': (BuildContext context) => PageJoinQuery(),
  '/upsert': (BuildContext context) => UpsertPage(),
  '/dbmodel': (BuildContext context) => DbModelPage(),
  '/dbmanager': (BuildContext context) => DbViewer(db: db),
};

class MyApp extends StatelessWidget {
  [@override](/user/override)
  Widget build(BuildContext context) {
    return MaterialApp(
      debugShowCheckedModeBanner: false,
      title: 'Sqlcool Example',
      routes: routes,
    );
  }
}