flutter (更新：如何在sqflite中存储Listdata类型)sqflite错误< String>：数据库异常(java.lang.字符串不能转换为java.lang.整数)

m1m5dgzv  于 2023-01-14  发布在  Flutter
关注(0)|答案(2)|浏览(135)

我有一个sqflite数据库，它的表中除了唯一键之外的所有列都设置为BLOB数据类型。目的是存储为二进制数据。我对dart和flutter非常陌生，所以我尽量保持简单。我有int，double，String，List<String>，Map<String:String>，和DateTime数据进入这些列。我使用的是.insert（）函数，而不是.rawInsert（）函数。
在我的TEST application中，string或int作为BLOB数据类型进入表中没有任何问题。我使用完全相同的代码插入，只是不同的对象具有不同的列。在我为生产构建的应用程序中，它具有更大、更长的字符串（如base 64中的图像）和更大的整数，我得到了错误。
奇怪的是，我有一个类型检查代码，它把我从服务器上得到的所有整数数据转换成真正的整数数据...整数不可能是整数以外的任何东西。每个值都要经过一个函数，这个函数只能抛出或返回一个整数。
那么，数据库如何获取它试图转换为整数的字符串呢？
我并不是在寻求解决方案，而是寻求我应该采取的额外的故障排除步骤，这些步骤可能是由于大脑疲劳或缺乏经验而遗漏的。
下面是所引发异常的堆栈跟踪：

I/flutter ( 6502): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #1      SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:29:7)
I/flutter ( 6502): #2      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite/src/database_mixin.dart:183:15)
I/flutter ( 6502): #3      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> (package:sqflite/src/database_mixin.dart:340:14)
I/flutter ( 6502): #4      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> (package:sqflite/src/database_mixin.dart:290:22)
I/flutter ( 6502): #5      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:31:26)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #6      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized (package:sqflite/src/database_mixin.dart:286:43)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #7      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite/src/database_mixin.dart:307:7)
I/flutter ( 6502): #8      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawInsert (package:sqflite/src/database_mixin.dart:339:12)
I/flutter ( 6502): #9      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.rawInsert (package:sqflite/src/database_mixin.dart:44:15)
I/flutter ( 6502): #10     _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.insert (package:sqflite/src/database_mixin.dart:54:12)
I/flutter ( 6502): #11     AppPersistenceManager.insertIntoDatabase (package:gogreen_utility_belt/app/AppPersistenceManager.dart:120:37)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #12     AppNetwork._triggerProductFetchAndCacheWith.<anonymous closure> (package:gogreen_utility_belt/app/AppNetwork.dart:167:38)
I/flutter ( 6502): #13     _rootRunUnary (dart:async/zone.dart:1132:38)
I/flutter ( 6502): #14     _CustomZone.runUnary (dart:async/zone.dart:1029:19)
I/flutter ( 6502): #15     _FutureListener.handleValue (dart:async/future_impl.dart:126:18)
I/flutter ( 6502): #16     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:639:45)
I/flutter ( 6502): #17     Future._propagateToListeners (dart:async/future_impl.dart:668:32)
I/flutter ( 6502): #18     Future._chainCoreFuture (dart:async/future_impl.dart:454:7)
I/flutter ( 6502): #19     Future._complete (dart:async/future_impl.dart:466:9)
I/flutter ( 6502): #20     _SyncCompleter.complete (dart:async/future_impl.dart:51:12)
I/flutter ( 6502): #21     _AsyncAwaitCompleter.complete (dart:async-patch/async_patch.dart:28:18)
I/flutter ( 6502): #22     _completeOnAsyncReturn (dart:async-patch/async_patch.dart:294:13)
I/flutter ( 6502): #23     compute (package:flutter/src/foundation/_isolates_io.dart)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #24     AppNetwork._triggerProductFetchAndCacheWith (package:gogreen_utility_belt/app/AppNetwork.dart:153:55)
I/flutter ( 6502): <asynchronous suspension>
I/flutter ( 6502): #25     AppNetwork.fetchAndCacheAllProducts (package:gogreen_utility_belt/app/AppNetwork.dart:195:9)
I/flutter ( 6502): #26     _asyncThenWrapperHelper.<anonymous closure> (dart:async-patch/async_patch.dart:77:64)
I/flutter ( 6502): #27     _rootRunUnary (dart:async/zone.dart:1132:38)
I/flutter ( 6502): #28     _CustomZone.runUnary (dart:async/zone.dart:1029:19)
I/flutter ( 6502): #29     _FutureListener.handleValue (dart:async/future_impl.dart:126:18)
I/flutter ( 6502): #30     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:639:45)
I/flutter ( 6502): #31     Future._propagateToListeners (dart:async/future_impl.dart:668:32)
I/flutter ( 6502): #32     Future._complete (dart:async/future_impl.dart:473:7)
I/flutter ( 6502): #33     _cancelAndValue (dart:async/stream_pipe.dart:63:12)
I/flutter ( 6502): #34     Stream.first.<anonymous closure> (dart:async/stream.dart:1190:11)
I/flutter ( 6502): #35     _rootRunUnary (dart:async/zone.dart:1132:38)
I/flutter ( 6502): #36     _CustomZone.runUnary (dart:async/zone.dart:1029:19)
I/flutter ( 6502): #37     _CustomZone.runUnaryGuarded (dart:async/zone.dart:931:7)
I/flutter ( 6502): #38     _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:336:11)
I/flutter ( 6502): #39     _BufferingStreamSubscription._add (dart:async/stream_impl.dart:263:7)
I/flutter ( 6502): #40     _SyncBroadcastStreamController._sendData (dart:async/broadcast_stream_controller.dart:375:20)
I/flutter ( 6502): #41     _BroadcastStreamController.add (dart:async/broadcast_stream_controller.dart:250:5)
I/flutter ( 6502): #42     _AsBroadcastStreamController.add (dart:async/broadcast_stream_controller.dart:474:11)
I/flutter ( 6502): #43     _rootRunUnary (dart:async/zone.dart:1136:13)
I/flutter ( 6502): #44     _CustomZone.runUnary (dart:async/zone.dart:1029:19)
I/flutter ( 6502): #45     _CustomZone.runUnaryGuarded (dart:async/zone.dart:931:7)
I/flutter ( 6502): #46     _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:336:11)
I/flutter ( 6502): #47     _BufferingStreamSubscription._add (dart:async/stream_impl.dart:263:7)
I/flutter ( 6502): #48     _SyncStreamController._sendData (dart:async/stream_controller.dart:764:19)
I/flutter ( 6502): #49     _StreamController._add (dart:async/stream_controller.dart:640:7)
I/flutter ( 6502): #50     _StreamController.add (dart:async/stream_controller.dart:586:5)
I/flutter ( 6502): #51     _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:172:12)

下面是其中一些函数的外观：

initDatabase() async {
  print(new Trace.from(StackTrace.current).terse.frames[0]);
  Directory documentsDirectory;
  print(
      'documentsDirectory set to null, about to assign with an await method');
  documentsDirectory = await getApplicationDocumentsDirectory();
  print('passed doc dir');
  String path = join(documentsDirectory.path, "TestDB.db");
  print('passed join');
  print(Trace.from(StackTrace.current).terse);
  return await openDatabase(path, version: 1, onOpen: (db) {
    print('in onOpen');
  }, onCreate: (Database database, int version) async {
    print('in onCreate async');
    for (DatabaseTable databaseTable in DatabaseTable.values) {
      print(
          'reached inside of "for (DatabaseTable databaseTable in DatabaseTable.values)"');
      String executionString =
          "CREATE TABLE IF NOT EXISTS ${RawValue.databaseTable(databaseTable)}("
          "$uniqueRowKey INTEGER PRIMARY KEY,";

      JSONSerializable objectExample;
      switch (databaseTable) {
        case DatabaseTable.Product:
          objectExample = Product.example;
      }
      print('getting parameters');
      List<String> parameters = [];
      for (String key
          in getDatabaseKeyedMapFrom(serialObject: objectExample).keys) {
        parameters.add(key);
      }
      print('getting unique keys');
      List<String> uniqueKeys = [];
      var rawUniqueProperties = objectExample.uniqueProperties();
      for (String property in rawUniqueProperties) {
        uniqueKeys.add(getDatabaseKeyFrom(jsonKey: property));
      }
      print('iterating through parameters');
      for (String parameter in parameters) {
        if (parameter != parameters.first) {
          executionString += ',';
        }
        if (uniqueKeys.contains(parameter)) {
          uniqueKeys.remove(parameter);
          executionString += "$parameter BLOB UNIQUE";
          continue;
        }
        executionString += "$parameter BLOB";
      }
      executionString += ')';
      print('executing string on database');
      await database.execute(executionString);
    }
  });
}

/// Attempts to insert the object into its corresponding table.
Future<DatabaseLocation> insertIntoDatabase(JSONSerializable object) async {
  print(new Trace.from(StackTrace.current).terse.frames[0]);
  final localDatabase = await database;
  String tableName = RawValue.databaseTable(getTableFor(object));
  try {
    Map<String, dynamic> dbKeyedMap =
        getDatabaseKeyedMapFrom(serialObject: object);
    int row = await localDatabase.insert(tableName, dbKeyedMap,
        conflictAlgorithm: ConflictAlgorithm.replace);
    return DatabaseLocation(table: tableName, row: row);
  } catch (error, trace) {
    printWrapped(error.toString());
    printWrapped(trace.toString());
    rethrow;
  }
}

///converts the String keys of any map representing an object into snake_case
Map<String, dynamic> getDatabaseKeyedMapFrom(
    {JSONSerializable serialObject}) {
  print(new Trace.from(StackTrace.current).terse.frames[0]);
  Map<String, dynamic> jsonKeyedMap = serialObject.toJson();
  List<String> jsonPropertyNames = serialObject.propertyNames();
  Map<String, dynamic> databaseKeyedMap = {};
  for (String jsonPropertyName in jsonPropertyNames) {
    ReCase reCase = ReCase(jsonPropertyName);
    String databasePropertyName = reCase.snakeCase;
    databaseKeyedMap[databasePropertyName] = jsonKeyedMap[jsonPropertyName];
  }
  return databaseKeyedMap;
}

///converts the String keys of any map representing an object into camelCase
Map<String, dynamic> getJSONKeyedMapFrom(
    {Map<String, dynamic> databaseKeyedMap}) {
  ////print(new Trace.from(StackTrace.current).terse.frames[0]);
  List<String> databasePropertyNames = databaseKeyedMap.keys;
  Map<String, dynamic> jsonKeyedMap = {};
  for (String databasePropertyName in databasePropertyNames) {
    ReCase reCase = ReCase(databasePropertyName);
    String jsonPropertyName = reCase.camelCase;
    jsonKeyedMap[jsonPropertyName] = databaseKeyedMap[databasePropertyName];
  }
  return jsonKeyedMap;
}

下面是数据库中使用的Product model。

更新：

我将生产代码模型中表示的所有数据类型添加到我的测试应用模型中，并且能够复制错误。请参见顶部的测试应用链接。我逐个删除新的数据类型，直到错误消失，并且能够发现是List数据类型生成了此错误。我仔细检查了Map，该数据类型不会导致任何问题。
所以，精炼问题：我怎么用sqflite存储一个字符串列表呢？我不想把它存储为原始的JSON字符串...那太糟糕了...

flutter

2条答案

按热度按时间
carvr3hs

carvr3hs1#

你必须展平你的模型。请参阅supported types帮助部分。
基本上int，double，String和Uint8List（blob）是唯一支持的类型。不幸的是，你必须转换你的内部List<String>和Map<String, Object?>，json是一个解决方案。

赞(0）分享  回复(0）举报  2023-01-14
bgtovc5b

bgtovc5b2#

经过一番研究，我认为我对BLOB的理解是不正确的--我认为它意味着它将对象存储为二进制数据，而且我还认为在SQLite中，如果一个表是BLOB，它将强制所有数据在插入时转换为二进制，或者如果不是，至少抛出。BLOB的意思是“你想要的任何数据类型”。我还发现SQLite会尝试自动转换对象的数据类型。因此，复杂的数据类型，如列表，需要转换成SQLite可以转换成自己的类型，然后再存储。SQLite中没有Array或Object类型，因此List无法工作。
我解决这个问题的方法是在插入数据库之前将复杂的数据类型转换成JSON字符串，然后在从数据库中获取/示例化对象时将其转换回List。这样一来，每次需要使用/更改对象属性或其中的某些内容时，我就不必处理将该属性从JSON中转换出来的问题。虽然不太优雅，但它确实有效，而且我理解它。

赞(0）分享  回复(0）举报  2023-01-14
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com