Flutter:如何使用SQFlite存储的数据

Question

我真的很难处理Flutter和SQFlite。有很多复杂的例子，但没有一个简单的例子可以用来获取数据并将其放入变量中，以便在小部件或模型中使用。我尝试了很多例子，其中大多数都给出了一个错误"表不存在"。我终于找到了一个行之有效的例子，并试图用它来满足我的需求，但我只是迷失了方向。我是一个新手，虽然不喜欢编码。多年来一直在做网络编码。我已经浪费了几个小时试图让一个简单的查询工作，并在变量中返回它的内容。我真的在考虑完全放弃Flutter，因为处理存储的数据有多难。这应该是基本的东西。为什么这么难！？

不管怎样，这是我的代码，这样你就可以看到我正在努力实现的目标。我可以在DBHelper类中创建数据库、插入和获取数据，但我不知道如何在它之外使用这些数据。我的目标是获得一个简单的示例，然后我可以在整个应用程序中更大规模地实现它。我有不同类型的动态列表等。任何帮助都将不胜感激！

database.dart

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class DBNoteModel {
String id;
String value;
DBNoteModel({this.id, this.value});
Map<String, dynamic> toMap() {
final map = new Map<String, dynamic>();
map["id"] = id;
map["value"] = value;
return map;
}
//to be used when converting the row into object
factory DBNoteModel.fromMap(Map<String, dynamic> data) =>
new DBNoteModel(id: data['id'], value: data['value']);
}
class DBHelper {
Database db;
DBHelper() {
initDatabase();
}
Future<void> initDatabase() async {
db = await openDatabase(join(await getDatabasesPath(), "database.db"),
onCreate: (db, version) {
return db.execute('''
CREATE TABLE notes(id TEXT PRIMARY KEY, value TEXT);
INSERT INTO notes (id, value) VALUES ('home', 'asd');
''');
}, version: 1);
}
Future<void> updateNote(String id, String value) async {
db.rawUpdate("UPDATE notes SET value = '$value' WHERE id = $id");
}
getNote(String id) async {
List<Map> result = await db.rawQuery("SELECT * FROM notes WHERE id = $id");
if (result.length > 0) {
return DBNoteModel.fromMap(result[0]);
}
return null;
}
}

test.dart

import 'package:flutter/material.dart';
import '../../utilities/database.dart';
class TestScreen extends StatelessWidget {
@override
Widget build(BuildContext context) {
final DBHelper _dbHelper = DBHelper();
var note = _dbHelper.getNote('home');
return Text(note.value);
}
}

Answer 1

如果您有多个数据记录，则需要数据模型的List<DBNoteModel>。在这个例子中，它显示了一个Listview

在数据库的onCreate命令中，每个命令都需要一个单独的execute，类似

onCreate: (db, version) async {
await db.execute("CREATE TABLE notes(id TEXT PRIMARY KEY, value TEXT)");
await db.execute("INSERT INTO notes (id, value) VALUES ('home', 'asd')");
await db.execute("INSERT INTO notes (id, value) VALUES ('home2', 'asddf')");
},

DBHelper

class DBHelper {
Database _db;
Future<Database> get db async {
if (_db != null) {
return _db;
}
_db = await initDatabase();
return _db;
}

initDatabase() async {
return await openDatabase(join(await getDatabasesPath(), "database.db"),
onCreate: (db, version) async {
await db.execute("CREATE TABLE notes(id TEXT PRIMARY KEY, value TEXT)");
await db.execute("INSERT INTO notes (id, value) VALUES ('home', 'asd')");
await db.execute("INSERT INTO notes (id, value) VALUES ('home2', 'asddf')");
},
version: 1);
}
Future<void> updateNote({DBNoteModel dbnoteupdate}) async {
_db.update("notes", dbnoteupdate.toMap(), where: 'id = ?', whereArgs: [dbnoteupdate.id]);
}
Future<void> insertNote({DBNoteModel dbnoteupdate}) async {
_db.insert("notes", dbnoteupdate.toMap());
}
Future<List<DBNoteModel>> selectNote(String value) async {
_db = await db;
List<Map> result;
if(value == "")
result = await _db.query("notes");
else {
result = await _db.query("notes", where: "value LIKE '" + value + "%'");
}
List<DBNoteModel> r_DBNoteModel = result.map((i) => DBNoteModel.fromMap(i)).toList();
return r_DBNoteModel;
}
}

在ListView中显示一些数据，选项卡=>更新，文本字段更改=>选择类似？*，在浮动按钮=>插入

class sqFliteSimpleExample extends StatefulWidget {
@override
_sqFliteSimpleExampleState createState() => _sqFliteSimpleExampleState();
}
class _sqFliteSimpleExampleState extends State<sqFliteSimpleExample> {
List<DBNoteModel> dbnotes = new List();
final DBHelper _dbHelper = DBHelper();
TextEditingController editingController = new TextEditingController();
@override
void initState() {
//fetch Notes for the firstTime
getNotes("");
super.initState();
}
getNotes(String where) {
// Select with a where or without
_dbHelper.selectNote(where).then((value) {
// return value is a List<DBNoteModel>
setState(() {
// refresh screen with the new values
dbnotes = value;
});
});
}
@override
Widget build(BuildContext context) {
return new Scaffold(
appBar: new AppBar(title: new Text("SqFilte Notes"),),
body: new Column(
crossAxisAlignment: CrossAxisAlignment.stretch,
mainAxisSize: MainAxisSize.max,
children: <Widget>[
new Expanded(
child: new ListView.builder(
itemCount: dbnotes.length,
itemBuilder: (BuildContext context, index) {
return new ListTile(
title: Text(dbnotes[index].id.toString()),
subtitle: Text(dbnotes[index].value),
onTap: () {
// updates you local Datamodel
dbnotes[index].value = dbnotes[index].value + " update";
// send sql Update
_dbHelper.updateNote(dbnoteupdate: dbnotes[index]).then((value) {
// refresh when it's done
getNotes("");
});
},
);
},
),
),
new Padding(
padding: const EdgeInsets.all(15.0),
child: new TextField(
controller: editingController,
onChanged: (value) => getNotes(value), // refresh the screen every string input in the TextField
decoration: InputDecoration(
hintText: 'search / create',
border: InputBorder.none,
),
),
),
],
),
floatingActionButton: new FloatingActionButton(
child: Icon(Icons.add),
onPressed: () {
//insert a new Note, and refresh when it's done
_dbHelper.insertNote(dbnoteupdate: new DBNoteModel(id: editingController.text, value: editingController.text)).then((value) {
getNotes("");
});
}
),
);
}
}