Flutter的sqflite查询

Question

我正在使用一个已经存在的数据库dictionary.db，它有一个表words，有4列id，englishWord，germanWord，isFavorite。

使用Sqflite，我试图返回一个基于英语输入的德语单词列表。用户流的工作原理是这样的:用户输入一个英语单词(例如：“”)，然后我返回一个德语单词的同义词列表。

我的问题是，我不知道怎么做，我正在尝试的方法返回完整的列表，而不仅仅是预期的搜索结果

这是我的实体：

class Word {
  final String id;
  final String eng;
  final String ger;
  final String isFav;

  Word({this.id, this.eng, this.ger, this.isFav});

  Map<String, dynamic> toMap() {
    return {
      'wordId': id,
      'englishWord': eng,
      'germanWord': ger,
      'isFavorite': isFav, 
    };
  }

  factory Word.fromMap(Map<String, dynamic> json) => new Word(
    id: json['wordId'],
    eng: json['englishWord'],
    ger: json['germanWord'],
    isFav: json['isFavorite']
  );
}

这是我的数据库助手类：

class DatabaseHelper{
  DatabaseHelper._();
  static final DatabaseHelper databaseHelper = DatabaseHelper._();
  Database _database;

  static const String DB_NAME = "dict.db";
  static const String TABLE = "words";
  static const String ID = "wordId";
  static const String ENGLISH_WORD = "englishWord";
  static const String GERMAN_WORD = "germanWord";
  static const String IS_FAV = "isFavorite";

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

  Future<Database> getDatabaseInstance() async {
    io.Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, DB_NAME);
    return await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE IF NOT EXISTS $TABLE ("
          "$ID TEXT,"
          "$ENGLISH_WORD TEXT,"
          "$GERMAN_WORD TEXT,"
          "$IS_FAV TEXT"
          ")");
    });
  }

  //This is where I get the search result list
  //I am stuck here I don't know where to go from here, I only get the full list
  Future<List<Word>> searchEnglishResults(String userSearch) async{
    final db = await database;
    var response = await db.query("Word");
    List<Word> list = response.map((c) => Word.fromMap(c)).toList();
    return list;
  }

Answer 1

我假设数据库如下所示：

|id     | englidhWord | germanWord        | isFavorite |
|-------|-------------|-------------------|------------|
| 'id0' | 'bye'       | 'Tschüss'         | 'false'    |
| 'id1' | 'bye'       | 'Auf Wiedersehen' | 'true'     |

因此，如果用户搜索"bye"，她/他应该会收到 'Tschüss', 'Auf Wiedersehen'。

为此，query提供了一些选项，如where和whereArgs，您可以使用这些选项在数据库中搜索特定的行。

在这里，我们想要在数据库中搜索englidhWord字段的值为bye的行。

以下是您的数据库的编辑版本：

import 'dart:io';

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sudoku/src/word.dart';

class DatabaseHelper {
  DatabaseHelper._();
  static final DatabaseHelper databaseHelper = DatabaseHelper._();
  Database _database;

  String DB_NAME = "dict.db";
  static const String TABLE = "words";
  static const String ID = "wordId";
  static const String ENGLISH_WORD = "englishWord";
  static const String GERMAN_WORD = "germanWord";
  static const String IS_FAV = "isFavorite";

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

  Future<Database> getDatabaseInstance() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, DB_NAME);
    return await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE IF NOT EXISTS $TABLE ("
          "$ID TEXT,"
          "$ENGLISH_WORD TEXT,"
          "$GERMAN_WORD TEXT,"
          "$IS_FAV TEXT"
          ")");
    });
  }
   //add new words
   Future<int> add(Word word) async {
    final db = await database;
    var response = await db.insert(TABLE, word.toMap());

    return response;
  }

  //This is where I get the search result list
  //I am stuck here I don't know where to go from here, I only get the full list
  Future<List<Word>> searchEnglishResults(String userSearch) async {
    final db = await database;
    var response = await db
        .query(TABLE, where: '$ENGLISH_WORD = ?', whereArgs: [userSearch]);
    List<Word> list = response.map((c) => Word.fromMap(c)).toList();
    return list;
  }
}

然后我在一个名为MyApp的页面上使用listview显示结果

import 'package:flutter/material.dart';
import 'package:sudoku/src/db.dart';
import 'package:sudoku/src/word.dart';

class MyApp extends StatefulWidget {
  MyApp({Key key}) : super(key: key);

  @override
  _MyAppState createState() => _MyAppState();
}

class _MyAppState extends State<MyApp> {
  DatabaseHelper db;
  @override
  void initState() {
    db = DatabaseHelper.databaseHelper;
    add();
    super.initState();
  }

  add() async {
    // await db.add(Word(eng: 'hi', ger: 'Hallo', id: 'id2', isFav: 'true'));
    // await db.add(Word(eng: 'bye', ger: 'Tschüss', id: 'id0', isFav: 'fasle'));
    // await db.add(
    //     Word(eng: 'bye', ger: 'Auf Wiedersehen', id: 'id0', isFav: 'true'));
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(),
      body: FutureBuilder<List<Word>>(
        future: db.searchEnglishResults('bye'),
        builder: (BuildContext context, AsyncSnapshot<List<Word>> snapshot) {
          if (snapshot.hasData)
            return ListView.builder(
              itemBuilder: (BuildContext context, int index) {
                return ListTile(
                  title: Text(snapshot.data[index].ger),
                  subtitle: Text(snapshot.data[index].eng),
                  trailing: Text(snapshot.data[index].isFav),
                );
              },
              itemCount: snapshot.data.length,
            );

          return Center(
            child: CircularProgressIndicator(),
          );
        },
      ),
    );
  }
}

我在数据库中添加了一个名为" add“的函数，并使用MyApp的initState向数据库中添加新词。

如果你多次刷新app，它会添加重复的行，原因是你应该让其中一个DB字段唯一，这里可以通过id来做到这一点，你应该创建如下表格：

return await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE IF NOT EXISTS $TABLE ("
          "$ID TEXT NOT NULL PRIMARY KEY,"
          "$ENGLISH_WORD TEXT,"
          "$GERMAN_WORD TEXT,"
          "$IS_FAV TEXT"
          ")");
    });

同样在DB的add函数中，你应该选择当它面对重复的行(这里是Word)时应该做什么，有一些选项，其中一个可以替换它，它应该像这样改变：

  //add new words
  Future<int> add(Word word) async {
    final db = await database;
    var response = await db.insert(TABLE, word.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);

    return response;
  }

我希望这就是你要找的。