1.添加依赖
在pubspec.yaml添加sqflite插件:
dependencies:
flutter:
sdk: flutter
sqflite: ^1.3.1
然后在项目根目录下执行如下命令:
$ flutter pub get
2.使用SQLite
2.1.导入
import 'package:sqflite/sqflite.dart';
2.2.打开数据库
SQLite数据库是文件系统里的一个文件,标识为一个路径。向openDatabase方法传入数据库文件的路径就可以打开数据库。
var db = await openDatabase('my_db.db');
2.3.关闭数据库
一般一个应用就用一个数据库,一般都不需要去关闭。关闭应用也就关闭了数据库。但你也可以主动关闭数据库释放资源:
await db.close();
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();
}
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;
}
}
3.完整例子
pubspec.yaml配置文件的依赖:
dependencies:
flutter:
sdk: flutter
sqflite: ^1.3.1
collection: ^1.14.13
- 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;
}
}