Flutter SQLite数据库管理插件quantum_sqlite的使用

quantum

对SQLite进行包装，以支持中文全文搜索。

以下是一个完整的示例代码，展示了如何在Flutter应用中使用quantum_sqlite插件来管理SQLite数据库。

示例代码

import 'package:flutter/material.dart';
import 'dart:async';

import 'package:flutter/services.dart';
import 'package:quantum_sqlite/quantum_sqlite.dart';
import 'package:sqlite3/sqlite3.dart' hide Row;

void main() {
  runApp(const MyApp());
}

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

  [@override](/user/override)
  State<MyApp> createState() => _MyAppState();
}

class _MyAppState extends State<MyApp> {
  final db = sqlite3.openInMemory(); // 打开内存中的SQLite数据库
  final TextEditingController _controller = TextEditingController();
  String _platformVersion = 'Unknown'; // 平台版本信息
  bool _enableJieBa = false; // 是否启用结巴分词
  String? _dashboard; // 搜索结果展示

  [@override](/user/override)
  void initState() {
    super.initState();
    initPlatformState();

    db.loadSimpleExtension(); // 加载简单扩展
    // 创建虚拟表t1，并指定使用fts5进行全文搜索
    db.execute("CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'simple')");
    // 插入一些数据
    db.execute(
        """
insert into t1(x) values
 ('周杰伦 Jay Chou:我已分不清，你是友情还是错过的爱情'), 
('周杰伦 Jay Chou:最美的不是下雨天，是曾与你躲过雨的屋檐'),
 ('I love China! 我爱中国！我是中华人民共和国公民！'),
  ('@English &amp;special _characters."''bacon-&amp;and''-eggs%'),
  ('政府は30日、世界文化遺産への登録を目指す「富士山」（山梨県、静岡県）について、国連教育科学文化機関（ユネスコ）の諮問機関から登録を求める勧告が出たと発表した。構成資産の一つ、三保松原（静岡市）の除外が条件。ユネスコが６月にカンボジアで開く世界遺産委員会が最終決定する。勧告が覆った例は少なく、登録されれば国内で17件目の世界遺産になる。'),
  ('서는 안될 정도로 꼭 요긴한 것들 만일까? 살펴볼수록 없어도 좋을 만한 것들이 적지 않다.')
  """);
  }

  // 初始化平台状态
  Future<void> initPlatformState() async {
    String platformVersion;
    // 获取平台版本信息
    try {
      platformVersion =
          await Quantum.platformVersion ?? 'Unknown platform version';
    } on PlatformException {
      platformVersion = 'Failed to get platform version.';
    }

    // 如果组件被移除，则不更新UI
    if (!mounted) return;

    setState(() {
      _platformVersion = platformVersion;
    });
  }

  [@override](/user/override)
  Widget build(BuildContext context) {
    return MaterialApp(
      home: Scaffold(
          appBar: AppBar(
            title: const Text('Plugin example app'),
          ),
          body: Column(children: [
            SizedBox(
              child: Text('Running on: $_platformVersion\n'),
            ),
            Padding(
              padding: const EdgeInsets.symmetric(vertical: 16),
              child: TextField(controller: _controller),
            ),
            SwitchListTile(
              value: _enableJieBa,
              onChanged: (value) {
                setState(() {
                  _enableJieBa = value;
                });
              },
              title: const Text('启用结巴分词'),
            ),
            SizedBox(
                child: TextButton(
                    child: const Text('搜索'),
                    onPressed: () async {
                      final queryType =
                      _enableJieBa ? 'jieba_query' : 'simple_query';
                      // 执行查询
                      final ret = db.select(
                          "select rowid as id, simple_highlight(t1, 0, '[', ']')"
                              " as info from t1 where x match $queryType(?)",
                          [_controller.text]);
                      setState(() {
                        _dashboard = ret.toString();
                      });
                    })),
            if (_dashboard != null)
              Padding(
                padding: const EdgeInsets.symmetric(vertical: 16),
                child: Text(
                  _dashboard!,
                  style: const TextStyle(fontSize: 20),
                ),
              ),
          ])),
    );
  }
}

代码说明

1. 导入必要的库：

   import 'package:flutter/material.dart';
   import 'dart:async';
   import 'package:flutter/services.dart';
   import 'package:quantum_sqlite/quantum_sqlite.dart';
   import 'package:sqlite3/sqlite3.dart' hide Row;
   

2. 创建主应用类MyApp：

   class MyApp extends StatefulWidget {
     const MyApp({Key? key}) : super(key: key);
   
     [@override](/user/override)
     State<MyApp> createState() => _MyAppState();
   }
   

3. 初始化数据库和文本控制器：

   class _MyAppState extends State<MyApp> {
     final db = sqlite3.openInMemory();
     final TextEditingController _controller = TextEditingController();
     String _platformVersion = 'Unknown';
     bool _enableJieBa = false;
     String? _dashboard;
   

4. 初始化平台状态并创建虚拟表：

   [@override](/user/override)
   void initState() {
     super.initState();
     initPlatformState();
   
     db.loadSimpleExtension();
     db.execute("CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'simple')");
     db.execute(...);
   }
   

5. 异步初始化平台状态：

   Future<void> initPlatformState() async {
     String platformVersion;
     try {
       platformVersion = await Quantum.platformVersion ?? 'Unknown platform version';
     } on PlatformException {
       platformVersion = 'Failed to get platform version.';
     }
     if (!mounted) return;
     setState(() {
       _platformVersion = platformVersion;
     });
   }
   

6. 构建UI：

   [@override](/user/override)
   Widget build(BuildContext context) {
     return MaterialApp(
       home: Scaffold(
           appBar: AppBar(
             title: const Text('Plugin example app'),
           ),
           body: Column(children: [
             SizedBox(
               child: Text('Running on: $_platformVersion\n'),
             ),
             Padding(
               padding: const EdgeInsets.symmetric(vertical: 16),
               child: TextField(controller: _controller),
             ),
             SwitchListTile(
               value: _enableJieBa,
               onChanged: (value) {
                 setState(() {
                   _enableJieBa = value;
                 });
               },
               title: const Text('启用结巴分词'),
             ),
             SizedBox(
               child: TextButton(
                 child: const Text('搜索'),
                 onPressed: () async {
                   final queryType = _enableJieBa ? 'jieba_query' : 'simple_query';
                   final ret = db.select(
                       "select rowid as id, simple_highlight(t1, 0, '[', ']') as info from t1 where x match $queryType(?)",
                       [_controller.text]);
                   setState(() {
                     _dashboard = ret.toString();
                   });
                 }),
             ),
             if (_dashboard != null)
               Padding(
                 padding: const EdgeInsets.symmetric(vertical: 16),
                 child: Text(
                   _dashboard!,
                   style: const TextStyle(fontSize: 20),
                 ),
               ),
           ])),
     );
   }