Flutter 持久化存储之数据库存储进阶
前言
在移动端，持久化存储需求越来越普遍，无论是单机模式还是配置数据，都离不开存储，SharedPreferences可以解决大部分的问题，但是比较复杂的数据就很难处理了，这里使用关系型数据库SQLite，Flutter中也有对SQLite数据库的插件sqflite，经过长期测试，其实Flutter中使用SQLite还是有很多问题，这里整理一下使用方法，尽量规避这些问题。
程序架构图
使用方法
SQLite官方网址 SQLite Home Page，语法和数据类型可以在官方搜索，都有使用示例。
1. 首先，在pubspec.yaml中引用插件，可以先使用sqflite: any，下载后在pubspec.lock找到对应的版本号，再修改为sqflite: ^xxxxx，为了稳定性，版本号一般要提前定好，避免因插件版本更新导致程序出现问题。
#数据库
sqflite: ^1.3.2+42. 定义操作帮助类SqlProvider，这里只有openDatabase，没有close，因为插件并不能很好的控制数据库的开关，经常出现未关闭的情况导致程序错误，综合考虑，一直开启。直接注册到全局变量Global类，方便业务层调用。
// ignore_for_file: file_names, import_of_legacy_library_into_null_safe, depend_on_referenced_packages, slash_for_doc_comments, unnecessary_null_comparison
import 'package:hsware_admin_app1/models/test_table1_model.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
class SqlProvider {
Database? database;
static const dbName = 'myDb.db';
//初始化
open() async {
var databasePath = await getDatabasesPath();
String path = join(databasePath, dbName);
database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
// When creating the db, create the table
await db.execute(TestTable1Model.createTableSql);
});
}
//判断表是否存在
isTableExits(String tableName) async {
//内建表sqlite_master
var sql =
"SELECT * FROM sqlite_master WHERE TYPE = 'table' AND NAME = '$tableName'";
List<Map<String, dynamic>>? res = await database?.rawQuery(sql);
var returnRes = res != null && res.isNotEmpty;
return returnRes;
}
/**
* * description: query查询所有
* * param1 tableName:表名
* * param2 listColumns:列集合
* * return {List<Map<String, dynamic>>}
*/
query(String tableName, List<String> listColumns) async {
//var myColumns = ['name', 'type'];
var result = await database?.query(tableName, columns: listColumns);
return result;
}
/**
* * description: query根据条件查询
* * param1 tableName:表名
* * param2 listColumns:列集合
* * param3 where:条件，'Id = ?'
* * param4 whereArgs:条件值，var myColumns = ['name', 'type'];
* * return {List<Map<String, dynamic>>}
*/
queryByWhere(String tableName, List<String> listColumns, String where,
List<dynamic> whereArgs) async {
//var myColumns = ['name', 'type'];
var result = await database?.query(tableName,
columns: listColumns, where: where, whereArgs: whereArgs);
return result;
}
/**
* * description: 根据sql语句查询数据
* * return {sql语句}
*/
queryBysql(String sql) async {
return await database?.rawQuery(sql);
}
/**
* * description: insert
* * param1 tableName:表名
* * param2 values:列集合
* * return {dynamic }
*/
insert(String tableName, Map<String, dynamic> values) async {
//var values = {'name':'my_name','type':'my_type'};
await database?.insert(tableName, values);
}
/**
* * description: 更新
* * param1 sql:语句，String update_sql = "UPDATE user SET username = ? WHERE id = ?";
* * param2 arguments:列值，['paul','1']
* * return {dynamic}
*/
rawUpdate(String sql, List<dynamic> arguments) async {
await database?.rawUpdate(sql, arguments);
}
/**
* * description: delete
* * param1 tableName:表名
* * param2 where:条件，'Id = ?'
* * param3 whereArgs:条件值，var myColumns = ['name', 'type'];
* * return {dynamic}
*/
delete(String tableName, String where, List<dynamic> whereArgs) async {
//var myWhere = 'name = ?';
//var myArgs = ['cat'];
await database?.delete(tableName, where: where, whereArgs: whereArgs);
}
/**
* * description: deleteAll
* * param1 tableName:表名
* * return {dynamic}
*/
deleteAll(String tableName) async {
await database?.delete(tableName);
}
///关闭
// close() async {
// database?.close();
// database = null;
// }
}3. 实体类TestTable1Model
注意点：
3.1 如果使用json转dart工具，将fromJson(dynamic json) 修改为fromJson(Map<String, dynamic> json)，只有使用生成dart工具才会注意这个问题。
3.2 todo 修改实体，这部分的内容只有该实体要持久化存储时才会配置，分别是表名、列名、查询列名集合、创建表语句。
// ignore_for_file: file_names
// ignore: slash_for_doc_comments
/**
* * Author: Hou
* * Date: 2022-08-25 14:29:02
* * LastEditors: Hou
* * LastEditTime: 2022-09-29 18:08:00
* * Description: Table1测试实体
* * [属性]:
* *
* ```dart
* ///pkid
* num? _id;
* ///列1
* String? _column1;
* ///列2
* String? _column2;
* ///列3
* String? _column3;
* ///列4
* String? _column4;
* ///列5
* String? _column5;
* ///列6
* String? _column6;
* ///列7
* String? _column7;
* ///列8
* String? _column8;
* ///列9
* String? _column9;
* ///列10
* String? _column10;
* ```
* */
class TestTable1Model {
TestTable1Model(
{num? id,
String? column1,
String? column2,
String? column3,
String? column4,
String? column5,
String? column6,
String? column7,
String? column8,
String? column9,
String? column10}) {
_id = id;
_column1 = column1;
_column2 = column2;
_column3 = column3;
_column4 = column4;
_column5 = column5;
_column6 = column6;
_column7 = column7;
_column8 = column8;
_column9 = column9;
_column10 = column10;
}
TestTable1Model.fromJson(Map<String, dynamic> json) {
_id = json['Id'];
_column1 = json['Column1'];
_column2 = json['Column2'];
_column3 = json['Column3'];
_column4 = json['Column4'];
_column5 = json['Column5'];
_column6 = json['Column6'];
_column7 = json['Column7'];
_column8 = json['Column8'];
_column9 = json['Column9'];
_column10 = json['Column10'];
}
///pkid
num? _id;
///列1
String? _column1;
///列2
String? _column2;
///列3
String? _column3;
///列4
String? _column4;
///列5
String? _column5;
///列6
String? _column6;
///列7
String? _column7;
///列8
String? _column8;
///列9
String? _column9;
///列10
String? _column10;
//todo 修改实体 start
// 定义数据库Mock模型
static String tableName = 'TestTable1';
//字段
static String columnId = 'Id';
static String columnColumn1 = 'Column1';
static String columnColumn2 = 'Column2';
static String columnColumn3 = 'Column3';
static String columnColumn4 = 'Column4';
static String columnColumn5 = 'Column5';
static String columnColumn6 = 'Column6';
static String columnColumn7 = 'Column7';
static String columnColumn8 = 'Column8';
static String columnColumn9 = 'Column9';
static String columnColumn10 = 'Column10';
//查询时的列
static List<String> listColumns = [
columnId,
columnColumn1,
columnColumn2,
columnColumn3,
columnColumn4,
columnColumn5,
columnColumn6,
columnColumn7,
columnColumn8,
columnColumn9,
columnColumn10
];
//建表语句
static String createTableSql = '''
CREATE TABLE If NOT EXISTS $tableName (
$columnId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
$columnColumn1 TEXT ,
$columnColumn2 TEXT,
$columnColumn3 TEXT,
$columnColumn4 TEXT,
$columnColumn5 TEXT,
$columnColumn6 TEXT,
$columnColumn7 TEXT,
$columnColumn8 TEXT,
$columnColumn9 TEXT,
$columnColumn10 TEXT
);
''';
//todo 修改实体 end
TestTable1Model copyWith(
{num? id,
String? column1,
String? column2,
String? column3,
String? column4,
String? column5,
String? column6,
String? column7,
String? column8,
String? column9,
String? column10}) =>
TestTable1Model(
id: id ?? _id,
column1: column1 ?? _column1,
column2: column2 ?? _column2,
column3: column3 ?? _column3,
column4: column4 ?? _column4,
column5: column5 ?? _column5,
column6: column6 ?? _column6,
column7: column7 ?? _column7,
column8: column8 ?? _column8,
column9: column9 ?? _column9,
column10: column10 ?? _column10,
);
num? get id => _id;
String? get column1 => _column1;
String? get column2 => _column2;
String? get column3 => _column3;
String? get column4 => _column4;
String? get column5 => _column5;
String? get column6 => _column6;
set setColumn6(String? value) {
_column6 = value;
}
String? get column7 => _column7;
String? get column8 => _column8;
String? get column9 => _column9;
String? get column10 => _column10;
Map<String, dynamic> toJson() {
final map = <String, dynamic>{};
//自动标识列
//map['Id'] = _id;
map['Column1'] = _column1;
map['Column2'] = _column2;
map['Column3'] = _column3;
map['Column4'] = _column4;
map['Column5'] = _column5;
map['Column6'] = _column6;
map['Column7'] = _column7;
map['Column8'] = _column8;
map['Column9'] = _column9;
map['Column10'] = _column10;
return map;
}
}4. 业务层使用示例
//查询所有数据
List<Map<String, dynamic>> listDb = await Global.sqlProvider
.query(TestTable1Model.tableName, TestTable1Model.listColumns);
//根据id查询实体，例如id为1001
List<Map<String, dynamic>> list = await Global.sqlProvider.queryByWhere(
TestTable1Model.tableName,
TestTable1Model.listColumns,
'Id = ?',
[1001]);
//删除数据，例如id为1001
await Global.sqlProvider.delete(TestTable1Model.tableName,'Id = ?', [1001]);
//新建
TestTable1Model model =TestTable1Model(
column1:"c1",
column2:"c2",
column3:"c3",
column4:"c4",
column5:"c5");
await Global.sqlProvider.insert(
TestTable1Model.tableName, model.toJson());
//修改
String updateSql =
"UPDATE ${TestTable1Model.tableName} "
"SET "
"Column1 = ?,"
"Column2 = ?,"
"Column3 = ?,"
"Column4 = ?,"
"Column5 = ?,"
"Column6 = ?"
" WHERE Id = ?";
await Global.sqlProvider.rawUpdate(updateSql, [
model.column1,
model.column2,
model.column3,
model.column4,
model.column5,
model.column6,
model.id
]);后续待优化
