flutter - 如何使用Flutter和sqflite从文件开始在应用程序上加载数据？

Question

我有一个完整的数据列表，我想在启动时加载到我的应用程序中。我能够提取到JSON文件中，因此可以将其加载到表中，但是加载不正确，而且似乎多次添加。看来我是通过 Assets 正确加载了文件(打印_loadIngredients中的值会为我提供正确的结果)。但是，当将其保存到数据库中时，它只会保存为整数，这就是 View 中显示的内容。

我在任何地方都找不到很好的例子。

我要去哪里错了？

在我的DatabaseHandler中:

class DatabaseHandler{
  DatabaseHandler._();

  static final DatabaseHandler db = DatabaseHandler._();

  static DatabaseHandler get() {
    return db;
  }

  static Database _database;

  final databaseName = "recipe.db";

  DatabaseHandler._createInstance();

  Future<Database> get database async {
    if (_database != null)
      return _database;

    _database = await initDB();
    return _database;
  }


  initDB() async {
    var path = await getDatabasesPath();
    print("Creating tables at path: $path");
    var dbPath = join(path, 'recipe.db');

    Database dbConnection = await openDatabase(dbPath, version: 1,
        onCreate: (Database db, int version) async {
           return db.execute(
             "CREATE TABLE ingredients(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
           );
        }
    );

    await _loadIngredients(dbConnection);
    return dbConnection;
  }

  _loadIngredients(Database db) async {
    Batch batch = db.batch();
    List<Map<String, dynamic>> records = await db.query('ingredients');
    print(records);

    String ingredientsJson = await rootBundle.loadString('assets/json/ingredients.json');
    List ingredientsList = json.decode(ingredientsJson);

    ingredientsList.forEach((val) {
      print(val);
      Ingredient ingredient = Ingredient.fromMap(val);
      batch.insert("ingredients", ingredient.toMap(false));
    });

    var results = await batch.commit();
  }

}

我的食材分类:

class Ingredient {
  int id;
  String name;
  String categoryName;
  DateTime dateCreated;

  Ingredient(this.id, this.name, this.categoryName, this.dateCreated);

  Map<String, dynamic> toMap(bool forUpdate) {
    if(dateCreated == null) {
      dateCreated = new DateTime.now();
    }
    var data = {
//      'id': id,  since id is auto incremented in the database we don't need to send it to the insert query.
      'name': utf8.encode(name),
      'category_name': utf8.encode(categoryName),
      'date_created': epochFromDate( dateCreated )
    };
    if(forUpdate){
      data["id"] = this.id;
    }
    return data;
  }

  Ingredient.fromMap(Map map){
    id = map["id"];
    name = map["name"];
    categoryName = map["category_name"];
    dateCreated = map["date_created"];
  }

// Converting the date time object into int representing seconds passed after midnight 1st Jan, 1970 UTC
  int epochFromDate(DateTime dt) {
    return dt.millisecondsSinceEpoch ~/ 1000 ;
  }
// overriding toString() of the note class to print a better debug description of this custom class
  @override toString() {
    return {
      'id': id,
      'name': name,
      'category_name': categoryName,
      'date_created': epochFromDate( dateCreated )
    }.toString();
  }

}

我正在初始化数据库的主页类:

class HomePage extends StatefulWidget {
  @override
  _HomePageState createState() => _HomePageState();
}

class _HomePageState extends State<HomePage> {

  List<Map<String, dynamic>> _allIngredientsInQueryResult = [];

  var notesViewType ;
  @override void initState() {
    super.initState();
    notesViewType = viewType.Staggered;
    DatabaseHandler.db.initDB();
    retrieveAllIngredientsFromDatabase();
  }

@override
  Widget build(BuildContext context) {
    return
      Container(
         child: _ingredientList()
      );
  }

  Widget _ingredientList() {
    return Container(
        child: ListView.separated(
          padding: const EdgeInsets.all(8),
          itemCount: _allIngredientsInQueryResult.length,
          itemBuilder: (BuildContext context, int index) {
            return Container(
              height: 50,
              color: Colors.amber[100],
              child: Center(child: Text('Entry ${_allIngredientsInQueryResult[index]["name"]}')),
            );
          },
          separatorBuilder: (BuildContext context, int index) => const Divider(),
        )
    );
  }

retrieveAllIngredientsFromDatabase() async {
    var _testData = await DatabaseHandler.db.selectAllIngredients();
    setState(() {
      _allIngredientsInQueryResult = _testData;
    });
  }
}

我在应用程序中看到的图像:



Ingredients json

Answer 1

如果使用utf8.encode(name)，则将String转换为类似flour = [102, 108, 111, 117, 114]的字节
当显示此值时，还必须设置一个utf8.decode(map["name"])
在您的示例中，类似

Text('Entry ' + utf8.decode(${_allIngredientsInQueryResult[index]["name"]})))

每次您的 initDB()调用时，数据都会再次出现在数据库中。您只能在Sqlite DB的onCreate部分中执行此操作

initDB() async {
    var path = await getDatabasesPath();
    print("Creating tables at path: $path");
    var dbPath = join(path, 'recipe.db');

    Database dbConnection = await openDatabase(dbPath, version: 1,
        onCreate: (Database db, int version) async {
          await db.execute(
            "CREATE TABLE ingredients(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, category_name TEXT, date_created INTEGER)",
          );

          await _loadIngredients(db);
        }
    );

    return dbConnection;
  }

我也将使用您的 model class而不是动态 map

定义_allIngredientsInQueryResult

List<Ingredient> _allIngredientsInQueryResult = new List();

使用fromMap()获得selectAllIngredients

  Future<List<Ingredient>> selectAllIngredients() async {
    var dbClient = await database;
    List<Map> result = await dbClient.query('ingredients');

    List<Ingredient> r_ingredient = result.map((i) => Ingredient.fromMap(i)).toList();

    return r_ingredient;
  }

在fromMap()中设置Decode

  Ingredient.fromMap(Map map){
    id = map["id"];
    name = utf8.decode(map["name"]);
    categoryName = utf8.decode(map["category_name"]);
    dateCreated = DateTime.fromMillisecondsSinceEpoch(map["date_created"]);
  }

获取成分

  retrieveAllIngredientsFromDatabase() async {

    DatabaseHandler.db.selectAllIngredients().then((List<Ingredient> r_ingredient) {
      setState(() {
        _allIngredientsInQueryResult = r_ingredient;
      });
    });

  }

在列表 View 中显示

Text('Entry ' + _allIngredientsInQueryResult[index].name)