SQLite 插件 同时支持 iOS 和Android.
开始
在您的flutter项目中添加依赖项:
dependencies:
...
sqflite: any
引用示例
Import sqflite.dart
import 'package:sqflite/sqflite.dart';
原始的Sql查询
执行原始SQL查询的示例代码
// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
// Delete the database
await deleteDatabase(path);
// open the database
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)');
});
// Insert some records in a transaction
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');
});
// Update some record
int count = await database.rawUpdate(
'UPDATE Test SET name = ?, VALUE = ? WHERE name = ?',
['updated name', '9876', 'some name']);
print('updated: $count');
// Get the records
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 the records
count = Sqflite
.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);
// Delete a record
count = await database
.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);
// Close the database
await dat
SqlHelper(助手)
使用助手的示例
inal 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();
}
Transaction
不要使用数据库,而只在事务中使用Transaction对象来访问数据库
await database.transaction((txn) async {
// Ok
await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
// DON'T use the database object in a transaction
// this will deadlock!
await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
});
批处理支持
为了避免频繁的交互,可以使用Batch:
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();
获取每个操作的结果都会带来成本(插入的id和更新和删除的更改的数量),尤其是在执行额外SQL请求的Android上。如果不关心结果并担心消耗大量资源和性能,可以使用
await batch.commit(noResult: true);
警告,在事务期间,在提交事务之前不会提交批处理
wait database.transaction((txn) async {
var batch = txn.batch();
// ...
// commit but the actual commit will happen when the transaction is commited
// however the data is available in this transaction
await batch.commit();
// ...
});
默认情况下,一遇到错误(通常恢复未提交的更改),批处理就停止。您可以忽略错误,即使碰到一个操作失败,也能运行和提交操作成功每个提交:
await batch.commit(continueOnError: true);
表和列名
一般来说,最好避免对实体名称使用SQLite关键字。如果使用下列名称中的任何一个:
"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"
助手会逃避这个名字,如。。
db.query('table')
以上这行代码将等同于手动在表名周围添加双引号(令人困惑的是,这里命名的表),等同于如下代码
db.rawQuery('SELECT * FROM "table"');
但是在任何其他原始语句(包括order.、where、group.)中,确保使用双引号正确地转义名称。例如,参见下面,列名组在列参数中没有转义,而是在where参数中转义。
db.query('table', columns: ['group'], where: '"group" = ?', whereArgs: ['my_group']);
SQLite 支持的数据类型
Sqlite还没有对值进行有效性检查,因此请避免不支持的类型
DateTime
SQLite不支持DateTime类型。我个人将它们存储为int(.sSinceEpoch)或string(iso8601)
Bool
SQLite不支持bool类型。使用整数和0和1值。
INTEGER
REAL
TEXT
BLOB
当前问题
-
-
Due to the way transaction works in SQLite (threads), concurrent read and write transaction are not supported. All calls are currently synchronized and transactions block are exclusive. I thought that a basic way to support concurrent access is to open a database multiple times but it only works on iOS as Android reuses the same database object. I also thought a native thread could be a potential future solution however on android accessing the database in another thread is blocked while in a transaction…
上述机翻
由于事务在SQLIT(线程)中的工作方式,不支持并发读写事务。所有调用当前都是同步的,事务块是独占的。我认为支持并发访问的基本方法是多次打开数据库,但是它只能在iOS上工作,因为Android重用了相同的数据库对象。我还认为本机线程可能是未来可能的解决方案,但是当android访问另一个线程中的数据库时,在事务中会阻塞……
目前INTEGER被限制在-2^63到2^63-1(尽管Android支持更大的)
更多
相关类库