Flutter使用SQLite
【摘要】 1.添加依赖
在pubspec.yaml添加sqflite插件：
dependencies:
flutter: sdk: flutter
sqflite: ^1.3.1
12345
然后在项目根目录下执行如下命令：
$ flutter pub get
1
2.使用SQLite
2.1.导入
import 'package:sqflite/sqfl...
1.添加依赖
在pubspec.yaml添加sqflite插件：
dependencies:
flutter: sdk: flutter
sqflite: ^1.3.1
- 1
- 2
- 3
- 4
- 5
然后在项目根目录下执行如下命令：
$ flutter pub get
- 1
2.使用SQLite
2.1.导入
import 'package:sqflite/sqflite.dart';
- 1
2.2.打开数据库
SQLite数据库是文件系统里的一个文件，标识为一个路径。向openDatabase方法传入数据库文件的路径就可以打开数据库。
var db = await openDatabase('my_db.db');
- 1
2.3.关闭数据库
一般一个应用就用一个数据库，一般都不需要去关闭。关闭应用也就关闭了数据库。但你也可以主动关闭数据库释放资源：
await db.close();
- 1
2.4.原生SQL查询
就是没有进行过封装，直接用SQL语句查询的。
_query() async {
/// 通过getDatabasesPath()方法获取数据库位置
var databasePath = await getDatabasesPath();
String path = join(databasePath, "demo.db"); /// 删除数据库
await deleteDatabase(path); /// 打开数据库
Database database = await openDatabase( path, version: 1, onCreate: (Database db, int version) async { /// 创建数据库 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 goods",225,456.123)'); print('inserted1:$id1'); int id2 = await txn.rawInsert( 'INSERT INTO Test(name,value,num) VALUES(?,?,?)', ['another name', 45456, 3.2154]); print('inserted2:$id2');
}); /// 更新记录
int count = await database.rawUpdate( 'UPDATE Test SET name = ?,value = ? WHERE name = ?', ['updated name', '9876', 'some goods']);
print('updated:$count'); /// 查询
List<Map> list = await database.rawQuery('SELECT * from Test');
List<Map> expectedList = [ {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.123}, {'name': 'another name', 'id': 2, 'value': 45456, 'num': 3.2154}
]; /// 断言，判断更新后的结果与预期结果是否一致
assert(const DeepCollectionEquality().equals(list, expectedList)); /// 统计记录数
int count2 = Sqflite.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count2 == 2); /// 删除一条记录
int count3 = await database.rawDelete('DELETE FROM Test WHERE name = ?',['another name']);
assert(count3 == 1); /// 关闭数据库
await database.close();
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
2.5.SQL helpers
_querySQLHelper() async {
TodoProvider todoProvider = TodoProvider(); /// 通过getDatabasesPath()方法获取数据库位置
var databasePath = await getDatabasesPath();
String path = join(databasePath, "demo.db"); /// 删除数据库
await deleteDatabase(path); /// 打开数据库，并创建todo表
await todoProvider.open(path); /// 查一条数据
Todo todo = Todo();
todo.id = 1;
todo.title = "Hello";
todo.done = false;
Todo td = await todoProvider.insert(todo);
print('inserted:${td.toMap()}'); Todo todo2 = Todo();
todo2.id = 2;
todo2.title = "Hello world";
todo2.done = false;
Todo td2 = await todoProvider.insert(todo2);
print('inserted:${td2.toMap()}'); /// 更新数据
todo2.title = "Big big world";
int u = await todoProvider.update(todo2);
print("update:$u"); /// 删除数据
int d = await todoProvider.delete(1);
print("delete:$d"); /// 查询数据
Todo dd = await todoProvider.getTodo(2);
print("todo:${dd.toMap()}"); /// 关闭数据库
todoProvider.close();
}
/// 表名
final String tableTodo = 'todo';
/// _id字段
final String columnId = '_id';
/// title字段
final String columnTitle = 'title';
/// done字段
final String columnDone = 'done';
/// 操作todo表的工具类
class TodoProvider {
Database db; /// 打开数据库，并创建todo表
Future open(String path) async { db = await openDatabase(path, version: 1, onCreate: (Database db, int version) async { await db.execute('''
create table $tableTodo ( $columnId integer primary key autoincrement, $columnTitle text not null,
$columnDone integer not null)
'''); });
} Future<Todo> insert(Todo todo) async { todo.id = await db.insert(tableTodo, todo.toMap()); return todo;
} Future<Todo> getTodo(int id) async { List<Map> maps = await db.query(tableTodo, columns: [columnId, columnDone, columnTitle], where: '$columnId = ?', whereArgs: [id]); if (maps.length > 0) { return Todo.fromMap(maps.first); } return null;
} Future<int> delete(int id) async { return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
} Future<int> update(Todo todo) async { return await db.update(tableTodo, todo.toMap(), where: '$columnId = ?', whereArgs: [todo.id]);
} Future close() async => db.close();
}
/// todo对应的实体类
class Todo {
int id;
String title;
bool done; Map<String, dynamic> toMap() { var map = <String, dynamic>{ columnTitle: title, columnDone: done == true ? 1 : 0 }; if (id != null) { map[columnId] = id; } return map;
} Todo(); Todo.fromMap(Map<String, dynamic> map) { id = map[columnId]; title = map[columnTitle]; done = map[columnDone] == 1;
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
3.完整例子
pubspec.yaml配置文件的依赖：
dependencies:
flutter: sdk: flutter
sqflite: ^1.3.1
collection: ^1.14.13
- 1
- 2
- 3
- 4
- 5
- collection: ^1.14.13是为了使用
import 'package:collection/collection.dart';的DeepCollectionEquality().equals(list, expectedList)。 - 字符串的
join，要引入import 'package:path/path.dart';
import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:fluttertoast/fluttertoast.dart';
import 'package:sqflite/sqflite.dart';
import 'package:collection/collection.dart';
import 'package:path/path.dart';
void main() => runApp(DemoApp());
class DemoApp extends StatelessWidget {
@override
Widget build(BuildContext context) { return new MaterialApp( title: 'Image Picker Demo', home: new MyHomePage(), );
}
}
class MyHomePage extends StatefulWidget {
@override
_MyHomePageState createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
@override
Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text("Hello World"), ), body: Center( child: RaisedButton( child: Text("Click"), onPressed: () { Fluttertoast.showToast(msg: "Hello world", textColor: Colors.black); _querySQLHelper(); }, ), ), );
}
}
_query() async {
/// 通过getDatabasesPath()方法获取数据库位置
var databasePath = await getDatabasesPath();
String path = join(databasePath, "demo.db"); /// 删除数据库
await deleteDatabase(path); /// 打开数据库
Database database = await openDatabase(path, version: 1, onCreate: (Database db, int version) async { /// 创建数据库 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 goods",225,456.123)'); print('inserted1:$id1'); int id2 = await txn.rawInsert( 'INSERT INTO Test(name,value,num) VALUES(?,?,?)', ['another name', 45456, 3.2154]); print('inserted2:$id2');
}); /// 更新记录
int count = await database.rawUpdate( 'UPDATE Test SET name = ?,value = ? WHERE name = ?', ['updated name', '9876', 'some goods']);
print('updated:$count'); /// 查询
List<Map> list = await database.rawQuery('SELECT * from Test');
List<Map> expectedList = [ {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.123}, {'name': 'another name', 'id': 2, 'value': 45456, 'num': 3.2154}
]; /// 断言，判断更新后的结果与预期结果是否一致
assert(const DeepCollectionEquality().equals(list, expectedList)); /// 统计记录数
int count2 = Sqflite.firstIntValue( await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count2 == 2); /// 删除一条记录
int count3 = await database .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count3 == 1); /// 关闭数据库
await database.close();
}
_querySQLHelper() async {
TodoProvider todoProvider = TodoProvider(); /// 通过getDatabasesPath()方法获取数据库位置
var databasePath = await getDatabasesPath();
String path = join(databasePath, "demo.db"); /// 删除数据库
await deleteDatabase(path); /// 打开数据库，并创建todo表
await todoProvider.open(path); /// 查一条数据
Todo todo = Todo();
todo.id = 1;
todo.title = "Hello";
todo.done = false;
Todo td = await todoProvider.insert(todo);
print('inserted:${td.toMap()}'); Todo todo2 = Todo();
todo2.id = 2;
todo2.title = "Hello world";
todo2.done = false;
Todo td2 = await todoProvider.insert(todo2);
print('inserted:${td2.toMap()}'); /// 更新数据
todo2.title = "Big big world";
int u = await todoProvider.update(todo2);
print("update:$u"); /// 删除数据
int d = await todoProvider.delete(1);
print("delete:$d"); /// 查询数据
Todo dd = await todoProvider.getTodo(2);
print("todo:${dd.toMap()}"); /// 关闭数据库
todoProvider.close();
}
/// 表名
final String tableTodo = 'todo';
/// _id字段
final String columnId = '_id';
/// title字段
final String columnTitle = 'title';
/// done字段
final String columnDone = 'done';
/// 操作todo表的工具类
class TodoProvider {
Database db; /// 打开数据库，并创建todo表
Future open(String path) async { db = await openDatabase(path, version: 1, onCreate: (Database db, int version) async { await db.execute('''
create table $tableTodo ( $columnId integer primary key autoincrement, $columnTitle text not null,
$columnDone integer not null)
'''); });
} Future<Todo> insert(Todo todo) async { todo.id = await db.insert(tableTodo, todo.toMap()); return todo;
} Future<Todo> getTodo(int id) async { List<Map> maps = await db.query(tableTodo, columns: [columnId, columnDone, columnTitle], where: '$columnId = ?', whereArgs: [id]); if (maps.length > 0) { return Todo.fromMap(maps.first); } return null;
} Future<int> delete(int id) async { return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
} Future<int> update(Todo todo) async { return await db.update(tableTodo, todo.toMap(), where: '$columnId = ?', whereArgs: [todo.id]);
} Future close() async => db.close();
}
/// todo对应的实体类
class Todo {
int id;
String title;
bool done; Map<String, dynamic> toMap() { var map = <String, dynamic>{ columnTitle: title, columnDone: done == true ? 1 : 0 }; if (id != null) { map[columnId] = id; } return map;
} Todo(); Todo.fromMap(Map<String, dynamic> map) { id = map[columnId]; title = map[columnTitle]; done = map[columnDone] == 1;
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
文章来源: blog.csdn.net，作者：WongKyunban，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/weixin_40763897/article/details/108232807
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）