Flutter：如何将Firestore的List数据存入sqflite数据库？

问题场景 #

我正在开发Flutter项目，使用Firestore作为在线数据库、sqflite作为本地数据库，需要实现用户启动应用时从Firestore同步recipes集合数据到本地sqflite的功能。但运行时出现错误，核心问题是Firestore中recipe_ingredients是List类型，而sqflite不支持直接存储List。

相关代码 #

获取并同步数据的函数 #

Future<List> getRecipeDataList() async {
  List recipes = [];
  int count = await getRecipeCount();
  int recipeID = 101;
  for (int i = 0; i < count; i++, recipeID++) {
    if (await checkIfRecipeDocExists(recipeID.toString()) == true) {
      QuerySnapshot<Map<String, dynamic>> recipesSnapshot = await FirebaseFirestore.instance.collection('recipes').get();
      for(QueryDocumentSnapshot<Map<String, dynamic>> doc in recipesSnapshot.docs){
        final data = doc.data();
        await LocalDatabase.instance.insertRecipe({
          LocalDatabase.recipeID: 100,
          LocalDatabase.recipe_name: data['recipe_name'],
          LocalDatabase.recipe_description: data['recipe_description'],
          LocalDatabase.recipeImageURL: data['recipeImageURL'],
          LocalDatabase.recipe_rating: data['recipe_rating'],
          LocalDatabase.recipe_time: data['recipe_time'],
          LocalDatabase.recipe_ingredients: data['recipe_ingredients'],
        });
        recipes.add(
          Recipes(
            recipeID: doc.id,
            recipeName: data['recipe_name'],
            recipeDescription: data['recipe_description'],
            recipeURL: data['recipeImageURL'],
            recipeRating: data['recipe_rating'],
            recipeTime: data['recipe_time'],
            recipeIngredients: (data['recipe_ingredients'] as List<dynamic>).cast<String>(),
          ),
        );
      }
    }
  }
  return recipes;
}

sqflite数据库类 #

import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
class LocalDatabase {
  //variables
  static const dbName = 'localDatabase.db';
  static const dbVersion = 1;
  static const recipeTable = 'recipes';
  static const recipe_description = 'recipe_description';
  static const recipe_rating = 'recipe_rating';
  static const recipeImageURL = 'recipeImageURL';
  static const recipe_time = 'recipe_time';
  static const recipe_ingredients = 'recipe_ingredients';
  static const recipe_name = 'recipe_name';
  static const recipeID = 'recipeID';
  static const recipe_category = 'recipe_category';

  //Constructor
  static final LocalDatabase instance = LocalDatabase();

  //Initialize Database
  static Database? _database;

  Future<Database?> get database async {
    _database ??= await initDB();
    return _database;
  }

  initDB() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, dbName);
    return await openDatabase(path, version: dbVersion, onCreate: onCreate);
  }

  Future onCreate(Database db, int version) async {
    db.execute('''
      CREATE TABLE $recipeTable (
        $recipeID INTEGER,
        $recipe_name TEXT,
        $recipe_description TEXT,
        $recipe_ingredients TEXT,
        $recipeImageURL TEXT,
        $recipe_category TEXT,
        $recipe_time TEXT,
        $recipe_rating TEXT,
      )
      ''');
  }

  insertRecipe(Map<String, dynamic> row) async {
    Database? db = await instance.database;
    return await db!.insert(recipeTable, row);
  }

  Future<List<Map<String, dynamic>>> readRecipe() async {
    Database? db = await instance.database;
    return await db!.query(recipeTable);
  }

  Future<int> updateRecipe(Map<String, dynamic> row) async {
    Database? db = await instance.database;
    int id = row[recipeID];
    return await db!
        .update(recipeTable, row, where: '$recipeID = ?', whereArgs: [id]);
  }

  Future<int> deleteRecipe(int id) async {
    Database? db = await instance.database;
    return await db!.delete(recipeTable, where: 'recipeID = ?', whereArgs: [id]);
  }
}

错误信息 #

I/flutter (13393): *** WARNING *** I/flutter (13393): I/flutter
(13393): Invalid argument [Ingredients 1, Ingredients 2, Ingredients
3] with type List. I/flutter (13393): Only num, String and
Uint8List are supported. See
https://github.com/tekartik/sqflite/blob/master/sqflite/doc/supported_types.md
for details I/flutter (13393): I/flutter (13393): This will throw an
exception in the future. For now it is displayed once per type.
I/flutter (13393): I/flutter (13393): E/flutter (13393):
[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled
Exception: DatabaseException(java.lang.String cannot be cast to
java.lang.Integer) sql 'INSERT INTO recipes (recipeID, recipe_name,
recipe_description, recipeImageURL, recipe_rating, recipe_time,
recipe_ingredients) VALUES (?, ?, ?, ?, ?, ?, ?)' args [100,
recipe_name, recipe_description, recipeImageURL, recipe_rating,
recipe_time, [Ingredients 1, Ingredients 2, Ingredients 3]] E/flutter
(13393): #0 wrapDatabaseException
(package:sqflite/src/exception_impl.dart:11:7) E/flutter (13393):
E/flutter (13393): #1
SqfliteDatabaseMixin.txnRawInsert.
(package:sqflite_common/src/database_mixin.dart:548:14) E/flutter
(13393): E/flutter (13393): #2
BasicLock.synchronized
(package:synchronized/src/basic_lock.dart:33:16) E/flutter (13393):
E/flutter (13393): #3
SqfliteDatabaseMixin.txnSynchronized
(package:sqflite_common/src/database_mixin.dart:489:14) E/flutter
(13393): E/flutter (13393): #4
LocalDatabase.insertRecipe
(package:recipedia/WidgetsAndUtils/local_database.dart:56:12)
E/flutter (13393): E/flutter (13393): #5
RecipeModel.getRecipeDataList
(package:recipedia/WidgetsAndUtils/recipe_model.dart:78:11) E/flutter
(13393): E/flutter (13393): #6
_LoginState.getRecipeData (package:recipedia/RegistrationAndLogin/login.dart:194:15) E/flutter
(13393): E/flutter (13393):

解决方案 #

sqflite仅支持存储num、String和Uint8List类型，List类型需要转换后才能存储，常用两种方法：

方法1：JSON序列化/反序列化 #

将List转换为JSON字符串存储，读取时再解析回List，这是最通用的方式。

修改插入数据的代码

在getRecipeDataList函数中，把recipe_ingredients转为JSON字符串，同时修复recipeID类型不匹配问题：

// 需先导入dart:convert包
import 'dart:convert';

// 插入数据部分修改
await LocalDatabase.instance.insertRecipe({
  LocalDatabase.recipeID: int.parse(doc.id), // Firestore文档ID是字符串，转成int匹配sqflite的INTEGER类型
  LocalDatabase.recipe_name: data['recipe_name'],
  LocalDatabase.recipe_description: data['recipe_description'],
  LocalDatabase.recipeImageURL: data['recipeImageURL'],
  LocalDatabase.recipe_rating: data['recipe_rating'],
  LocalDatabase.recipe_time: data['recipe_time'],
  LocalDatabase.recipe_ingredients: jsonEncode(data['recipe_ingredients']), // 转为JSON字符串
});

修改读取数据的代码

从sqflite读取时，把JSON字符串解析回List：

Future<List<Recipes>> readRecipes() async {
  Database? db = await instance.database;
  final List<Map<String, dynamic>> maps = await db!.query(recipeTable);
  
  return List.generate(maps.length, (i) {
    return Recipes(
      recipeID: maps[i][LocalDatabase.recipeID].toString(),
      recipeName: maps[i][LocalDatabase.recipe_name],
      recipeDescription: maps[i][LocalDatabase.recipe_description],
      recipeURL: maps[i][LocalDatabase.recipeImageURL],
      recipeRating: maps[i][LocalDatabase.recipe_rating],
      recipeTime: maps[i][LocalDatabase.recipe_time],
      recipeIngredients: jsonDecode(maps[i][LocalDatabase.recipe_ingredients]).cast<String>(), // 解析回List<String>
    );
  });
}

方法2：字符串拼接 #

如果List里都是字符串，且元素不含特定分隔符（比如逗号），可以用分隔符把List拼接成单个字符串，读取时再拆分。

修改插入数据的代码

await LocalDatabase.instance.insertRecipe({
  // 其他字段不变
  LocalDatabase.recipe_ingredients: (data['recipe_ingredients'] as List<String>).join(','), // 用逗号拼接
});

修改读取数据的代码

recipeIngredients: maps[i][LocalDatabase.recipe_ingredients].split(','), // 拆分回List

这种方法比JSON更轻量，但局限性大，若元素可能包含分隔符，优先用JSON方法。

内容的提问来源于stack exchange，提问作者Ahmed Raza