Flutter sqflite的使用-模型插入

前言

sqflite是一款轻量级的数据库，类似SQLite.
在Flutter平台我们使用sqflite库来同时支持Android 和iOS.
sqflite同时可以支持表结构升级.但是本篇不介绍表结构升级，而是使用整个模型插入方式实现。
表结构升级请点击点我

pubspec.yaml 导入库
#数据库
sqflite: ^1.3.0
#路径
path_provider: ^1.5.0

1、字段命名

final _version = 1;//数据库版本号
final _name = "DataBase.db";//数据库名称
final _tableUser = "tableUser";//表名称
final _primaryId   = "primaryId";//主键
final _data        = "data”;//模型内容

2、创建数据库、创建表

ATQueueData.internal();
//数据库句柄
static Database _database;
Future<Database> get database async {
  print("create database");
  if (_database == null) {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, _name);
    _database =  await openDatabase(path,version: _version);
    _createTable(_database);
    print("create database success");
  }
  return _database;
}
//创建表
void _createTable(Database db) async{
  final sql = '''create table if not exists $_tableUser ($_primaryId integer primary key,$_data text)''';
  var batch = db.batch();
  batch.execute(sql);
  await batch.commit()；
}

3、打开、关闭表

//打开
Future<Database> open() async{
  return await database;
}
///关闭
Future<void> close() async {
  var db = await database;
  return db.close();
}

4、插入数据

static Future insertData(int userId,String content) async{
  print(content);
  Database db = await ATQueueData.internal().open();
  db.transaction((txn) async{
    txn.rawInsert("insert or replace into $_tableUser ($_primaryId,$_data) values (?,?)",[userId,content]);
  });
  await db.batch().commit();
}

5、更新数据

static Future updateData(int userId,String content) async{
  Database db = await ATQueueData.internal().open();
  db.transaction((txn) async{
    txn.rawUpdate("update $_tableUser set $_data =  ? where $_primaryId = ?",[content,userId]);
  });
  await db.batch().commit();
}

6、删除数据

static Future deleteData(int userId) async{
  Database db = await ATQueueData.internal().open();
  db.transaction((txn) async{
    txn.rawDelete("delete from $_tableUser where $_primaryId = ?",[userId]);
  });
  await db.batch().commit();
}

7、查询数据

  static Future<Map> searchData(int userId) async {
    Database db = await ATQueueData.internal().open();
    List<Map<String, dynamic>> maps = await db.rawQuery("select * from $_table where $_primaryId = $userId");
    List datas = await decodeData(maps);
    return datas.first;
  }
  static Future<List> searchDatas() async {
    Database db = await ATQueueData.internal().open();
    List<Map<String, dynamic>> maps = await db.rawQuery("select * from $_tableUser");
    return await decodeData(maps);
  }
  static  Future<List> searchDataSize(int page,int size) async {
    Database db = await ATQueueData.internal().open();
    List<Map<String, dynamic>> maps = await db.rawQuery("select * from $_table order by $_primaryId asc limit ?,?",[(page - 1) * size,size]);
    return await decodeData(maps);
  }
  static Future<List> decodeData(List listData) async{
    List datas = List();
    listData.forEach((element) {
      var params = element["$_data"];
      datas.add(params);
    });
    return await datas;
  }

7、使用方式

//增
ATUser user = ATUser(userId: 10001,nickName: "吕布",mobile: "15160023363",sex: "男",age: 40);
ATQueueData.insertData(user.userId, user.toJson().toString());
//删
ATQueueData.deleteData(10000);
//改
ATUser user = ATUser(userId: 10000,nickName: "诸葛亮春福",mobile: "15280592811",sex: "男",age: 50);
ATQueueData.updateData(10000, user.toJson().toString());
//查
ATQueueData.searchDatas()

8、ATUser类的定义

class ATUser{
  int userId;
  String nickName;
  String mobile;
  String sex;
  int age;
  ATUser({this.userId, this.nickName, this.mobile, this.sex,this.age});
  ATUser.fromJson(Map<String, dynamic> json) {
    userId = json['userId'];
    age = json['age'];
    nickName = json['nickName'];
    mobile = json['mobile'];
    sex = json['sex'];
  }
  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data['userId'] = this.userId;
    data['age'] = this.age;
    data['nickName'] = this.nickName;
    data['mobile'] = this.mobile;
    data['sex'] = this.sex;
    return data;
  }
}