Flutter——SQLite数据库的使用与封装

导入依赖

 #数据库
  sqflite: ^2.2.0+3

创建实体类

根据自己的所要存储的内容创建一个实体类，并构建Map映射存取两个方法

class MovieFavorite{
  late String doubanId;//豆瓣id
  late String moviePoster;//电影海报
  late String movieName;//电影名称
  late String movieCountry;//电影国家
  late String movieLanguage;//电影语言
  late String movieGenre;//电影类型
  late String movieDescription; //电影描述

  MovieFavorite(
      this.doubanId,
      this.moviePoster,
      this.movieName,
      this.movieCountry,
      this.movieLanguage,
      this.movieGenre,
      this.movieDescription
      );

  MovieFavorite.fromJson(dynamic json){
    doubanId = json['doubanId'];
    moviePoster = json['MoviePoster'];
    movieName = json['MovieName'];
    movieCountry = json['MovieCountry'];
    movieLanguage = json['MovieLanguage'];
    movieGenre = json['MovieGenre'];
    movieDescription = json['MovieDescription'];
  }

  Map<String,dynamic> toJson(){
    final map = <String,dynamic>{};
    map['doubanId'] = doubanId;
    map['MoviePoster'] = moviePoster;
    map['MovieName'] = movieName;
    map['MovieCountry'] = movieCountry;
    map['MovieLanguage'] = movieLanguage;
    map['MovieGenre'] = movieGenre;
    map['MovieDescription'] = movieDescription;
    return map;
  }
}

建表

下面是创建了俩个数据库表

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';


class DBManager{
  final int _version = 4;//版本号
  final String _databaseName = 'Movie.db';//数据库名称

  ///收藏表
  static const String favoriteTable = 'FavoriteTable';//表名
  static const String doubanId = 'doubanId';//primary key
  static const String _moviePoster = 'MoviePoster';//电影海报
  static const String _movieName = 'MovieName';//电影名称
  static const String _movieCountry = 'MovieCountry';//电影国家
  static const String _movieLanguage = 'MovieLanguage';//电影语言
  static const String _movieGenre = 'MovieGenre';//电影类型(可能为空)
  static const String _movieDescription = 'MovieDescription';//电影描述

  ///搜索记录表
  static const String searchTable = 'SearchTable';//表名
  static const String searchKey = 'SearchKey';


  static DBManager? _instance;
  static DBManager getInstance() => _instance ??= DBManager();

  static Database? _database;
  Future<Database> get getDatabase async => _database ??= await _initSQl();

  ///初始化数据库
  Future<Database> _initSQl() async{
    var dbPath = await getDatabasesPath();
    var path = join(dbPath,_databaseName);
    return await openDatabase(path,version: _version,onCreate: _onCreate);
  }

  ///创建表
  Future _onCreate(Database db,int version) async{
    String favoriteSQL =
      '''
      CREATE TABLE $favoriteTable(
      $doubanId TEXT PRIMARY KEY,
      $_moviePoster TEXT,
      $_movieName TEXT,
      $_movieCountry TEXT,
      $_movieLanguage TEXT,
      $_movieGenre TEXT,
      $_movieDescription TEXT
      )
      ''';

    String searchSQL =
      '''
      CREATE TABLE $searchTable(
      $searchKey TEXT PRIMARY KEY
      )
      ''';

    await db.execute(favoriteSQL);
    await db.execute(searchSQL);
  }
}

封装Dao

然后对增删改查四个功能进行单独封装

class FavoriteDao{

  static FavoriteDao? _instance;
  static FavoriteDao getInstance() => _instance ??= FavoriteDao();

  ///插入数据
  Future<int> insert(MovieFavorite bean) async{
    Database db = await DBManager.getInstance().getDatabase;
    return await db.insert(DBManager.favoriteTable, bean.toJson());
  }

  ///修改数据
  // Future<int> update() async{
  //   Database db = await database;
  //   await db.update(table, values)
  // }

  ///删除数据
  Future<int> delete(String doubanId) async{
    Database db = await DBManager.getInstance().getDatabase;
    return await db.delete(DBManager.favoriteTable,where:'${DBManager.doubanId} = ?',whereArgs:[doubanId]);
  }

  ///删除全部数据
  Future<int> deleteAll() async{
    Database db = await DBManager.getInstance().getDatabase;
    return await db.delete(DBManager.favoriteTable);
  }

  ///查询数据
  Future<List<MovieFavorite>> query(String doubanId) async {
    Database db = await DBManager.getInstance().getDatabase;
    var result = await db.query(DBManager.favoriteTable, where: '${DBManager.doubanId} = ?', whereArgs: [doubanId]);
    if (result.isNotEmpty) {
      return result.map((e) => MovieFavorite.fromJson(e)).toList();
    } else {
      return [];
    }
  }

  根据doubanId查询判断是否存在
  Future<bool> isExist(String doubanId) async {
    Database db = await DBManager.getInstance().getDatabase;
    var result = await db.query(DBManager.favoriteTable, where: '${DBManager.doubanId} = ?', whereArgs: [doubanId]);
    if (result.isNotEmpty) {
      return true;
    } else {
      return false;
    }
  }

  ///查询所有数据
  Future<List<MovieFavorite>> queryAll() async {
    Database db = await DBManager.getInstance().getDatabase;
    var result = await db.query(DBManager.favoriteTable);
    if (result.isNotEmpty) {
      return result.map((e) => MovieFavorite.fromJson(e)).toList();
    } else {
      return [];
    }
  }
}

使用

exeFavoriteInsert(MovieFavorite entity,BuildContext context) async {
  bool isExist = await FavoriteDao.getInstance().isExist(entity.doubanId);
  if(!isExist){
    int flag = await FavoriteDao.getInstance().insert(entity);
    final favoriteProvide = context.read<FavoriteProvider>();
    final List<MovieFavorite> list = [entity];
    favoriteProvide.favoriteList = list;

    if (flag > 0) {
      showSuccessToast('收藏成功!');
    } else {
      showFailedToast('收藏失败!');
    }
  }else{
    showFailedToast('此影片已被收藏，请勿重复添加!');
  }
}

isFavoriteInserted(String doubanId) async => await FavoriteDao.getInstance().isExist(doubanId);