Flutter开发笔记 —— sqflite插件数据库应用

前言

今天在观阅掘金大佬文章的时候，了解到了该 sqflite 插件，结合官网教程和自己实践，由此总结出该文，希望对大家的学习有帮助！

插件详情

Flutter的 SQLite 插件。支持 iOS、Android 和 MacOS。

• 支持事务和batch模式
• 打开时自动进行版本管理
• 插入/查询/更新/删除查询的助手
• iOS 和 Android 上的数据库操作在后台线程中执行

插件地址: https://pub.dev/packages/sqflite

本文使用插件版本为最新版本：2.3.0

基础介绍

数据表创建

• openDatabase() 连接数据库
• deleteDatabase() 删除数据库

openDataBase方法 可选参为db文件地址、版本号、相关回调

案例速览

late Database db;
final database = "book.db";
final table = "book";/** @author Marinda* @date 2023/12/13 15:53* @description 连接数据库*/
connection() async{//获取var databasePath = await getDatabasesPath();String path = p.join(databasePath,database);//删除数据库await deleteDatabase(path);print('当前地址：${path}');if(File(path).existsSync()){db = await openDatabase(path);}else{db = await openDatabase(path,version: 1,onCreate: (database,version) async{database.execute("CREATE TABLE `${table}` (id integer primary key autoincrement,name text not null,price REAL ,author TEXT NOT NULL,description TEXT)");});}
}

语句介绍

CURD方法介绍

• 使用SQL语句的CURD前缀带raw (例如rawInsert)
• 官方封装好的CURD语句不带raw(例如insert) ->全文使用该方法

语法速览

//前者
Future<int> rawInsert(String sql, [List<Object?>? arguments]);
//后者
Future<int> insert(String table, Map<String, Object?> values,{String? nullColumnHack, ConflictAlgorithm? conflictAlgorithm});

接下来看几个简单的CURD快速了解写法

insert(插入)

Future<int> insert() async{Map<String,dynamic> args = {"id": 1,"name": "张飞","description": "猛将"};//返回id table为表名return await db.insert(table,args);
}

delete(删除)

Future<int> delete(int id) async{return await db.delete(table,where: "id = ?",whereArgs: [id]);
}

update(修改)

Future<int> update() async{Map<String,dynamic> args = {"id": 1,"name": "吕布","description": "天下无双"};return await db.update(table, args,where: "id = ?",whereArgs: [args["id"]]);
}

select(查询)

/** @author Marinda* @date 2023/12/13 16:11* @description 通过id查询*/
Future<Map<String,dynamic>?> selectById(int id) async{var list = await db.query(table,where: "id = ?",whereArgs: [id]);if(list.isNotEmpty){return list.first;}return null;
}

现在我们对sqflite插件的基础使用有一个初步的了解了，接下来围绕一个书本表案例做实战

实战应用

• 定义Book书表作为实体类
• 定义DBBookProvider类处理数据库表

book.dart

/*** @author Marinda* @date 2023/12/13 15:42* @description 书籍信息*/
class Book {int? _id;String? _name;double? _price;String? _author;String? _description;Book({int? id,String? name,double? price,String? author,String? description}) {if (id != null) {this._id = id;}if (name != null) {this._name = name;}if (price != null) {this._price = price;}if (author != null) {this._author = author;}if (description != null) {this._description = description;}}int? get id => _id;set id(int? id) => _id = id;String? get name => _name;set name(String? name) => _name = name;double? get price => _price;set price(double? price) => _price = price;String? get author => _author;set author(String? author) => _author = author;String? get description => _description;set description(String? description) => _description = description;Book.fromJson(Map<String, dynamic> json) {_id = json['id'];_name = json['name'];_price = json['price'];_author = json['author'];_description = json['description'];}Map<String, dynamic> toJson() {final Map<String, dynamic> data = new Map<String, dynamic>();data['id'] = this._id;data['name'] = this._name;data['price'] = this._price;data['author'] = this._author;data['description'] = this._description;return data;}
}

db_book_provider.dart

/*** @author Marinda* @date 2023/12/13 15:43* @description 书籍数据库适配器*/class DBBookProvider{late Database db;final database = "book.db";final table = "book";static DBBookProvider? _instance;static DBBookProvider instance = getInstance();DBBookProvider._();factory DBBookProvider(){return instance;}static getInstance(){if(_instance == null){_instance = DBBookProvider._();}return _instance ?? DBBookProvider._();}/** @author Marinda* @date 2023/12/13 15:53* @description 连接数据库*/connection() async{var databasePath = await getDatabasesPath();String path = p.join(databasePath,database);// await deleteDatabase(path);print('当前地址：${path}');if(File(path).existsSync()){db = await openDatabase(path);}else{db = await openDatabase(path,version: 1,onCreate: (database,version) async{database.execute("CREATE TABLE `${table}` (id integer primary key autoincrement,name text not null,price REAL ,author TEXT NOT NULL,description TEXT)");});}}/** @author Marinda* @date 2023/12/13 16:01* @description 关闭数据库*/close() async{await db.close();}/** @author Marinda* @date 2023/12/13 16:02* @description 插入*/Future<Book> insert(Book book) async{int id = await db.insert(table,book.toJson());book.id = id;return book;}/** @author Marinda* @date 2023/12/13 16:08* @description 删除id*/Future<int> delete(int id) async{return await db.delete(table,where: "id = ?",whereArgs: [id]);}/** @author Marinda* @date 2023/12/13 16:11* @description 通过id查询*/Future<Book?> selectById(int id) async{var list = await db.query(table,where: "id = ?",whereArgs: [id]);if(list.isNotEmpty){return Book.fromJson(list.first);}return null;}/** @author Marinda* @date 2023/12/13 16:13* @description 获取所有书籍列表*/Future<List<Book>> queryList() async{svar result = await db.query(table);return result.map((e) => Book.fromJson(e)).toList();}/** @author Marinda* @date 2023/12/13 16:15* @description 修改书籍信息*/Future<int> update(Book book) async{return await db.update(table, book.toJson(),where: "id = ?",whereArgs: [book.id]);}}

实例化调用

initDatabase() async{DBBookProvider dbBookProvider = DBBookProvider.instance;//连接await dbBookProvider.connection();Book book = Book(name: "斗破苍穹",author: "天蚕土豆",price: 88.00,description: "一本不错的小说");//插入Book element = await dbBookProvider.insert(book);print('element : ${element.toJson()}');//删除for(var id in result){await dbBookProvider.delete(id);}Book newBook = Book.fromJson(book.toJson());newBook.id = 1;newBook.author = "天蚕土豆";//修改await dbBookProvider.update(newBook);//查询全部var list = await dbBookProvider.queryList();print("当前列表: ${list.map((e) => e.toJson()).toList()}");
}

事务&batch

单独拉出来讲讲的原因是我在应用中踩了个坑

事务和batch是什么这个大家如果不了解的话自行百度一下，下文带大家简单看看案例

/** @author Marinda* @date 2023/12/13 16:17* @description 批量插入全部书籍列表*/
insertAll(List<Book> bookList) async{List resultList = [];await db.transaction((txn) async{Batch batch = txn.batch();for(var book in bookList){batch.insert(table,book.toJson());}//返回全部结果并且不跳过错误！resultList = await batch.commit(noResult: false,continueOnError: false);print('resultList: ${resultList}');});await close();return resultList;
}

注：在该案例中配合事务 batch.commit方法中如果continueOnError为false时 出现异常则会中止，导致前面插入回推。

注：在该insertAll()方法 或其他涉及事务的方法，都要执行完毕后主动调用close() 断开本次连接。否则会导致 Error Domain=FMDatabase Code=5 “database is locked”

如果已经出现 Error Domain=FMDatabase Code=5 “database is locked” 错误，请删除该数据库文件，重新生成。

结束语

感谢你的观看，希望对大家学习有帮助，有不对的地方欢迎指正！