flutter - Flutter的sqflite查询

Question

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

我使用Sqflite尝试根据英文输入返回德语单词的列表，而用户流的工作方式几乎是这样的:用户输入一个英文单词(例如:“Ability”)，然后用德语返回该单词的同义词列表。

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

这是我的实体:

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'     |

因此，如果用户搜索“再见”，则她/他应该收到['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中添加了向数据库添加新单词的功能。

如果您多次刷新应用程序，则会添加重复的行，原因是您应使数据库字段之一唯一，此处可以是 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;
  }

我希望这就是您想要的。