如何在SQFlite中插入POJO对象和列表

Question

我正试图将数据从API存储到sqflite，但我必须插入对象和列表，但在此过程中，会出现以下错误：

ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(java.lang.String cannot be cast to java.lang.Integer) sql 'INSERT INTO articleTable (id, created_on, title, summary, details, tags, featured_image, author, category) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)' args [21, 2020-04-14 04:04:57, Singer Jose ...}]

这是型号Article类：

@JsonSerializable(explicitToJson: true)
class Article {
Article(this.id, this.created_on, this.title, this.details,
this.featured_image, this.author, this.category, this.summary, this.tags);
int id;
String created_on;
String title;
String summary;
String details;
List<String> tags;
String featured_image;
Author author;
Category category;
factory Article.fromJson(Map<String, dynamic> json) =>
_$ArticleFromJson(json);
Map<String, dynamic> toJson() => _$ArticleToJson(this);
}

Article模型类的自动生成文件：

/ GENERATED CODE - DO NOT MODIFY BY HAND
part of 'article.dart';
// **************************************************************************
// JsonSerializableGenerator
// **************************************************************************
Article _$ArticleFromJson(Map<String, dynamic> json) {
return Article(
json['id'] as int,
json['created_on'] as String,
json['title'] as String,
json['details'] as String,
json['featured_image'] as String,
json['author'] == null
? null
: Author.fromJson(json['author'] as Map<String, dynamic>),
json['category'] == null
? null
: Category.fromJson(json['category'] as Map<String, dynamic>),
json['summary'] as String,
(json['tags'] as List)?.map((e) => e as String)?.toList(),
);
}
Map<String, dynamic> _$ArticleToJson(Article instance) => <String, dynamic>{
'id': instance.id,
'created_on': instance.created_on,
'title': instance.title,
'summary': instance.summary,
'details': instance.details,
'tags': instance.tags,
'featured_image': instance.featured_image,
'author': instance.author?.toJson(),
'category': instance.category?.toJson(),
};

这就是我插入Sqflite的地方：

class ArticleApiProvider {
Future<List<Article>> fetchArticles() async {
final response =
await http.get('https://api.xyxyxyxy');
if (response.statusCode == 200) {
List jsonResponse = jsonDecode(response.body);
return jsonResponse.map((article) {
articleBloc.addArticles(Article.fromJson(article));
}).toList();
} else {
throw Exception('Failed to retrieve articles');
}
}
}

我的Bloc类别：

class ArticleBloc {
final _repository = Repository();
final _articleFetcher = PublishSubject<List<Article>>();
Stream<List<Article>> get allArticles => _articleFetcher.stream;
fetchArticles() async {
List<Article> articles = await _repository.fetchArticles();
_articleFetcher.sink.add(articles);
}
getArticlesCached() async {
// sink is a way of adding data reactively to the stream
// by registering a new event
_articleFetcher.sink.add(await _repository.getAllArticles());
}
void addArticles(Article article) async {
await _repository.insertArticle(article);
}
dispose() {
_articleFetcher.close();
}
}
final articleBloc = ArticleBloc();

这是DatabaseProvider类

final articlesTable = "articleTable";
class DatabaseProvider {
static final DatabaseProvider dbProvider = DatabaseProvider();
Database _database;
String id = "id";
String createdOn = "created_on";
String title = "title";
String summary = "summary";
String details = "details";
String featuredImage = "featured_image";
String author = "author";
String category = "category";
String tags = "tags";
Future<Database> get database async {
if (_database != null) return _database;
_database = await createDatabase();
return _database;
}
createDatabase() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
// NewVision.db is our database instance name
String path = join(documentsDirectory.path, "NewVision.db");
var database = await openDatabase(path,
version: 1, onCreate: initDB, onUpgrade: onUpgrade);
return database;
}
void initDB(Database db, int version) async {
await db.execute(
'CREATE TABLE $articlesTable($id INTEGER PRIMARY KEY AUTOINCREMENT, $createdOn TEXT,  $title TEXT ,'
' $summary TEXT , $details TEXT , '
'$tags TEXT,  $featuredImage TEXT , $author TEXT , $category TEXT )');
}
void onUpgrade(Database db, int oldVersion, int newVersion) {
if (newVersion > oldVersion) {}
}
}
String join(String path, String s) {
return path + s;
}

其余的文件确实遵循Bloc体系结构，为了使问题变得准确，我认为从我提供的内容来看，我可以得到一些帮助。

我将感谢你的帮助。

Answer 1

感谢您的详细报告。我的假设是，错误来自您的tags属性。字符串列表不是SQLite中支持的类型。出于兼容性原因，sqflite支持List of int(对于Blob，但Uint8List可能是未来唯一受支持的类型(，因此转换错误可能来自此。

在进行另一项调查之前，您应该尝试对标记进行编码(json或逗号分隔的字符串(。

此问题也适用于author和category字段。你必须压平你的模型。请参阅支持的类型帮助部分：https://github.com/tekartik/sqflite/blob/master/sqflite/doc/supported_types.md

基本上，int、double、String和Uint8List(blob(是唯一受支持的类型。不幸的是，您必须转换内部的List和Map，json是一种解决方案。

但我同意报告的错误无助于发现问题，这肯定可以改进。。。