代码运行后是这样的:
flutter sqflite:
1、在pubspec.yaml中导入sqflite
sqflite: ^1.1.5
2、flutter packages get
如果是用vs code开发,直接ctrl -s 保存就行了
3、sqflite的增删改查有两种方式,一种是原生的,一直是sql助手的,个人建议用sql助手的,相对更安全一点
下面文件依次为:建表sql语句文件、启动时创建表的文件、工具类文件:增删改查,事务,批量执行、 调用代码demo文件
4、我的思路是在一个文件中写了需要用到的所有表,软件启动的时候去检查是否有没创建的,如果有没创建的,则创建
表的文件为:
///数据表定义
class CreateTableSqls{
//关系表语句
static final String createTableSql_relation = '''
CREATE TABLE IF NOT EXISTS relation (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
uid BIGINT(20) NOT NULL,
fuid BIGINT(20) NOT NULL,
type INTEGER(3) NOT NULL);
''';
//用户表语句
static final String createTableSql_user = '''
CREATE TABLE IF NOT EXISTS user (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
uid BIGINT(20) NOT NULL,
phonenumber TEXT(30) NOT NULL,
nickName TEXT(100));
''';
Map<String,String> getAllTables(){
Map<String,String> map = Map<String,String>();
map['relation'] = createTableSql_relation;
map['user'] = createTableSql_user;
return map;
}
}
软件启动时检查和创建表的文件为:软件启动时调用下面的init()方法即可完成所有表的创建
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'CreateTableSqls.dart';
import 'Constants.dart' as Constants;
class TablesInit{
static Database db;
Future init() async{
String databasePath = await getDatabasesPath();
String path = join(databasePath,Constants.DB_NAME);
print('数据库存储路径path:'+path);
//所有的sql语句
CreateTableSqls sqlTables = CreateTableSqls();
//所有的sql语句
Map<String,String> allTableSqls = sqlTables.getAllTables();
try {
db = await openDatabase(path);
} catch (e) {
print('CreateTables init Error $e');
}
//检查需要生成的表
List<String> noCreateTables = await getNoCreateTables(allTableSqls);
print('noCreateTables:'+noCreateTables.toString());
if (noCreateTables.length>0) {
//创建新表
// 关闭上面打开的db,否则无法执行open
db.close();
db = await openDatabase(
path,
version: 1,
onCreate: (Database db,int version) async{
print('db created version is $version');
},
onOpen: (Database db)async{
noCreateTables.forEach((sql) async{
await db.execute(allTableSqls[sql]);
});
print('db补完表已打开');
});
}else{
print("表都存在,db已打开");
}
List tableMaps = await db.rawQuery('SELECT name FROM sqlite_master WHERE type = "table"');
print('所有表:'+tableMaps.toString());
db.close();
print("db已关闭");
}
// 检查数据库中是否有所有有表,返回需要创建的表
Future<List<String>> getNoCreateTables(Map<String,String> tableSqls) async {
Iterable<String> tableNames = tableSqls.keys;
//已经存在的表
List<String> existingTables = List<String>();
//要创建的表
List<String> createTables = List<String>();
List tableMaps = await db.rawQuery('SELECT name FROM sqlite_master WHERE type = "table"');
print('tableMaps:'+tableMaps.toString());
tableMaps.forEach((item){
existingTables.add(item['name']);
});
tableNames.forEach((tableName){
if(!existingTables.contains(tableName)){
createTables.add(tableName);
}
});
return createTables;
}
}
5:工具类文件:其实sqflite已经分装的很好了,我加了一层,一是为了加一点注释和调用样例,方便别人使用,二是以后方法有改动,直接改工具类就可以了
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'Constants.dart' as Constants;
///sqflite数据库操作工具类
class DBUtil {
Database db;
///sql助手插入 @tableName:表名 @paramters:参数map
Future<int> insertByHelper(String tableName,Map<String,Object> paramters) async {
return await db.insert(tableName, paramters);
}
///sql原生插入
Future<int> insert(String sql,List paramters) async {
//调用样例: dbUtil.insert('INSERT INTO Test(name, value) VALUES(?, ?)',['another name', 12345678]);
return await db.rawInsert(sql,paramters);
}
///sql助手查找列表 @tableName:表名 @selects 查询的字段数组 @wheres 条件,如:'uid=? and fuid=?' @whereArgs 参数数组
Future<List<Map>> queryListByHelper(String tableName,List<String> selects,String whereStr,List whereArgs) async {
//调用样例:await dbUtil.queryListByHelper('relation', ['id','uid','fuid'], 'uid=? and fuid=?', [6,1]);
List<Map> maps = await db.query(
tableName,
columns: selects,
where: whereStr,
whereArgs: whereArgs);
return maps;
}
///sql原生查找列表
Future<List<Map>> queryList(String sql) async {
return await db.rawQuery(sql);
}
///sql助手修改
Future<int> updateByHelper(String tableName,Map<String,Object> setArgs,String whereStr,List whereArgs) async {
//样例:
//Map<String,Object> par = Map<String,Object>();
//par['fuid'] = 1;
//dbUtil.updateByHelper('relation', par, 'type=? and uid=?', [0,5]);
return await db.update(
tableName,
setArgs,
where: whereStr,
whereArgs: whereArgs);
}
///sql原生修改
Future<int> update(String sql,List paramters) async {
//样例:dbUtil.update('UPDATE relation SET fuid = ?, type = ? WHERE uid = ?', [1,2,3]);
return await db.rawUpdate(sql,paramters);
}
///sql助手删除 刪除全部whereStr和whereArgs传null
Future<int> deleteByHelper(String tableName,String whereStr,List whereArgs) async {
return await db.delete(
tableName,
where: whereStr,
whereArgs: whereArgs);
}
///sql原生删除
Future<int> delete(String sql,List parameters) async {
//样例: 样例:await dbUtil.delete('DELETE FROM relation WHERE uid = ? and fuid = ?', [123,234]);
return await db.rawDelete(sql, parameters);
}
///获取Batch对象,用于执行sql批处理
Future<Batch> getBatch() async{
//调用样例
// Batch batch = await DBUtil().getBatch();
// batch.insert('Test', {'name': 'item'});
// batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
// batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
// List<Object> results = await batch.commit(); //返回的是id数组
// //batch.commit(noResult: true);//noResult: true不关心返回结果,性能高
// //batch.commit(continueOnError: true)//continueOnError: true 忽略错误,错误可继续执行
return db.batch();
}
///事务控制
Future<dynamic> transaction(Future<dynamic> Function(Transaction txn) action)async{
//调用样例
// try {
// await dbUtil.transaction((txn) async {
// Map<String,Object> par = Map<String,Object>();
// par['uid'] = Random().nextInt(10); par['fuid'] = Random().nextInt(10);
// par['type'] = Random().nextInt(2); par['id'] = 1;
// var a = await txn.insert('relation', par);
// var b = await txn.insert('relation', par);
// });
// } catch (e) {
// print('sql异常:$e');
// }
return await db.transaction(action);
}
//打开DB
open() async {
String databasePath = await getDatabasesPath();
String path = join(databasePath,Constants.DB_NAME);
print('数据库存储路径path:'+path);
try {
db = await openDatabase(path);
print('DB open');
} catch (e) {
print('DBUtil open() Error $e');
}
}
// 记得及时关闭数据库,防止内存泄漏
close() async {
await db.close();
print('DB close');
}
}
6:调用样例
import 'dart:math';
import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'TablesInit.dart';
import 'DBUtil.dart';
void main() => runApp(MyApp());
class MyApp extends StatelessWidget{
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Demo',
theme: ThemeData(
primarySwatch:Colors.blue,
),
home: MyHomePage(title:'数据操作'),
);
}
}
class MyHomePage extends StatefulWidget{
MyHomePage({Key key,this.title}) :super(key:key);
@override
_MyHomePageState createState() => _MyHomePageState();
final String title;
}
class _MyHomePageState extends State<MyHomePage>{
var dataList = "";
DBUtil dbUtil = null;
@override
void initState() {
super.initState();
initDB();
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: Text("数据操作"),
actions: <Widget>[
IconButton(
icon: Icon(Icons.settings),
onPressed: (){
},
)
],
),
body: new Container(
alignment: Alignment.center,
child:Column(
//mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
Row(
mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
// SizedBox(width: 400),
RaisedButton.icon(
icon: Icon(Icons.search),
label: Text('查询'),
color: Colors.blue,
textColor: Colors.white,
// onPressed: null,
onPressed: () {
print("查询数据");
queryData();
}),
SizedBox(width: 10),
RaisedButton(
padding: new EdgeInsets.fromLTRB(10.0, 10.0, 10.0, 10.0),
child: Text('插入'),
color: Colors.blue,
textColor: Colors.white,
onPressed: () {
print("插入");
insertData();
},
),
SizedBox(width: 10),
RaisedButton(
child: Text('清除'),
color: Colors.blue,
textColor: Colors.white,
onPressed: () {
print("清除");
delete();
},
),
SizedBox(width: 10),
RaisedButton(
child: Text('修改'),
color: Colors.blue,
textColor: Colors.white,
elevation: 20,
onPressed: () {
print("修改");
update();
},
),
],
),Row(
mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
// SizedBox(width: 400),
RaisedButton.icon(
icon: Icon(Icons.search),
label: Text('批量插入'),
color: Colors.blue,
textColor: Colors.white,
// onPressed: null,
onPressed: () {
print("批量插入");
batchDispose();
}),
SizedBox(width: 10),
RaisedButton(
padding: new EdgeInsets.fromLTRB(10.0, 10.0, 10.0, 10.0),
child: Text('事务控制'),
color: Colors.blue,
textColor: Colors.white,
onPressed: () {
print("事务控制");
transaction();
},
),
SizedBox(width: 10),
RaisedButton(
child: Text(''),
color: Colors.blue,
textColor: Colors.white,
onPressed: () {
print("");
delete();
},
),
],
),
new Container(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
Text(
"数据:",
),
Text(
dataList,
)
],
),
)
],
),
),
);
}
void initDB() async {
TablesInit tables = TablesInit();
tables.init();
dbUtil = new DBUtil();
}
void insertData() async {
await dbUtil.open();
Map<String,Object> par = Map<String,Object>();
par['uid'] = Random().nextInt(10);
par['fuid'] = Random().nextInt(10);
par['type'] = Random().nextInt(2);
int flag = await dbUtil.insertByHelper('relation', par);
//int flag = await dbUtil.insert('INSERT INTO relation(uid, fuid, type) VALUES("111111", "2222222", 1)');
print('flag:$flag');
await dbUtil.close();
queryData();
}
void delete() async{
await dbUtil.open();
dbUtil.delete('DELETE FROM relation', null);
//dbUtil.deleteByHelper('relation','uid=? and fuid=?',[1,6]);
await dbUtil.close();
queryData();
}
void update() async{
await dbUtil.open();
//dbUtil.update('UPDATE relation SET fuid = ?, type = ? WHERE uid = ?', [Random().nextInt(10),Random().nextInt(10),5]);
Map<String,Object> par = Map<String,Object>();
par['fuid'] = Random().nextInt(10);
dbUtil.updateByHelper('relation', par, 'type=? and uid=?', [0,5]);
await dbUtil.close();
queryData();
}
void queryData() async{
await dbUtil.open();
List<Map> data = await dbUtil.queryList("SELECT * FROM relation");
//List<Map> data = await dbUtil.queryListByHelper('relation', ['id','uid','fuid','type'], 'uid=?', [5]);
print('data:$data');
String showdata = "";
if(data == null){
showdata = "";
}else{
showdata = data.toString();
}
setState(() {
dataList = showdata;
});
await dbUtil.close();
}
//批量处理
void batchDispose() async{
await dbUtil.open();
Batch batch = await dbUtil.getBatch();
Map<String,Object> par = Map<String,Object>();
for(int n=0; n<5;n++){
par['uid'] = Random().nextInt(10);
par['fuid'] = Random().nextInt(10);
par['type'] = Random().nextInt(2);
batch.insert('relation', par);
}
List<Object> results = await batch.commit();
print('results:$results');//返回的是id
await dbUtil.close();
queryData();
}
//事务控制
void transaction() async{
await dbUtil.open();
try {
await dbUtil.transaction((txn) async {
Map<String,Object> par = Map<String,Object>();
par['uid'] = Random().nextInt(10); par['fuid'] = Random().nextInt(10);
par['type'] = Random().nextInt(2); par['id'] = 1;
var a = await txn.insert('relation', par);
var b = await txn.insert('relation', par);
});
} catch (e) {
print('sql异常:$e');
}
await dbUtil.close();
queryData();
}
}
7:运行起来就是这样:
页面很简单,就是那几个方法的调用
上面也是完整的代码
git地址 :https://github.com/jack-xiaoyu/flutter_sqflite
感谢浏览,如有不当之处还望指出~~~