Flutter中SQLite数据库的使用

时间：2022-01-30 18:25:08

同时支持android和ios

支持事务和批量操作

支持插入/查询/更新/删除操作

在iOS和Android上的后台线程中执行数据库操作

1.添加依赖

dependencies:

  ...

  sqflite: any
Dart

2.导入依赖

import 'package:sqflite/sqflite.dart';
Dart

3.支持SQL查询

// 获取本地SQLite数据库

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

// 获取Test表的数据

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

4.用法示例

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

}
Dart

注意事项

1.Transaction(事务)

当使用transaction对象访问数据时，不能再使用database对象访问数据库
await database.transaction((txn) async {

  // 完全Ok

  await txn.execute("CREATE TABLE Test1 (id INTEGER PRIMARY KEY)");

  // 不能在transaction对象里面使用database对象，这会发生死锁

  // await database.execute("CREATE TABLE Test2 (id INTEGER PRIMARY KEY)");

});
Dart

2.支持批量操作

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();
Dart
这些操作返回结果，都会有一些开销；如果你不考虑结果，可以使用：
await batch.commit(noResult: true);
Dart
事务期间，直到事务被提交，批量操作才能提交
await database.transaction((txn) async {

  var batch = txn.batch();

  //...

  // 实际提交将在事务启动时发生

  await batch.commit();

});
Dart

3.表和列的名字

通常避免使用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"
Dart
否则要使用双引号转义，例如：
db.rawQuery('SELECT * FROM "table"');

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

4.支持的列类型

INTEGER   相当于dart类型中int

 REAL     相当于dart类型中num

 TEXT     相当于dart类型中String

 BLOB     相当于dart类型中Uint8List

相关文章