How to do a database query with SQFlite in Flutter(如何在 Flutter 中使用 SQFlite 进行数据库查询)

本文介绍了如何在 Flutter 中使用 SQFlite 进行数据库查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何使用 SQFlite 插件在 Flutter 中查询 SQLite 数据库中的数据?

我最近一直在努力学习这个，所以我在下面添加我的答案，作为帮助我学习的一种方式，也作为未来其他人的快速参考.

解决方案

添加依赖项

打开 pubspec.yaml 并在依赖项部分添加以下行:

sqflite: ^1.0.0路径提供者:^0.4.1

sqflite 当然是

为了方便您复制和粘贴，这里是 main.dart 的布局代码:

import 'package:flutter/material.dart';//我将我的项目称为flutter_database_operations".你可以为你更新.导入 'package:flutter_database_operations/database_helper.dart';导入'包:sqflite/sqflite.dart'；void main() =>运行应用程序(我的应用程序())；class MyApp 扩展 StatelessWidget {@覆盖小部件构建(BuildContext 上下文){返回 MaterialApp(title: 'SQFlite 演示',主题:主题数据(主色板:Colors.blue，),主页:MyHomePage(),);}}class MyHomePage 扩展了 StatelessWidget {@覆盖小部件构建(BuildContext 上下文){返回脚手架(应用栏:应用栏(标题:文本('sqflite')，),正文:凸起按钮(child: Text('query', style: TextStyle(fontSize: 20),),onPressed: () {_query();},),);}_query() 异步 {//获取对数据库的引用数据库 db = 等待 DatabaseHelper.instance.database;//获取所有行列表<地图>结果 = 等待 db.query(DatabaseHelper.table);//获取单行//列表<地图>结果 = 等待 db.query(DatabaseHelper.table,//列:[DatabaseHelper.columnId, DatabaseHelper.columnName, DatabaseHelper.columnAge],//其中:'${DatabaseHelper.columnId} = ?',//whereArgs: [1]);//原始查询//列表<地图>result = await db.rawQuery('SELECT * FROM my_table WHERE name=?', ['Mary']);//获取结果列表中的每一行并打印出来result.forEach((row) => print(row));}}

继续

这篇文章是我之前文章的发展:SimpleFlutter 中的 SQFlite 数据库示例.有关其他 SQL 操作和建议，请参阅该博文.

How do you query data from SQLite database in Flutter using the SQFlite plugin?

I have been working on learning this recently, so I am adding my answer below as a means to help me learn and also as a quick reference for others in the future.

解决方案

Add the dependencies

Open pubspec.yaml and in the dependencies section add the following lines:

sqflite: ^1.0.0
path_provider: ^0.4.1

The sqflite is the SQFlite plugin of course and the path_provider will help us get the user directory on Android and iPhone. You can check the most up-to-date version numbers here: sqflite and path_provider.

Make a database helper class

I'm keeping a global reference to the database in a singleton class. This will prevent concurrency issues and data leaks. You can also add helper methods (like query) in here for accessing the database.

Create a new file called database_helper.dart and paste in the following code:

import 'dart:io' show Directory;
import 'package:path/path.dart' show join;
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart' show getApplicationDocumentsDirectory;

class DatabaseHelper {

  static final _databaseName = "MyDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'my_table';

  static final columnId = '_id';
  static final columnName = 'name';
  static final columnAge = 'age';

  // make this a singleton class
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // only have a single app-wide reference to the database
  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // lazily instantiate the db the first time it is accessed
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(path,
        version: _databaseVersion,
        onCreate: _onCreate);
  }

  // SQL code to create the database table
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnAge INTEGER NOT NULL
          )
          ''');

    // prepopulate a few rows (consider using a transaction)
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Bob", 23)');
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Mary", 32)');
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Susan", 12)');
  }
}

Note that when the database is created I pre-populated a few rows. This is so that we have something to work with in the query examples below.

Query data

We'll use an async method to do our query because database operations can be expensive.

Get all rows

To do a SELECT * and return everything in the table you just pass in the table name.

  _query() async {

    // get a reference to the database
    Database db = await DatabaseHelper.instance.database;

    // get all rows
    List<Map> result = await db.query(DatabaseHelper.table);

    // print the results
    result.forEach((row) => print(row));
    // {_id: 1, name: Bob, age: 23}
    // {_id: 2, name: Mary, age: 32}
    // {_id: 3, name: Susan, age: 12}
  }

Get a single row

We can pass an argument in for the where parameter to select specific rows that meet our criteria. In this example we will query the row with an ID of 1.

  _query() async {

    // get a reference to the database
    Database db = await DatabaseHelper.instance.database;

    // get single row
    List<String> columnsToSelect = [
      DatabaseHelper.columnId,
      DatabaseHelper.columnName,
      DatabaseHelper.columnAge,
    ];
    String whereString = '${DatabaseHelper.columnId} = ?';
    int rowId = 1;
    List<dynamic> whereArguments = [rowId];
    List<Map> result = await db.query(
        DatabaseHelper.table,
        columns: columnsToSelect,
        where: whereString,
        whereArgs: whereArguments);

    // print the results
    result.forEach((row) => print(row));
    // {_id: 1, name: Bob, age: 23}
  }

The items in the whereArguments list get substituted in place of the ?s in the whereString. In this case there was only one ? so the whereArguments only had one item. If there were two ?s (for example an integer and a string), then you would have two items in the list.

Raw query

If you prefer the familiarity or flexibility of SQL code itself, you can do a raw query. In this example we will select any row whose name column is 'Mary'.

  _query() async {

    // get a reference to the database
    Database db = await DatabaseHelper.instance.database;

    // raw query
    List<Map> result = await db.rawQuery('SELECT * FROM my_table WHERE name=?', ['Mary']);

    // print the results
    result.forEach((row) => print(row));
    // {_id: 2, name: Mary, age: 32}
  }

Be sure to use data binding using ? string replacements. This will guard against SQL injection attacks.

Notes

You will have to import the DatabaseHelper class and sqflite if you are in another file (like main.dart).
The SQFlite plugin uses a Map<String, dynamic> to map the column names to the data in each row.

Supplemental code

For your copy-and-paste convenience, here is the layout code for main.dart:

import 'package:flutter/material.dart';
// I called my project 'flutter_database_operations'. You can update for yours.
import 'package:flutter_database_operations/database_helper.dart';
import 'package:sqflite/sqflite.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'SQFlite Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(),
    );
  }
}

class MyHomePage extends StatelessWidget {

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('sqflite'),
      ),
      body: RaisedButton(
        child: Text('query', style: TextStyle(fontSize: 20),),
        onPressed: () {_query();},
      ),
    );
  }

  _query() async {

    // get a reference to the database
    Database db = await DatabaseHelper.instance.database;

    // get all rows
    List<Map> result = await db.query(DatabaseHelper.table);

    // get single row
    //List<Map> result = await db.query(DatabaseHelper.table,
    //    columns: [DatabaseHelper.columnId, DatabaseHelper.columnName, DatabaseHelper.columnAge],
    //    where: '${DatabaseHelper.columnId} = ?',
    //    whereArgs: [1]);


    // raw query
    //List<Map> result = await db.rawQuery('SELECT * FROM my_table WHERE name=?', ['Mary']);

    // get each row in the result list and print it
    result.forEach((row) => print(row));
  }
}