• • 支持事务和批处理

• • 版本开放自动管理

• • 增删改查助手

• iOS 和 Android的后台线程中执行的DB操作

开始

dependencies:
  ...
  sqflite: any

引用示例

import 'package:sqflite/sqflite.dart';

原始的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

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 = 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);

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);

表和列名

"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"');

db.query('table', columns: ['group'], where: '"group" = ?', whereArgs: ['my_group']);

SQLite 支持的数据类型

DateTime

Bool

INTEGER

• • Dart type: int

• 取值范围: 从-2^63 到 2^63 – 1

REAL

• Dart type: num

TEXT

• Dart type: String

BLOB

• • Dart type: Uint8List

• Dart type List<int> 是支持的，但是不推荐使用 慢速转换)

当前问题

• • 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…

• Currently INTEGER are limited to -2^63 to 2^63 – 1 (although Android supports bigger ones)

上述机翻

由于事务在SQLIT（线程）中的工作方式，不支持并发读写事务。所有调用当前都是同步的，事务块是独占的。我认为支持并发访问的基本方法是多次打开数据库，但是它只能在iOS上工作，因为Android重用了相同的数据库对象。我还认为本机线程可能是未来可能的解决方案，但是当android访问另一个线程中的数据库时，在事务中会阻塞……

目前INTEGER被限制在-2^63到2^63-1（尽管Android支持更大的）

更多

相关类库