Flutter|Flutter sqflite的使用-表结构升级 Fluttersqflite的使用-表结构升级

前言 sqflite是一款轻量级的数据库，类似SQLite.
在Flutter平台我们使用sqflite库来同时支持Android 和iOS.
sqflite同时可以支持表结构升级.
1、pubspec.yaml 导入库

#数据库 sqflite: ^1.3.0 #路径 path_provider: ^1.5.0

2、导入头文件 import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart’;
3、常见字段的定义

//数据库版本号用于控制表结构升级 final _version = 2; //数据库名称 final _name = "DataBase.db"; //表名称 final _table = "SearchTable"; //表字段 final _userId= "userId"; final _nickName = "nickName"; final _mobile= "mobile"; //新字段用于表结构升级 final _sex= "sex”;

4、创建数据库_name

ATQueue.internal(); //数据库句柄 static Database _database; Future get database async { if (_database == null) { var databasesPath = await getDatabasesPath(); String path = join(databasesPath, _name); _database =await openDatabase(path,version: _version,onCreate: _onCreate,onUpgrade:_onUpgrade); } return _database; }

5、创建表_table

//创建表 void _onCreate(Database db,int version) async{ print("createTable version $version"); final sql = ''' create table if not exists $_table ( $_userIdinteger primary key, $_nickName text not null, $_mobile text not null, $_sextext) '''; var batch = db.batch(); batch.execute(sql); await [batch.commit(); ](http://batch.commit(); /) } //升级表结构如果有新字段加入比如这里的sex ，则旧的表需要升级表结构 void _onUpgrade(Database db, int oldVersion, int newVersion) async{ if (oldVersion < 2){ var batch = db.batch(); print("updateTable version $oldVersion $newVersion"); batch.execute('alter table $_table add column $_sex text'); await [batch.commit(); ](http://batch.commit(); /) } //if (oldVersion < 2){ //var batch = db.batch(); //try{ //final sal = 'select $_sex from $_table'; //await db.rawQuery(sal); //}catch (error){ //print(error); //print("======================"); //batch.execute('alter table $_table add column $_sex text'); //await [batch.commit(); ](http://batch.commit(); /) ////新增一列名称sex //} //} }

6、打开，关闭数据库

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

7、数据插入普通插入/事务插入

static Future insertData(int userId,String nickName,String mobile,{String sex = "男"}) async{ Database db = await ATQueue.internal().open(); //1、普通插入 //await db.rawInsert("insert or replace into $_table ($_userId,$_nickName,$_mobile,$_sex) values (?,?,?,?)",[userId,nickName,mobile,sex]); //2、事务插入 db.transaction((txn) async{ txn.rawInsert("insert or replace into $_table ($_userId,$_nickName,$_mobile,$_sex) values (?,?,?,?)",[userId,nickName,mobile,sex]); }); await db.batch().commit(); }

8、数据修改普通提交/事务提交

static Future updateData(int userId,String nickName,String mobile) async{ Database db = await ATQueue.internal().open(); //1、普通插入 // await db.rawUpdate("update $_table set $_nickName =?,$_mobile =? where $_userId = ?",[nickName,mobile,userId]); //2、事务插入 db.transaction((txn) async{ txn.rawUpdate("update $_table set $_nickName =?,$_mobile =? where $_userId = ?",[nickName,mobile,userId]); }); await db.batch().commit(); }

9、数据删除普通提交/事务提交

static Future deleteData(int userId) async{ Database db = await ATQueue.internal().open(); //1、普通提交 //await db.rawDelete("delete from $_table where $_userId = ?",[userId]); //2、事务提交 db.transaction((txn) async{ txn.rawDelete("delete from $_table where $_userId = ?",[userId]); }); await db.batch().commit(); }

10、数据查询单体查询所有查询分页查询

static Future searchData(int userId) async { Database db = await ATQueue.internal().open(); List maps = await db.rawQuery("select * from $_table where $_userId = $userId"); print(maps); return maps; } static Future searchDatas() async { Database db = await ATQueue.internal().open(); List maps = await db.rawQuery("select * from $_table"); print(maps); return maps; } static Future searchDataSize(int page,int size) async { Database db = await ATQueue.internal().open(); List maps = await db.rawQuery("select * from $_table order by $_userId asc limit ?,?",[(page - 1) * size,size]); print(maps); return maps; }

11、使用方式

ATUserQueue.insertData(110, "关于", "15160023363”); ATUserQueue.updateData(110, "诸葛亮春福", "15280592811”); ATUserQueue.searchDatas(); ATUserQueue.deleteData(110);

12、注意因为涉及到表结构升级；所有这边设计两个版本号 1 和 2、
1版本号