sqlite 如何将List插入到sqflite数据库的列中

brjng4g3  于 2023-02-23  发布在  SQLite
关注(0)|答案(1)|浏览(243)

我正在做一个flutter项目,我有两个数据库,firestore数据库作为在线数据库,sqflite数据库作为本地数据库。
我试图做的是,当用户启动应用程序时,它会从firestore数据库中获取一个食谱集合,并将其本地存储在sqflite数据库中。
函数如下:

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/扑动(13393):***警告***I/ Flutter (13393):I/扑动(13393):类型为列表的参数[成分1,成分2,成分3]无效。I/flutter(13393):仅支持num、String和Uint8List。有关详细信息,请参见https://github.com/tekartik/sqflite/blob/master/sqflite/doc/supported_types.md I/flutter(13393):I/扑动(13393):这将在将来抛出异常。目前,每个类型只显示一次。I/flutter(13393):I/扑动(13393):E/扑动(13393):[错误:flutter/运行时/dart_vm_initializer. cc(41)]未处理的异常:DatabaseException(java. lang. String不能转换为java. lang. Integer)sql 'INSERT INTO配方(配方ID,配方名称,配方描述,配方图像URL,配方评级,配方时间,配方成分)VALUES(?,?,?,?,?,?,?)'参数[100,配方名称,配方描述,配方图像URL,配方评级,配方时间,[成分1,成分2,成分3]] E/扑动(13393):#0 Package 数据库异常(软件包:sqflite/src/exception_impl. dart:11:7)E/ Flutter (13393):E/扑动(13393):#1
数据库混合文件. txn原始数据插入.(软件包:sgm.com/src/数据库混合文件. dart:548:14)E/flutter(13393):E/扑动(13393):#2
基本锁. synchronized(软件包:/src/基本锁. dart:33:16)E/flutter(13393):E/扑动(13393):#3
已同步(软件包:sqflite_common/src/数据库_mixin. dart:489:14)E/抖动(13393):E/扑动(13393):#4
插入配方(软件包:recipedia/小部件和实用工具/本地数据库。dart:56:12)E/flutter(13393):E/扑动(13393):#5
配方模型. getRecipeDataList(软件包:配方百科/小部件和实用工具/配方模型. dart:78:11)E/flutter(13393):E/扑动(13393):#6
_登录状态. getRecipeData(包:recipedia/注册和登录/登录. dart:194:15)E/flutter(13393):E/扑动(13393):
看了这个错误,我发现的问题是firestore数据库中的recipe_ingredient是一个数组,sqflite不支持,我该如何解决这个问题?

syqv5f0l

syqv5f0l1#

如果您按照日志中报告的链接操作:https://github.com/tekartik/sqflite/blob/master/sqflite/doc/supported_types.md
它会给予你一些解决方案。
一种解决方案是将Firestore中的recipe_ingredient列表编码为json字符串,并保存在SQLite中。

代替

recipeIngredients: (data['recipe_ingredients'] as List<dynamic>).cast<String>(),

try(也许你也应该处理null值):

recipeIngredients: jsonEncode(data['recipe_ingredients'])

相关问题