Flutter数据库Sqflite之增删改查
简介
- sqflite是Flutter的SQLite插件，支持iOS和Android，目前官方版本是sqflite1.1.3
- sqflite插件地址：https://pub.dartlang.org/packages/sqflite#-readme-tab-
- sqflite支持事务和批处理
- sqflite支持打开期间自动版本管理
- sqflite支持插入/查询/更新/删除查询的助手
- sqflite支持在iOS和Android上的后台线程中执行数据库操作
-
更多Flutter相关内容可以访问我的Github
关键API
- 获取数据库的路径
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');- 打开数据库
Database database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
// When creating the db, create the table
await db.execute(
'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});- 使用事务插入一条记录
await database.transaction((txn) async {
int id1 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
print('inserted1: $id1');
int id2 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
['another name', 12345678, 3.1416]);
print('inserted2: $id2');
});- 更新一条记录
int count = await database.rawUpdate(
'UPDATE Test SET name = ?, VALUE = ? WHERE name = ?',
['updated name', '9876', 'some name']);
print('updated: $count');- 查询记录
List<Map> list = await database.rawQuery('SELECT * FROM Test');- 查询总记录数
count = Sqflite.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));- 删除一条记录
count = await database.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);- 关闭数据库
await database.close();使用
- 首先创建model
class User {
String name;
int age;
int id;
Map<String, dynamic> toMap() {
var map = new Map<String, dynamic>();
map['name'] = name;
map['age'] = age;
map['id'] = id;
return map;
}
static User fromMap(Map<String, dynamic> map) {
User user = new User();
user.name = map['name'];
user.age = map['age'];
user.id = map['id'];
return user;
}
static List<User> fromMapList(dynamic mapList) {
List<User> list = new List(mapList.length);
for (int i = 0; i < mapList.length; i++) {
list[i] = fromMap(mapList[i]);
}
return list;
}
}- 创建db_helper，数据库帮助类
class DatabaseHelper {
static final DatabaseHelper _instance = DatabaseHelper.internal();
factory DatabaseHelper() => _instance;
final String tableName = "table_user";
final String columnId = "id";
final String columnName = "name";
final String columnAge = "age";
static Database _db;
Future<Database> get db async {
if (_db != null) {
return _db;
}
_db = await initDb();
return _db;
}
DatabaseHelper.internal();
initDb() async {
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'sqflite.db');
var ourDb = await openDatabase(path, version: 1, onCreate: _onCreate);
return ourDb;
}
//创建数据库表
void _onCreate(Database db, int version) async {
await db.execute(
"create table $tableName($columnId integer primary key,$columnName text not null ,$columnAge integer not null )");
print("Table is created");
}
//插入
Future<int> saveItem(User user) async {
var dbClient = await db;
int res = await dbClient.insert("$tableName", user.toMap());
print(res.toString());
return res;
}
//查询
Future<List> getTotalList() async {
var dbClient = await db;
var result = await dbClient.rawQuery("SELECT * FROM $tableName ");
return result.toList();
}
//查询总数
Future<int> getCount() async {
var dbClient = await db;
return Sqflite.firstIntValue(await dbClient.rawQuery(
"SELECT COUNT(*) FROM $tableName"
));
}
//按照id查询
Future<User> getItem(int id) async {
var dbClient = await db;
var result = await dbClient.rawQuery("SELECT * FROM $tableName WHERE id = $id");
if (result.length == 0) return null;
return User.fromMap(result.first);
}
//清空数据
Future<int> clear() async {
var dbClient = await db;
return await dbClient.delete(tableName);
}
//根据id删除
Future<int> deleteItem(int id) async {
var dbClient = await db;
return await dbClient.delete(tableName,
where: "$columnId = ?", whereArgs: [id]);
}
//修改
Future<int> updateItem(User user) async {
var dbClient = await db;
return await dbClient.update("$tableName", user.toMap(),
where: "$columnId = ?", whereArgs: [user.id]);
}
//关闭
Future close() async {
var dbClient = await db;
return dbClient.close();
}
}- 在进行页面增删该查操作
class DataAppPage extends StatefulWidget {
@override
State<StatefulWidget> createState() {
return new _DataAppPageState();
}
}
class _DataAppPageState extends State<DataAppPage> {
List<User> _datas = new List();
var db = DatabaseHelper();
Future<Null> _refresh() async {
_query();
}
@override
void initState() {
super.initState();
_getDataFromDb();
}
_getDataFromDb() async {
List datas = await db.getTotalList();
if (datas.length > 0) {
//数据库有数据
datas.forEach((user) {
User item = User.fromMap(user);
_datas.add(item);
});
} else {
//数据库没有数据
User user = new User();
user.name = "张三";
user.age = 10;
user.id = 1;
User user2 = new User();
user2.name = "李四";
user2.age = 12;
user2.id = 2;
await db.saveItem(user);
await db.saveItem(user2);
_datas.add(user);
_datas.add(user2);
}
setState(() {});
}
//添加
Future<Null> _add() async {
User user = new User();
user.name = "我是增加的";
user.age = 33;
await db.saveItem(user);
_query();
}
//删除,默认删除第一条数据
Future<Null> _delete() async {
List datas = await db.getTotalList();
if (datas.length > 0) {
//修改第一条数据
User user = User.fromMap(datas[0]);
db.deleteItem(user.id);
_query();
}
}
//修改，默认修改第一条数据
Future<Null> _update() async {
List datas = await db.getTotalList();
if (datas.length > 0) {
//修改第一条数据
User u = User.fromMap(datas[0]);
u.name = "我被修改了";
db.updateItem(u);
_query();
}
}
//查询
Future<Null> _query() async {
_datas.clear();
List datas = await db.getTotalList();
if (datas.length > 0) {
//数据库有数据
datas.forEach((user) {
User dataListBean = User.fromMap(user);
_datas.add(dataListBean);
});
}
setState(() {});
}
@override
Widget build(BuildContext context) {
return new Scaffold(
appBar: AppBar(
title: Text("sqflite学习"),
centerTitle: true,
actions: <Widget>[
new PopupMenuButton(
onSelected: (String value) {
switch (value) {
case "增加":
_add();
break;
case "删除":
_delete();
break;
case "修改":
_update();
break;
case "查询":
_query();
break;
}
},
itemBuilder: (BuildContext context) => <PopupMenuItem<String>>[
new PopupMenuItem(value: "增加", child: new Text("增加")),
new PopupMenuItem(value: "删除", child: new Text("删除")),
new PopupMenuItem(value: "修改", child: new Text("修改")),
new PopupMenuItem(value: "查询", child: new Text("查询")),
])
],
),
body: RefreshIndicator(
displacement: 15,
onRefresh: _refresh,
child: ListView.separated(
itemBuilder: _renderRow,
physics: new AlwaysScrollableScrollPhysics(),
separatorBuilder: (BuildContext context, int index) {
return Container(
height: 0.5,
color: Colors.black38,
);
},
itemCount: _datas.length),
),
);
}
Widget _renderRow(BuildContext context, int index) {
return Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: <Widget>[
Padding(
padding: EdgeInsets.all(5),
child: Text("姓名：" + _datas[index].name)),
Padding(
padding: EdgeInsets.all(5),
child: Text("年龄：" + _datas[index].age.toString())),
],
);
}
}