阅读 3005

Flutter 数据库sqflite使用知识点

前言

sqflite是Flutter的SQLite插件,在App端能够高效的存储和处理数据库数据,官方地址:pub.flutter-io.cn/packages/sq…

SQLite

关于SQLite的学习,推荐 菜 鸟 教 程 的 SQLite 教 程

SQLite的特点:

  • 不需要单独的服务器或操作系统
  • SQLite不需要配置,即不需要手动安装和管理
  • 存储在一个单一的跨平台的磁盘文件
  • 不需要外部依赖,完全自给自足
  • 轻量级

sqflite

sqflite全称,我的理解是Structured Query Flutter Language Lite,即用于Flutter的轻量级结构化查询语言。如果理解有误,请及时更正,万分谢谢!

一起看一下sqflite官方介绍:

用于Flutter的SQLite插件,支持iOS、Android、MacOS.

  • 支持事务和批量操作
  • 程序打开期间,自动化版本管理
  • 增删改查的帮助程序
  • 在iOS和Android后台线程中执行DB操作

当前sqflite版本:1.2.0

关于持久化存储

关于App端的持久化存储,用的比较多有shared_preferences、数据库存储、文件存储。

  • shared_preferences以key-value的方式存储数据,是一种轻量级的数据持久化存储方案。
  • 数据库存储数据量较大的场合,能够高效的存储、组织和处理数据。
  • 像长篇文章、图片、视频等Size比较大的,利用File存储。

sqflite支持的数据类型

如果你对英文比较自信,可以尝试读这篇文章 Datatypes In SQLite Version 3.

SQLite并没有对值进行类型检查,即存储INTEGER类型的列是可以存储TEXT类型的,但是当我们解析查询的结果进行映射时,会报类型异常的。所以还是要避免存储类型不一致的数据。

sqflite支持5种数据类型:NULL, INTEGER, REAL, TEXT, BLOB.

  • NULL

    某一列不存储数据的时候,默认值是NULL.

  • INTEGER

    dart中的int类型,值的范围是-2^63 到 2^63 - 1

  • REAL

    dart中的num类型,即int和double类型

  • TEXT

    dart中的String类型

  • BLOB

    dart中的Uint8List类型,虽然能够存储List< int >,但官方并不建议,因为转化比较慢。

如果我们需要存储其他类型,比如bool,DateTime,List< String >等数据,需要我们自行处理,每个人或许都有自己独特的方法,希望您能够提出一些建议。我们可以通过封装实体类和解析类,在外部代码看来,就是实现了存储bool,DateTime,List< String >等这些类型。

  • bool

    存储INTEGER类型,0为false,1为true.

  • DateTime

    存储INTEGER类型,一列数据的创建时间和更新时间,一般是比较重要的。当然还有其他的,比如一个订单的付款时间、发货时间、取消时间等很多的时间信息。如果存储TEXT类型,程序如果支持多种语言的话,还是不方便的。

  • List

    存储TEXT类型,我们可以根据特殊的分隔符,把数据组合成String存储到数据库。然后根据String的split解析成List< String >。还是有很多需要注意的,比如List的元素中一定不能包含定义的分隔符。对List的某一个Item修改比较麻烦,只能整体覆盖List。

  • Map、json、实体类

    存储TEXT类型,一般我使用实体类的toMap方法把实体类转换成Map, 通过jsonEncode把实体类转换成String,反过来,利用jsonDecode把String转换成Map,通过实体类的fromMap转换成实体类。

数据库操作

数据库的创建

根据数据库的名称和版本号,Open数据库。

import 'package:sqflite/sqflite.dart';

String databasesPath = await getDatabasesPath();
// Database Path: /data/user/0/com.package.name/databases
String path = join(databasesPath, 'db_name.db');
// Path: /data/user/0/com.package.name/databases/db_name.db
Database database = await openDatabase(
  path,
  version: VERSION,
  onCreate: (Database db, int version) async {
    // 表格创建等初始化操作
  },
  onUpgrade: (Database db, int oldVersion, int newVersion) async {
    // 数据库升级
  },
);
复制代码

数据库删除

await deleteDatabase(path);
复制代码

数据库关闭

await database.close();
复制代码

数据库的升级

官方的使用文档中,表格是按需创建的,但我在使用过程中遇到一个麻烦,关于数据库的升级问题,因为有些Table,用户可能并未触及到,在更新表格结构的时候,需要首先判断Table是否存在等操作。所以,我在创建数据库的时候,选择创建所有好表格(一般情况下,App端并没有太多表格,我写的项目中最多的一个有11个表格。)。

只需要把openDatabase的参数version加1,程序在打开的时候,会自动调用openDatabase的onUpgrade方法。所以我们需要在onUpgrade方法中,执行数据库的升级操作。最霸道的做法是,Drop所有的表格,然后Create最新的表格(会不会被领导打,我就不清楚了)。

使用sqflite,就必须要对SQL语句比较熟悉。对数据库的ORM封装,可以使用插件库 sqfentity

添加表格

database.execute('CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
复制代码

删除表格

database.execute('DROP table test');
复制代码

清空表格

database.execute('DELETE FROM test');
复制代码

重命名表格

database.execute('ALTER TABLE test RENAME TO test_1');
复制代码

添加字段

database.execute('ALTER TABLE test ADD age integer');
复制代码

删除字段

database.execute('ALTER TABLE test DROP COLUMN age');
复制代码

修改字段类型

database.execute('ALTER TABLE test ALTER COLUMN value TEXT');
复制代码

数据的增删改查

Insert

返回值需要注意下:Returns the last inserted record id(返回最后插入的记录ID).

int id = await database.insert(‘test’, {'name': 'Civet', 'value': '18', 'num': '456.7'});
复制代码
int id = await database.rawInsert('INSERT INTO test(name, value, num) VALUES("Civet", 18, 456.7)');
复制代码

Delete

返回值需要注意下:Returns the number of changes made(返回受影响的的数量,即删除的条目数量)。另外,如果没where语句,会清空表格,要特别小心。

int count = await database.delete('test', where: 'name = ?', whereArgs: ['Civet']);
复制代码
int count = await database.rawDelete('DELETE FROM test WHERE name = ?', ['Civet']);
复制代码

Update

返回值需要注意下:Returns the number of changes made(返回受影响的的数量).

int count = await database.update(
  'test', 
  {'name': 'Home', 'value': '20'}
  where: 'name = ?', 
  whereArgs: ['Civet']
);
复制代码
int count = await database.rawUpdate('UPDATE test SET name = ?, value = ? WHERE name = ?', ['Home', 20, 'Civet']);
复制代码

Query

Query的返回值:List<Map<String, dynamic>>,List是行数据,Map是列数据。

Query是SQL语句中最复杂的一个,关键词包含distinct, where, group by, having, count, order by asc/desc, limit, offset, in, join, as, nuion等等。

List<Map<String, dynamic>> result = await database.query(
  ‘test’,
  distinct: true, // 是否是独特的,即是否不让重复
  columns: ['name','value'], // 需要查询的列
  where: 'age > ?', // 查询条件
  whereArgs: [16], // 查询条件参数
  groupBy: 'name', // 按列分组
  having: 'count(name) < 2', // 给分组设置条件
  orderBy: 'name asc', // 按列排序 asc/desc
  limit: 5, // 限制查询结果数量
  offset: 2, // 跳过几条数据
);
复制代码
List<Map<String, dynamic>> result = await database.rawQuery(
  'SELECT distinct name, value FROM test WHERE age > ? group by name having count(name) < 2 order by name asc limit 5 offset 2',
  [16],
);
复制代码

对于 in 语句的组合:

/// SQL query where in
/// If colunm is INTEGER, use this method.
/// Reslut: (2, 3, 4)
static String whereInIntToString(List<int> data) {
  String result;
  for (int sub in data) {
    if (result == null) {
      result = '($sub';
    } else {
      result = '$result, $sub';
    }
  }
  result = '$result)';
  return result;
}

/// SQL query where in
/// If colunm is TEXT, use this method.
/// Reslut: ('2', '3', '4')
static String whereInStringToString(List<String> data) {
  String result;
  for (String sub in data) {
    if (result == null) {
      result = '(\'$sub\'';
    } else {
      result = '$result, \'$sub\'';
    }
  }
  result = '$result)';
  return result;
}
复制代码

关于 join 的使用:

连接两个表格的数据,使用on, using, natural限定连接条件,join分为cross join(x * y的一种实现), inner join(默认join方式), outer join(只支持left outer join)。

用的比较多的是内连接inner join, 它把两个表的数据,以限定条件on Table1.column = Table2.column组合起来。

/// SQL Statement
SELECT user_id, user_name, address, postal_code FROM at_user INNER JOIN at_address ON at_user.id = at_address.user_id;
复制代码

Batch 批量操作

需要注意的是,如果并不关心batch.commit()的返回值,传入noResult为true,这时候返回的List< dynamic >即为null.

Batch batch = database.batch();
batch.insert('test', {'name': 'item'});
batch.update('test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('test', where: 'name = ?', whereArgs: ['item']);
List<dynamic> results = await batch.commit(
    noResult: true, // 是否关心返回值
    continueOnError: true, // 出现错误是否继续
);
复制代码

Transaction 事务的支持

事务就像一个封闭的执行环境,只有当事务提交时,一系列操作对外才是可见的。

需要特别注意的是,事务执行期间,不能使用外部的DataBase对象,避免造成死锁。

你能看出下面操作的返回值吗?

/// 测试事务
Future<void> testTransaction({bool noResult, bool continueOnError}) async {
  print('--------------- Transaction(noResult: $noResult, continueOnError: $continueOnError) --------------- ');
  var db = await getDataBase();
  // 清空表
  await db.delete('test');
  // 启动事务
  await db.transaction((Transaction txn) async {
    // Ok
    var batch = txn.batch();
    // 操作(1-3):插入数据
    batch.insert('test', {'name': 'item-0'});
    batch.insert('test', {'name': 'item-1'});
    batch.insert('test', {'name': 'item-2'});
    // 操作4:更新一条数据
    batch.update(
      'test',
      {'name': 'item-3'},
      where: 'name = ?',
      whereArgs: ['item-0'],
    );
    // 操作5:模拟异常: no such column no_column
    batch.insert(
      'test',
      {'no_column': 'item-0'},
    );
    // 操作6:删除一条数据
    batch.delete(
      'test',
      where: 'name = ?',
      whereArgs: ['item-3'],
    );
    // 执行上述一系列操作
    List<dynamic> results = await batch.commit(
      noResult: noResult,
      continueOnError: continueOnError,
    );
    // 操作返回值
    print('Reslut<dynamic>: $results');
  });
  // 事务执行后:查询表中多少数据
  List<Map<String, dynamic>> result = await db.rawQuery(
    'SELECT * FROM Test',
  );
  print('List<Map> Reslut: $result');
  print('------------------------------ ');
  print('');
}
复制代码
  1. 需要返回值,错误不继续执行
await testTransaction(noResult: false, continueOnError: false);
复制代码

结果:

  • 发送异常 table Test has no column named no_column
  • 分析:操作5发生错误,不继续执行,直接抛出异常。
  • 表中并未插入任何数据,操作全部Rollback
  1. 需要返回值,错误继续执行
await testTransaction(noResult: false, continueOnError: true);
复制代码

结果:

  • Reslut: [1, 2, 3, 1, DatabaseException(table Test has no column named no_column (Sqlite code 1): , while compiling: INSERT INTO Test (no_column) VALUES (?), (OS error - 2:No such file or directory)) sql 'INSERT INTO Test (no_column) VALUES (?)' args [item-0]}, 1]
  • List Reslut: [{id: 2, name: item-1, value: null, num: null}, {id: 3, name: item-2, value: null, num: null}]
  • 分析:操作5发生错误,继续执行,操作5返回值是一个异常,其他操作正常
  1. 不需要返回值,错误不继续执行
  2. 不需要返回值,错误继续执行

既然你已经读到这里了,返回值已经在你脑海中了。

可视化界面

有时候为了调试,或许为了分析数据,我们需要查看数据库中具体有哪些数据,通过可视化界面展示数据就比较方便了。

1. Database Navigator

Android Studio的Plugin,直接在线(Marketplace)安装即可。最上边的状态栏有个DB Navigator,然后点击Database browser, 设置数据库文件路径。

2. SQLiteStudio