# 用 SQLite 做数据持久化

> 如何使用 SQLite 做数据持久化。



<?code-excerpt path-base="cookbook/persistence/sqlite/"?>

:::note

This guide uses the [sqflite package][].
This package only supports apps that run on
macOS, iOS, or Android.

本示例使用了 [sqflite package][]。
该 package 仅支持 macOS、iOS 和 Android。

:::

[sqflite package]: https://pub-web.flutter-io.cn/packages/sqflite

If you are writing an app that needs to persist and query large amounts of data on
the local device, consider using a database instead of a local file or
key-value store. In general, databases provide faster inserts, updates,
and queries compared to other local persistence solutions.

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

Flutter apps can make use of the SQLite databases via the
[`sqflite`][] plugin available on pub.dev.
This recipe demonstrates the basics of using `sqflite`
to insert, read, update, and remove data about various Dogs.

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

If you are new to SQLite and SQL statements, review the
[SQLite Tutorial][] to learn the basics before
completing this recipe.

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

This recipe uses the following steps:

总共有以下的步骤：

  1. Add the dependencies.

     添加依赖；

  2. Define the `Dog` data model.

     定义 `Dog (狗)` 数据模型；

  3. Open the database.

     打开数据库；

  4. Create the `dogs` table.

     创建 `dogs` 数据表；

  5. Insert a `Dog` into the database.

     将一条 `Dog` 数据插入数据库；

  6. Retrieve the list of dogs.

     查询所有狗狗的数据；

  7. Update a `Dog` in the database.

     更新（修改）一条 `Dog` 的数据；

  7. Delete a `Dog` from the database.

     删除一条 `Dog` 的数据。

## 1. Add the dependencies

## 1. 添加依赖

To work with SQLite databases, import the `sqflite` and
`path` packages.

为了使用 SQLite 数据库，首先需要导入 `sqflite` 和 
`path` package。

  * The `sqflite` package provides classes and functions to
    interact with a SQLite database.

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

  * The `path` package provides functions to
    define the location for storing the database on disk.

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

To add the packages as a dependency,
run `flutter pub add`:

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

```console
$ flutter pub add sqflite path
```

Make sure to import the packages in the file you'll be working in.

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

<?code-excerpt "lib/main.dart (imports)"?>
```dart
import 'dart:async';

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

## 2. Define the Dog data model

## 2. 定义狗狗的数据模型

Before creating the table to store information on Dogs, take a few moments to
define the data that needs to be stored. For this example, define a Dog class
that contains three pieces of data:
A unique `id`, the `name`, and the `age` of each dog.

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

<?code-excerpt "lib/step2.dart"?>
```dart
class Dog {
  final int id;
  final String name;
  final int age;

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

## 3. Open the database

## 3. 打开数据库

Before reading and writing data to the database, open a connection
to the database. This involves two steps:

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

  1. Define the path to the database file using `getDatabasesPath()` from the
  `sqflite` package, combined with the `join` function from the `path` package.
  
     使用 `sqflite` package 里的 `getDatabasesPath` 方法并配合 `path` package里的
     `join` 方法定义数据库的路径。
     
  2. Open the database with the `openDatabase()` function from `sqflite`.
  
  	 使用 `sqflite` package 里的 `openDatabase` 方法打开数据库。

:::note
In order to use the keyword `await`, the code must be placed
inside an `async` function. You should place all the following
table functions inside `void main() async {}`.
:::

<?code-excerpt "lib/step3.dart (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. Create the `dogs` table

## 4. 创建 `dogs` 表

Next, create a table to store information about various Dogs.
For this example, create a table called `dogs` that defines the data
that can be stored. Each `Dog` contains an `id`, `name`, and `age`.
Therefore, these are represented as three columns in the `dogs` table.

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


  1. The `id` is a Dart `int`, and is stored as an `INTEGER` SQLite
     Datatype. It is also good practice to use an `id` as the primary
     key for the table to improve query and update times.
     
     `id` 是 Dart 的 `int` 类型，在数据表中是 SQLite 的 `INTEGER` 数据类型。
     最佳实践是将 `id` 作为数据库表的主键，用以改善查询和修改的时间。
     
  2. The `name` is a Dart `String`, and is stored as a `TEXT` SQLite
     Datatype.
     
     `name` 是Dart的 `String`类型，在数据表中是SQLite的 `TEXT` 数据类型。
     
  3. The `age` is also a Dart `int`, and is stored as an `INTEGER`
     Datatype.
     
     `age` 也是Dart的 `int` 类型，在数据表中是SQLite的 `INTEGER` 数据类型。

For more information about the available Datatypes that can be stored in a
SQLite database, see the [official SQLite Datatypes documentation][].

关于 SQLite 数据库能够存储的更多的数据类型信息请查阅官方的
[SQLite Datatypes 文档](https://www.sqlite.org/datatype3.html)。

<?code-excerpt "lib/main.dart (openDatabase)"?>
```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. Insert a Dog into the database

## 5. 插入一条狗狗的数据

Now that you have a database with a table suitable for storing information
about various dogs, it's time to read and write data.

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

First, insert a `Dog` into the `dogs` table. This involves two steps:

首先，在 `dogs` 数据表中插入一条 `Dog` 数据。分以下两步：

1. Convert the `Dog` into a `Map`
  
   把 `Dog` 转换成一个 `Map` 数据类型；
     
2. Use the [`insert()`][] method to store the
   `Map` in the `dogs` table.
  
   使用 [`insert()`][] 方法把 `Map` 保存到 `dogs` 数据表中。

<?code-excerpt "lib/main.dart (Dog)"?>
```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}';
  }
}
```

<?code-excerpt "lib/main.dart (insertDog)"?>
```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,
  );
}
```

<?code-excerpt "lib/main.dart (fido)"?>
```dart
// Create a Dog and add it to the dogs table
var fido = Dog(id: 0, name: 'Fido', age: 35);

await insertDog(fido);
```

## 6. Retrieve the list of Dogs

## 6. 查询狗狗列表

Now that a `Dog` is stored in the database, query the database
for a specific dog or a list of all dogs. This involves two steps:

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

  1. Run a `query` against the `dogs` table. This returns a `List<Map>`.
  
     调用 `dogs` 表对像的 `query` 方法。这将返回一个`List <Map>`。
     
  2. Convert the `List<Map>` into a `List<Dog>`.
  
     将 `List<Map>` 转换成 `List<Dog>` 数据类型。

<?code-excerpt "lib/main.dart (dogs)"?>
```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),
  ];
}
```

<?code-excerpt "lib/main.dart (print)"?>
```dart
// Now, use the method above to retrieve all the dogs.
print(await dogs()); // Prints a list that include Fido.
```

## 7. Update a `Dog` in the database

## 7. 修改一条 `Dog` 数据

After inserting information into the database,
you might want to update that information at a later time.
You can do this by using the [`update()`][]
method from the `sqflite` library.

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

This involves two steps:

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

  1. Convert the Dog into a Map.
  
     将一条狗狗的数据转换成 `Map` 数据类型；
  
  2. Use a `where` clause to ensure you update the correct Dog.
     
     使用  `where` 语句定位到具体将要被修改的数据。

<?code-excerpt "lib/main.dart (update)"?>
```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],
  );
}
```

<?code-excerpt "lib/main.dart (update2)"?>
```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.
```

:::warning

Always use `whereArgs` to pass arguments to a `where` statement.
This helps safeguard against SQL injection attacks.

使用 `whereArgs` 将参数传递给 `where` 语句。有助于防止 SQL 注入攻击。

Do not use string interpolation, such as `where: "id = ${dog.id}"`!

这里不要使用字符串模板，比如： `where: "id = ${dog.id}"`！

:::


## 8. Delete a `Dog` from the database

## 8. 删除一条 `Dog` 的数据

In addition to inserting and updating information about Dogs,
you can also remove dogs from the database. To delete data,
use the [`delete()`][] method from the `sqflite` library.

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

In this section, create a function that takes an id and deletes the dog with
a matching id from the database. To make this work, you must provide a `where`
clause to limit the records being deleted.

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


<?code-excerpt "lib/main.dart (deleteDog)"?>
```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],
  );
}
```

## Example

## 示例

To run the example:

运行示例需要以下几步：

  1. Create a new Flutter project.

     创建一个新的 Flutter 工程；

  2. Add the `sqflite` and `path` packages to your `pubspec.yaml`.

     将 `sqflite` 和 `path` package 添加到 `pubspec.yaml` 文件里；

  3. Paste the following code into a new file called `lib/db_test.dart`.

     将以下代码粘贴在 `lib/db_test.dart` 文件里（若无则新建，若有则覆盖）；

  4. Run the code with `flutter run lib/db_test.dart`.

     运行 `flutter run lib/db_test.dart`。

<?code-excerpt "lib/main.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}';
  }
}
```

## Use relationships between tables

:::note
This is a more advanced use case and not required for basic usage.
:::

In real-world applications, you often need to model relationships between tables.

For example, instead of storing all data in a single table, you can separate
related data into multiple tables and connect them using foreign keys.

```sql
CREATE TABLE breeds(
  id INTEGER PRIMARY KEY,
  name TEXT
);

You can execute these statements using the `db.execute()` method from the `sqflite` package.

CREATE TABLE dogs(
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  breed_id INTEGER,
  FOREIGN KEY (breed_id) REFERENCES breeds(id)
);
```

In this example, each dog is associated with a breed using the `breed_id` field.
This allows you to organize data more efficiently and avoid duplication.



[`delete()`]: https://pub-web.flutter-io.cn/documentation/sqflite_common/latest/sqlite_api/DatabaseExecutor/delete.html
[`insert()`]: https://pub-web.flutter-io.cn/documentation/sqflite_common/latest/sqlite_api/DatabaseExecutor/insert.html
[`sqflite`]: https://pub-web.flutter-io.cn/packages/sqflite
[SQLite Tutorial]: https://www.sqlitetutorial.net/
[official SQLite Datatypes documentation]: https://www.sqlite.org/datatype3.html
[`update()`]: https://pub-web.flutter-io.cn/documentation/sqflite_common/latest/sqlite_api/DatabaseExecutor/update.html