本文为sqflite的基本用法介绍,翻译自sqflite的readme
原文地址:https://pub.dartlang.org/packages/sqflite
首先要在项目路径下的pubspec.yaml中添加依赖
sqflite: ^1.1.0
在Dart文件中导入包后即可使用
import 'package:sqflite/sqflite.dart';
基本用法:
// 获取数据库
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, '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 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');
List<Map> expectedList = [
{'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
{'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));
// 获取表内数据条数
count = Sqflite
.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);
// 删
count = await database
.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);
// 用完后记得关闭
await database.close();
创建一个数据库Helper
final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';
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;
}
}
class TodoProvider {
Database db;
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();
}
处理查询结果,查询结果是只读的,修改将引发错误
List<Map<String, dynamic>> records = await db.query('my_table');
如果想得到可以修改的对象,你得这么做
Map<String, dynamic> map = Map<String, dynamic>.from(mapRead);
关于使用事务
await database.transaction((txn) async {
// 正确的例子,在事务中必须使用事务对象txn来处理
await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
// 错误的例子,这将导致死锁
await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
//如果事务未能正常结束,在回调中抛出异常可放弃事务
});
另一个方案,使用批处理
batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
//如果想得到操作结果,用这个
results = await batch.commit();
//如果只想插入大量数据,用这个
await batch.commit(noResult: true);
在事务期间,在事务提交之前不会提交批处理
await database.transaction((txn) async {
var batch = txn.batch();
// ...
//提交,但是实际的提交将在提交事务时发生
//然而,数据在这个事务中是可用的
await batch.commit();
// ...
});
默认情况下,批处理一旦遇到错误就会停止(这通常会返回未提交的更改)。你可以忽略错误,这样即使一个操作失败,每个成功的操作都会运行并提交
await batch.commit(continueOnError: true);
关键字列表,不要让下列字符串出现在表名和数据中
“add”,“all”,“alter”,“and”,“as”,“autoincrement”,“between”,“case”,“check”,“collate”,“commit”,“constraint”,“create”,“default”,“deferrable”,“delete”,“distinct”,“drop”,“else”,“escape”,“except”,“exists”,“foreign”,“from”,“group”,“having”,“if”,“in”,“index”,“insert”,“intersect”,“into”,“is”,“isnull”,“join”,“limit”,“not”,“notnull”,“null”,“on”,“or”,“order”,“primary”,“references”,“select”,“set”,“table”,“then”,“to”,“transaction”,“union”,“unique”,“update”,“using”,“values”,“when”,“where”
支持的数据
日期类型#
需要转换成INTEGER或者TEXT
bool#
转换成INTEGER,使用的值为0和1
INTEGER #
Dart type: int
数据范围: 从 -2^63 到 2^63 - 1
REAL #
Dart type: num
TEXT #
Dart type: String
BLOB #
Dart type: Uint8List
Dart type List 虽然支持,但是慢