用 SQLite 做数据持久化

如果你正在编写一个需要持久化且查询大量本地设备数据的 app,可考虑采用数据库,而不是本地文件夹或关键值库。总的来说,相比于其他本地持久化方案来说,数据库能够提供更为迅速的插入、更新、查询功能。

Flutter应用程序中可以通过 sqflite package 来使用 SQLite 数据库。本文将通过使用 sqflite 来演示插入,读取,更新,删除各种狗狗的数据。

如果你对于 SQLite 和 SQL 的各种语句还不熟悉,请查看 SQLite 官方的教程 SQLite 教程,在查看本文之前需要掌握基本的SQL语句。

总共有以下的步骤:

  1. 添加依赖;

  2. 定义 Dog (狗) 数据模型;

  3. 打开数据库;

  4. 创建 dogs 数据表;

  5. 将一条 Dog 数据插入数据库;

  6. 查询所有狗狗的数据;

  7. 更新(修改)一条 Dog 的数据;

  8. 删除一条 Dog 的数据。

1. 添加依赖

为了使用 SQLite 数据库,首先需要导入 sqflitepath package。

  • sqflite 提供了丰富的类和方法,以便你能便捷实用 SQLite 数据库。

  • path 提供了大量方法,以便你能正确的定义数据库在磁盘上的存储位置。

运行 flutter pub add 将其添加为依赖:

$ flutter pub add sqflite path

确保你已将 packages 导入要使用的文件中。

import 'dart:async';

import 'package:flutter/widgets.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

2. 定义狗狗的数据模型

在你准备在新建的表里存储狗狗们的信息的的时候,你需要先定义这些数据。例如,定义一个狗类时,每一条狗狗的数据将包含三个字段:一个唯一的 id ;名字 name ;年龄 age

class Dog {
  final int id;
  final String name;
  final int age;

  const Dog({
    required this.id,
    required this.name,
    required this.age,
  });
}

3. 打开数据库

在你准备读写数据库的数据之前,你要先打开这个数据库。打开一个数据库有以下两个步骤:

  1. 使用 sqflite package 里的 getDatabasesPath 方法并配合 path package里的 join 方法定义数据库的路径。

  2. Open the database with the openDatabase() function from sqflite.

使用 sqflite package 里的 openDatabase 方法打开数据库。
// Avoid errors caused by flutter upgrade.
// Importing 'package:flutter/widgets.dart' is required.
WidgetsFlutterBinding.ensureInitialized();
// Open the database and store the reference.
final database = openDatabase(
  // Set the path to the database. Note: Using the `join` function from the
  // `path` package is best practice to ensure the path is correctly
  // constructed for each platform.
  join(await getDatabasesPath(), 'doggie_database.db'),
);

4. 创建 dogs

接下来,你需要创建一个表用以存储各种狗狗的信息。在这个示例中,创建一个名为 dogs 数据库表,它定义了可以被存储的数据。这样,每条 Dog 数据就包含了一个 idnameage。因此,在 dogs 数据库表中将有三列,分别是 idnameage

  1. id 是 Dart 的 int 类型,在数据表中是 SQLite 的 INTEGER 数据类型。最佳实践是将 id 作为数据库表的主键,用以改善查询和修改的时间。

  2. name 是Dart的 String类型,在数据表中是SQLite的 TEXT 数据类型。

  3. age 也是Dart的 int 类型,在数据表中是SQLite的 INTEGER 数据类型。

关于 SQLite 数据库能够存储的更多的数据类型信息请查阅官方的 SQLite Datatypes 文档

final database = openDatabase(
  // Set the path to the database. Note: Using the `join` function from the
  // `path` package is best practice to ensure the path is correctly
  // constructed for each platform.
  join(await getDatabasesPath(), 'doggie_database.db'),
  // When the database is first created, create a table to store dogs.
  onCreate: (db, version) {
    // Run the CREATE TABLE statement on the database.
    return db.execute(
      'CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)',
    );
  },
  // Set the version. This executes the onCreate function and provides a
  // path to perform database upgrades and downgrades.
  version: 1,
);

5. 插入一条狗狗的数据

现在你已经准备好了一个数据库用于存储各种狗狗的信息数据,现在开始读写数据咯。

首先,在 dogs 数据表中插入一条 Dog 数据。分以下两步:

  1. Dog 转换成一个 Map 数据类型;

  2. 使用 insert() 方法把 Map 保存到 dogs 数据表中。

class Dog {
  final int id;
  final String name;
  final int age;

  Dog({
    required this.id,
    required this.name,
    required this.age,
  });

  // Convert a Dog into a Map. The keys must correspond to the names of the
  // columns in the database.
  Map<String, Object?> toMap() {
    return {
      'id': id,
      'name': name,
      'age': age,
    };
  }

  // Implement toString to make it easier to see information about
  // each dog when using the print statement.
  @override
  String toString() {
    return 'Dog{id: $id, name: $name, age: $age}';
  }
}
// Define a function that inserts dogs into the database
Future<void> insertDog(Dog dog) async {
  // Get a reference to the database.
  final db = await database;

  // Insert the Dog into the correct table. You might also specify the
  // `conflictAlgorithm` to use in case the same dog is inserted twice.
  //
  // In this case, replace any previous data.
  await db.insert(
    'dogs',
    dog.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}
// Create a Dog and add it to the dogs table
var fido = Dog(
  id: 0,
  name: 'Fido',
  age: 35,
);

await insertDog(fido);

6. 查询狗狗列表

现在已经有了一条 Dog 存储在数据库里。你可以通过查询数据库,检索到一只狗狗的数据或者所有狗狗的数据。分为以下两步:

  1. 调用 dogs 表对像的 query 方法。这将返回一个List <Map>

  2. List<Map> 转换成 List<Dog> 数据类型。

// A method that retrieves all the dogs from the dogs table.
Future<List<Dog>> dogs() async {
  // Get a reference to the database.
  final db = await database;

  // Query the table for all the dogs.
  final List<Map<String, Object?>> dogMaps = await db.query('dogs');

  // Convert the list of each dog's fields into a list of `Dog` objects.
  return [
    for (final {
          'id': id as int,
          'name': name as String,
          'age': age as int,
        } in dogMaps)
      Dog(id: id, name: name, age: age),
  ];
}
// Now, use the method above to retrieve all the dogs.
print(await dogs()); // Prints a list that include Fido.

7. 修改一条 Dog 数据

使用 sqflite package 中的 update()方法,可以对已经插入到数据库中的数据进行修改(更新)。

修改数据操作包含以下两步:

  1. 将一条狗狗的数据转换成 Map 数据类型;

  2. 使用 where 语句定位到具体将要被修改的数据。

Future<void> updateDog(Dog dog) async {
  // Get a reference to the database.
  final db = await database;

  // Update the given Dog.
  await db.update(
    'dogs',
    dog.toMap(),
    // Ensure that the Dog has a matching id.
    where: 'id = ?',
    // Pass the Dog's id as a whereArg to prevent SQL injection.
    whereArgs: [dog.id],
  );
}
// Update Fido's age and save it to the database.
fido = Dog(
  id: fido.id,
  name: fido.name,
  age: fido.age + 7,
);
await updateDog(fido);

// Print the updated results.
print(await dogs()); // Prints Fido with age 42.

8. 删除一条 Dog 的数据

除了插入和修改狗狗们的数据,你还可以从数据库中删除狗狗的数据。删除数据用到了 sqflite package 中的 delete() 方法。

在这一小节,新建一个方法用来接收一个 id 并且删除数据库中与这个 id 匹配的那一条数据。为了达到这个目的,你必须使用 where 语句限定哪一条才是被删除的数据。

Future<void> deleteDog(int id) async {
  // Get a reference to the database.
  final db = await database;

  // Remove the Dog from the database.
  await db.delete(
    'dogs',
    // Use a `where` clause to delete a specific dog.
    where: 'id = ?',
    // Pass the Dog's id as a whereArg to prevent SQL injection.
    whereArgs: [id],
  );
}

示例

运行示例需要以下几步:

  1. 创建一个新的 Flutter 工程;

  2. sqflitepath 包添加到 pubspec.yaml 文件里;

  3. 将以下代码粘贴在 lib/db_test.dart 文件里(若无则新建,若有则覆盖);

  4. 运行 flutter run lib/db_test.dart

import 'dart:async';

import 'package:flutter/widgets.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

void main() async {
  // Avoid errors caused by flutter upgrade.
  // Importing 'package:flutter/widgets.dart' is required.
  WidgetsFlutterBinding.ensureInitialized();
  // Open the database and store the reference.
  final database = openDatabase(
    // Set the path to the database. Note: Using the `join` function from the
    // `path` package is best practice to ensure the path is correctly
    // constructed for each platform.
    join(await getDatabasesPath(), 'doggie_database.db'),
    // When the database is first created, create a table to store dogs.
    onCreate: (db, version) {
      // Run the CREATE TABLE statement on the database.
      return db.execute(
        'CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)',
      );
    },
    // Set the version. This executes the onCreate function and provides a
    // path to perform database upgrades and downgrades.
    version: 1,
  );

  // Define a function that inserts dogs into the database
  Future<void> insertDog(Dog dog) async {
    // Get a reference to the database.
    final db = await database;

    // Insert the Dog into the correct table. You might also specify the
    // `conflictAlgorithm` to use in case the same dog is inserted twice.
    //
    // In this case, replace any previous data.
    await db.insert(
      'dogs',
      dog.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // A method that retrieves all the dogs from the dogs table.
  Future<List<Dog>> dogs() async {
    // Get a reference to the database.
    final db = await database;

    // Query the table for all the dogs.
    final List<Map<String, Object?>> dogMaps = await db.query('dogs');

    // Convert the list of each dog's fields into a list of `Dog` objects.
    return [
      for (final {
            'id': id as int,
            'name': name as String,
            'age': age as int,
          } in dogMaps)
        Dog(id: id, name: name, age: age),
    ];
  }

  Future<void> updateDog(Dog dog) async {
    // Get a reference to the database.
    final db = await database;

    // Update the given Dog.
    await db.update(
      'dogs',
      dog.toMap(),
      // Ensure that the Dog has a matching id.
      where: 'id = ?',
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [dog.id],
    );
  }

  Future<void> deleteDog(int id) async {
    // Get a reference to the database.
    final db = await database;

    // Remove the Dog from the database.
    await db.delete(
      'dogs',
      // Use a `where` clause to delete a specific dog.
      where: 'id = ?',
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [id],
    );
  }

  // Create a Dog and add it to the dogs table
  var fido = Dog(
    id: 0,
    name: 'Fido',
    age: 35,
  );

  await insertDog(fido);

  // Now, use the method above to retrieve all the dogs.
  print(await dogs()); // Prints a list that include Fido.

  // Update Fido's age and save it to the database.
  fido = Dog(
    id: fido.id,
    name: fido.name,
    age: fido.age + 7,
  );
  await updateDog(fido);

  // Print the updated results.
  print(await dogs()); // Prints Fido with age 42.

  // Delete Fido from the database.
  await deleteDog(fido.id);

  // Print the list of dogs (empty).
  print(await dogs());
}

class Dog {
  final int id;
  final String name;
  final int age;

  Dog({
    required this.id,
    required this.name,
    required this.age,
  });

  // Convert a Dog into a Map. The keys must correspond to the names of the
  // columns in the database.
  Map<String, Object?> toMap() {
    return {
      'id': id,
      'name': name,
      'age': age,
    };
  }

  // Implement toString to make it easier to see information about
  // each dog when using the print statement.
  @override
  String toString() {
    return 'Dog{id: $id, name: $name, age: $age}';
  }
}