Flutter中SQLite数据库的使用

最新推荐文章于 2022-12-19 13:27:48 发布

weixin_30834019

最新推荐文章于 2022-12-19 13:27:48 发布

阅读量317

点赞数

CC 4.0 BY-SA版权

文章标签：数据库移动开发

原文链接：http://www.cnblogs.com/pythonClub/p/10677084.html

本文介绍了一个跨平台数据库操作方案，支持Android和iOS，利用sqflite包进行事务和批量操作，实现插入、查询、更新和删除功能。文章通过示例展示了如何在Dart中使用sqflite进行数据库操作，包括创建数据库、执行SQL查询、事务处理、数据更新和获取等。

同时支持android和ios

支持事务和批量操作
支持插入/查询/更新/删除操作
在iOS和Android上的后台线程中执行数据库操作

1.添加依赖

dependencies:
  ...
  sqflite: any

2.导入依赖

import 'package:sqflite/sqflite.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();

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