我试图在Flutter中将列表插入到sql数据库中,但是我不知道该怎么办,有人可以帮助我吗?
当我初始化mi数据库时,我有这个:
Directory documentsDirectory = await getApplicationDocumentsDirectory(); String path = join(documentsDirectory.path, "tuCuestionarioDB.db"); return await openDatabase(path, version: 1, onOpen: (db) {}, onCreate: (Database db, int version) async { await db.execute("CREATE TABLE Question (" "id INTEGER PRIMARY KEY," "subject INTEGER," "topic INTEGER," "question TEXT," "answer INTEGER," "answers TEXT ARRAY," //THIS IS MY LIST<STRING> "rightAnswer INTEGER," "correctly BIT," "answered BIT" ")"); });
我有这个要插入数据:
newQuestion(Question newQuestion) async { final db = await database; //get the biggest id in the table var table = await db.rawQuery("SELECT MAX(id)+1 as id FROM Question"); int id = table.first["id"]; //insert to the table using the new id var raw = await db.rawInsert( "INSERT Into Question (id,subject,topic,question,answer,answers,rightAnswer,correctly,answered)" " VALUES (?,?,?,?,?,?,?,?,?)", [id != null ? id : 0, newQuestion.subject, newQuestion.topic,newQuestion.question,newQuestion.answer,newQuestion.answers,newQuestion.rightAnswer,newQuestion.correctly ? 1 : 0,newQuestion.answered ? 1 : 0]); return raw; }
但是当y尝试插入这样的值时:
{subject: 1, topic: 1, question: What is the best community?, answer: 3, rightAnswer: 0, answers: [Stack Overflow, Facebook, Yahoo Answers], correctly: false, answered: false}
我收到这样的错误:
发生异常。 SqfliteDatabaseException(DatabaseException(java.lang.String无法转换为java.lang.Integer)sql’INSERT Into Question(id,subject,topic,question,answer,answers,rightAnswer,正确,已回答)值(?,?,? ,?,?,?,?,?,?,?)’args [0,1,1,什么是最好的社区?,3,[Stack Overflow,Facebook,Answers Yahoo],0,0,0]})
发生异常。
SqfliteDatabaseException(DatabaseException(java.lang.String无法转换为java.lang.Integer)sql’INSERT Into Question(id,subject,topic,question,answer,answers,rightAnswer,正确,已回答)值(?,?,? ,?,?,?,?,?,?,?)’args [0,1,1,什么是最好的社区?,3,[Stack Overflow,Facebook,Answers Yahoo],0,0,0]})
当我删除anwsers字段时,我没有任何错误
我很确定您不能以这种方式存储字符串列表。根据文档,它仅支持整数。我认为您可以通过两种方式来执行此操作。
一种方法是将答案分成一个字符串。您可以使用join方法,并用一个不会包含任何答案的字符(例如|这样的管道)将其分开。然后,当您从数据库中读取数据时,可以使用选择的字符将字符串拆分回数组。
执行此操作的另一种方法是创建一个表,该表将单个问题与多个答案相关联。这基本上是sudo代码,但是如果您有与此匹配的表,它应该可以工作。
Answer int id autoincremented, int questionId, String answerText, int isCorrectAnswer (0 or 1)
另外,一点也不相关,但是sqflite支持AUTOINCREMENT关键字,我建议您将其用作主键ID。