flutter floor数据库的简单使用

1. 引入floor插件
   在pubspec.yaml中引入floor插件
   image.png
2. 创建数据库实体类，实体类在数据库里就代表一张表
   下面以我的实体类为例，注解无需改动，改动实体类内容即可

import 'package:floor/floor.dart';

@entity
class Beacon {
  @PrimaryKey(autoGenerate: true)
  int? id;
  String? major;
  String? minor;
  double? dx;
  double? dy;
  String? name;
  String? floorName;
  bool? indoor = true;
  int? gid;
  int? userid;
  String? projectionId;
  String? floorid;
  // 是否有修改
  bool? modified = false;
  String? mapId;
  bool? newAdd; // 新增标志位
  bool? edit; // 编辑标志位
  bool? deleted; // 删除标志位
  int? sendFlag = 1;
  String? deviceId;

  void clear() {
    major = null;
    minor = null;
    dx = 0.0;
    dy = 0.0;
    name = null;
    floorName = null;
    gid = 1;
    floorid = null;
    modified = false;
    id = null;
    deviceId = null;
  }

  Beacon(
      {this.id,
      this.major,
      this.minor,
      this.dx,
      this.dy,
      this.name,
      this.floorName,
      this.floorid,
      this.indoor,
      this.gid,
      this.userid,
      this.projectionId,
      this.modified,
      this.mapId,
      this.newAdd,
      this.deleted,
      this.edit,
      this.sendFlag,
      this.deviceId});

  factory Beacon.fromJson(Map<String, dynamic> json) {
    String? major = json['deviceAttributes']['major'] as String?;
    String? minor = json['deviceAttributes']['minor'] as String?;
    double dx = (json['deviceAttributes']['x'] as num).toDouble();
    double dy = (json['deviceAttributes']['y'] as num).toDouble();
    bool indoor =
        json['deviceAttributes']['place'] as String == '室内' ? true : false;
    String projectionId = json['proId'] as String;
    String mapId = json['mapId'] as String;
    int userid = json['userId'] as int;
    String name = json['deviceName'] ?? '';
    int gid = json['level'] ?? 1;
    String floorid = json['floorId'] as String;
    bool modified = false;
    String deviceid = json['deviceId'];
    Beacon beacon = Beacon(
        gid: gid,
        major: major,
        minor: minor,
        dx: dx,
        dy: dy,
        indoor: indoor,
        projectionId: projectionId,
        mapId: mapId,
        userid: userid,
        name: name,
        floorid: floorid,
        modified: modified,
        deviceId: deviceid);

    return beacon;
  }

  Map<String, dynamic> toJson() => {
        'gid': this.gid,
        'id': this.id,
        'major': this.major,
        'minor': this.minor,
        'dx': this.dx,
        'dy': this.dy,
        'name': this.name,
        'floorName': this.floorName,
        'floorid': this.floorid,
        'indoor': this.indoor,
        'userid': this.userid,
        'projecetionId': this.projectionId,
        'modified': this.modified,
        'mapId': this.mapId,
        'newAdd': this.newAdd,
        'edit': this.edit,
        'sendFlag': this.sendFlag,
        'deviceId': this.deviceId,
        'deleted': this.deleted
      };
}

这样在后续数据库中就会生成一张以Beacon命名的表，以id作为主键自增
再给大家看一下生成的数据库的形式

image.png

3. 生成操作数据库的类
   数据库简单的增删改查，同样附上代码，注解无需改动，只需要改动sql语句内容即可，sql语句中Beacon是表名称

import 'package:floor/floor.dart';
import 'package:fm_location/db/beacon_db.dart';

@dao
abstract class BeaconDao {
  @Query('SELECT * FROM Beacon')
  Future<List<Beacon>> findAllBeacons();

  @Query('SELECT * FROM Beacon WHERE projectionId = :projectionId')
  Future<List<Beacon>> findBeaconsByProjectionId(String projectionId);

  @Query('SELECT * FROM Beacon WHERE id = :id')
  Future<Beacon?> findBeaconById(int id);

  @Query('SELECT * FROM Beacon WHERE modified = :modified')
  Future<List<Beacon>> findBeaconsByModified(bool modified);

  @Query('SELECT * FROM Beacon WHERE floorId = :floorId')
  Future<List<Beacon>> findBeaconsByFloorId(String floorId);

  @Query('SELECT * FROM Beacon WHERE major = :major AND minor = :minor')
  Future<Beacon?> findBeaconsByMajorMinor(String major, String minor);

  @Query('SELECT * FROM Beacon WHERE id = :id')
  Future<Beacon?> findBeaconsById(int id);

  @Query(
      'SELECT * FROM Beacon WHERE name LIKE :beaconName AND projectionId = :projId AND floorId = :floorId')
  Future<List<Beacon>> findBeaconsByBeaconName(
      String beaconName, String projId, String floorId);

  @Query(
      'SELECT * FROM Beacon WHERE modified = :modified AND floorId = :floorid')
  Future<List<Beacon>> findBeaconsByFloorid(String floorid, bool modified);

  @Query(
      'SELECT * FROM Beacon WHERE modified = :modified AND projectionId = :projectionId')
  Future<List<Beacon>> findBeacons(String projectionId, bool modified);

// 找到所有新增的beacon
  @Query(
      'SELECT * FROM Beacon WHERE newAdd = true AND projectionId = :projectionId')
  Future<List<Beacon>> findNewAddBeacons(String projectionId);

// 找到项目下所有编辑的beacon
  @Query(
      'SELECT * FROM Beacon WHERE edit = true AND projectionId = :projectionId')
  Future<List<Beacon>> findEditBeacons(String projectionId);
// 找到项目下所有被标记为删除的beacon
  @Query(
      'SELECT * FROM Beacon WHERE deleted = true AND projectionId = :projectionId')
  Future<List<Beacon>> findDeletedBeacons(String projectionId);

  @Query('DELETE FROM Beacon WHERE projectionId = :projectionId')
  Future<void> deleteBeaconByProjId(String projectionId);

  @Query('DELETE FROM Beacon WHERE id = :id')
  Future<void> deleteBeaconById(int id);

  // 查找最后一条数据
  @Query('SELECT * from Beacon order by id desc limit 1')
  Future<Beacon?> queryLatestBeacon();

  @Query('DELETE FROM Beacon')
  Future<void> clear();

  @insert
  Future<int> insertBeacon(Beacon beacon);

  @update
  Future<int> updateBeacon(Beacon beacon);

  @insert
  Future<List<int>> insertBeacons(List<Beacon> beacons);

  @update
  Future<int> updateBeacons(List<Beacon> beacons);

  @delete
  Future<int> deleteBeacon(Beacon beacon);

  @delete
  Future<void> deleteBeacons(List<Beacon> beacons);
}

以上操作基本覆盖增删改查，按照自己数据库格式写就行

4. database类,这个类按照示例直接写就可以，不需要太多其他操作，part 'beacon_database.g.dart';也正常写

import 'dart:async';
import 'package:floor/floor.dart';
import 'package:sqflite/sqflite.dart' as sqflite;

import './beacon_dao.dart';
import './beacon_db.dart';

part 'beacon_database.g.dart'; // the generated code will be there

@Database(version: 1, entities: [Beacon])
abstract class AppDatabase extends FloorDatabase {
  BeaconDao get beaconDao;
}

5. 在终端执行flutter packages pub run build_runner build或者flutter packages pub run build_runner watch命令，两者的区别就在于前者只执行一次，后者在每次保存时都会执行，用于频繁修改数据库类操作，成功执行后会在目录里生成一个xxx.g.dart文件，至此我们就可以开始使用数据库了，操作完成后会有以下几个文件
   image.png
6. 为了方便使用，我又简单封装了一下数据库操作类

import 'package:fm_location/db/beacon_dao.dart';
import 'package:fm_location/db/beacon_database.dart';
import 'package:fm_location/utils/file_mixin.dart';
import 'beacon_db.dart';

class DBManager with FileMixin {
  late BeaconDao _beaconDao;

  // 数据库版本
  static const int _VERSION = 1;
  // 数据库名称
  static const String _DB_NAME = 'beacons.db';

  static DBManager? _instance;

  factory DBManager() => _getInstance();

  DBManager._internal() {
    createDataBase();
  }

  static DBManager _getInstance() {
    if (_instance == null) {
      _instance = DBManager._internal();
    }
    return _instance!;
  }

  // 创建数据库
  Future<void> createDataBase() async {
    final database = await $FloorAppDatabase.databaseBuilder(_DB_NAME).build();
    _beaconDao = database.beaconDao;
  }

  Future<List<Beacon>> findAllBeacons() async {
    return await _beaconDao.findAllBeacons();
  }

  //查找一个项目下所有beacon
  Future<List<Beacon>> queryBeaconByProjectionId(String projectionId) async {
    return await _beaconDao.findBeaconsByProjectionId(projectionId);
  }

  // 查找一个项目下所有被修改的beacon
  Future<List<Beacon>> queryBeacon(String projectionId, bool modified) async {
    List<Beacon> beacons = await _beaconDao.findBeacons(projectionId, modified);
    return beacons;
  }

// 添加一个beacon
  Future<int> addBeacon(Beacon beacon) async {
    return await _beaconDao.insertBeacon(beacon);
  }

  Future<void> insertBeacons(List<Beacon> beacons) async {
    await _beaconDao.insertBeacons(beacons);
  }

  // 批量删除beacon
  Future<void> deleteBeacons(List<Beacon> beacons) async {
    await _beaconDao.deleteBeacons(beacons);
  }

  // 删除单个beacon
  Future<void> deleteBeacon(Beacon beacon) async {
    await _beaconDao.deleteBeacon(beacon);
  }

  // 根据ID删除单个beacon
  Future<void> deleteBeaconById(int id) async {
    await _beaconDao.deleteBeaconById(id);
  }

  // 删除一个项目下所有beacon
  Future<void> deleteBeaconByProid(String projID) async {
    await _beaconDao.deleteBeaconByProjId(projID);
  }

  // 模糊查询
  Future<List<Beacon>> queryBeaconByName(
      String name, String projId, String floorId) async {
    return await _beaconDao.findBeaconsByBeaconName(name, projId, floorId);
  }

  // 查询一个楼层下被修改或未修改beacon
  Future<List<Beacon>> queryBeaconsByFloorid(String floorid,
      [bool query = false]) async {
    if (query) {
      return await _beaconDao.findBeaconsByFloorId(floorid);
    } else {
      return await _beaconDao.findBeaconsByFloorid(floorid, true);
    }
  }

  // 找到一个项目中所有新增的beacon
  Future<List<Beacon>> queryNewAddBeaconsByProjId(String projId) async {
    return await _beaconDao.findNewAddBeacons(projId);
  }

  // 找到一个项目中所有被编辑的beacon
  Future<List<Beacon>> queryEditBeaconsByProjId(String projId) async {
    return await _beaconDao.findEditBeacons(projId);
  }

  // 找到一个项目中所有被标记为删除的beacon
  Future<List<Beacon>> queryDeletedBeaconsByProjId(String projId) async {
    return await _beaconDao.findDeletedBeacons(projId);
  }

  // 清空数据库
  Future<void> clear() async {
    await _beaconDao.clear();
  }

  // 更新beacon
  Future<int> updateBeacon(Beacon beacon) async {
    return await _beaconDao.updateBeacon(beacon);
  }

// 查找刚刚操作的beacon数据
  Future<Beacon?> queryLatestBeacon([int? beaconId]) async {
    if (beaconId != null) {
      return await _beaconDao.findBeaconById(beaconId);
    } else {
      return await _beaconDao.queryLatestBeacon();
    }
  }

  // 按照major minor查找beacon
  Future<Beacon?> queryBeaconByMajorMinor(String major, String minor) async {
    return await _beaconDao.findBeaconsByMajorMinor(major, minor);
  }

  // 用ID查找beacon
  Future<Beacon?> queryBeaconById(int id) async {
    return await _beaconDao.findBeaconById(id);
  }
}