跳至主要内容

使用 SQLite 持久化数据

如果您正在编写需要在本地设备上持久化和查询大量数据的应用,请考虑使用数据库而不是本地文件或键值存储。通常,与其他本地持久化解决方案相比,数据库提供更快的插入、更新和查询。

Flutter 应用可以通过 pub.dev 上提供的 sqflite 插件利用 SQLite 数据库。此食谱演示了使用 sqflite 插入、读取、更新和删除各种 Dog 数据的基础知识。

如果您不熟悉 SQLite 和 SQL 语句,请在完成此食谱之前查看 SQLite 教程 以了解基础知识。

此食谱使用以下步骤

  1. 添加依赖项。
  2. 定义 Dog 数据模型。
  3. 打开数据库。
  4. 创建 dogs 表。
  5. Dog 插入数据库。
  6. 检索狗列表。
  7. 更新数据库中的 Dog
  8. 从数据库中删除 Dog

1. 添加依赖项

#

要使用 SQLite 数据库,请导入 sqflitepath 包。

  • sqflite 包提供与 SQLite 数据库交互的类和函数。
  • path 包提供定义数据库在磁盘上存储位置的函数。

要将包添加为依赖项,请运行 flutter pub add

flutter pub add sqflite path

确保在您要使用的文件中导入这些包。

dart
import 'dart:async';

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

2. 定义 Dog 数据模型

#

在创建存储 Dog 信息的表之前,请花点时间定义需要存储的数据。对于此示例,定义一个包含三条数据的 Dog 类:唯一的 idname 和每只狗的 age

dart
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 包中的 getDatabasesPath() 以及 path 包中的 join 函数定义数据库文件的路径。
  2. 使用 sqflite 中的 openDatabase() 函数打开数据库。
dart
// 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

#

接下来,创建一个表来存储有关各种 Dog 的信息。对于此示例,创建一个名为 dogs 的表,定义可以存储的数据。每个 Dog 包含一个 idnameage。因此,这些表示为 dogs 表中的三列。

  1. id 是一个 Dart int,并存储为 INTEGER SQLite 数据类型。将 id 用作表的 primary key 也是一个好习惯,以提高查询和更新速度。
  2. name 是一个 Dart String,并存储为 TEXT SQLite 数据类型。
  3. age 也是一个 Dart int,并存储为 INTEGER 数据类型。

有关可以在 SQLite 数据库中存储的可用数据类型的更多信息,请参阅 官方 SQLite 数据类型文档

dart
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. 将 Dog 插入数据库

#

现在您拥有一个包含适合存储各种狗信息的表的数据库,是时候读取和写入数据了。

首先,将 Dog 插入 dogs 表。这涉及两个步骤

  1. Dog 转换为 Map
  2. 使用 insert() 方法将 Map 存储在 dogs 表中。
dart
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}';
  }
}
dart
// 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,
  );
}
dart
// Create a Dog and add it to the dogs table
var fido = Dog(
  id: 0,
  name: 'Fido',
  age: 35,
);

await insertDog(fido);

6. 检索 Dog 列表

#

现在 Dog 已存储在数据库中,请查询数据库以获取特定狗或所有狗的列表。这涉及两个步骤

  1. dogs 表运行 query。这将返回一个 List<Map>
  2. List<Map> 转换为 List<Dog>
dart
// 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),
  ];
}
dart
// Now, use the method above to retrieve all the dogs.
print(await dogs()); // Prints a list that include Fido.

7. 更新数据库中的 Dog

#

将信息插入数据库后,您可能希望稍后更新该信息。您可以使用 sqflite 库中的 update() 方法执行此操作。

这涉及两个步骤

  1. 将 Dog 转换为 Map。
  2. 使用 where 子句确保更新正确的 Dog。
dart
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],
  );
}
dart
// 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

#

除了插入和更新有关 Dog 的信息外,您还可以从数据库中删除狗。要删除数据,请使用 sqflite 库中的 delete() 方法。

在本节中,创建一个函数,该函数接受一个 id 并从数据库中删除具有匹配 id 的狗。要使其正常工作,必须提供 where 子句以限制要删除的记录。

dart
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 运行代码。
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}';
  }
}