在pubspec.yaml引入sqlite插件，记得执行flutter packages get
flutter:
sdk: flutter
sqflite: ^1.2.0
最新版本到这里搜索
接下来，以我的项目为例（小说阅读器）
项目中需要保存小说的数据、书的目录，阅读记录等
1.首先创建一个类，用来管理数据库和表
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class ReadSQL{
static const String tableShelf = "bookShelf";
static const String tableCatalog = "bookCatalog";
final String dropTableShelf = "DROP TABLE IF EXISTS $tableShelf";
final String createTableShelf = "CREATE TABLE $tableShelf (id INTEGER PRIMARY KEY AUTOINCREMENT, bookId TEXT"
",title TEXT,readers REAL,briefIntro TEXT,charactersCount INTEGER"
",chaptersCount INTEGER,cover TEXT,serialState INTEGER"
",majorCateId TEXT,majorCateName TEXT"
",minorCateId TEXT,minorCateName TEXT"
",ratio REAL,author TEXT,lastReadTime INTEGER)";
final String dropTableCatalog = "DROP TABLE IF EXISTS $tableCatalog";
final String createTableCatalog = "CREATE TABLE $tableCatalog (id INTEGER PRIMARY KEY AUTOINCREMENT, mixCode TEXT"
",bookId TEXT,chapterTitle TEXT,chapterId INTEGER,status INTEGER)";
Database _db;
Future<Database> get db async{
if(_db == null){
_db = await _initDb();
}
return _db;
}
_initDb() async{
String basePath = await getDatabasesPath();
String path = join(basePath,"read.db");
Database db = await openDatabase(path,version: 2,onCreate: _onCreate,onUpgrade: _onUpgrade);
return db;
}
Future close() async {
var result = _db.close();
_db = null;
return result;
}
void _onCreate(Database db, int newVersion) async{
print("_onCreate newVersion:$newVersion");
var batch = db.batch();
batch.execute(dropTableShelf);
batch.execute(createTableShelf);
batch.execute(dropTableCatalog);
batch.execute(createTableCatalog);
await batch.commit();
}
void _onUpgrade(Database db, int oldVersion,int newVersion)async{
print("_onUpgrade oldVersion:$oldVersion");
print("_onUpgrade newVersion:$newVersion");
var batch = db.batch();
if(oldVersion == 1){
batch.execute(dropTableCatalog);
batch.execute(createTableCatalog);
}
await batch.commit();
}
}
该类主要用来创建表和升级表。
说下升级的问题：
1.第一个版本version=1,创建了表bookShelf，用来保存书籍到书架。
2.接着又要添加一个目录表bookCatalog(记得version改为2)，所以才有上面的代码
如果是新用户，直接执行是_onCreate方法，日志打印newVersion=2;
升级上来的用户执行_onUpgrade方法，然后做个判断，创建表bookCatalog。
如果要升级修改某个表的字段看这个链接
2.创建具体对象
import 'package:json_annotation/json_annotation.dart';
part 'bookChapter.g.dart';
@JsonSerializable()
class BookChapter{
int chapterId;
String chapterTitle;
int status;
@JsonKey(ignore: true)
String mixCode;
@JsonKey(ignore: true)
String bookId;
BookChapter(this.chapterId, this.chapterTitle, this.status,{this.mixCode,this.bookId});
factory BookChapter.fromJson(Map<String,dynamic> json) => _$BookChapterFromJson(json);
Map<String, dynamic> toJson() => _$BookChapterToJson(this);
Map<String, dynamic> toMap() {
final Map<String, dynamic> data = new Map<String, dynamic>();
data['mixCode'] = this.mixCode;
data['bookId'] = this.bookId;
data['chapterTitle'] = this.chapterTitle;
data['chapterId'] = this.chapterId;
data['status'] = this.status;
return data;
}
BookChapter.fromMap(Map<String, dynamic> data) {
this.mixCode = data['mixCode'];
this.bookId = data['bookId'];
this.chapterTitle = data['chapterTitle'];
this.chapterId = data['chapterId'];
this.status = data['status'];
}
@override
String toString() {
return 'BookChapter{chapterId: $chapterId, chapterTitle: $chapterTitle, status: $status}';
}
}
说明：
类中有些字段使用了@JsonKey关键字，代表这个字段不用使用json自动化写到BookChapterFromJson类中，和表没有关系,关于json相关，看flutter中文网相关介绍
3.写个帮助类，具体对某个表的操作（例如bookCatalog表）
import 'package:sqflite/sqflite.dart';
import 'package:tangerine_read/common/app_constant.dart';
import 'package:tangerine_read/local/database/read_sql.dart';
import 'package:tangerine_read/model/bookChapter.dart';
class BookCatalogHelper{
ReadSQL _sql;
static final BookCatalogHelper _instance = new BookCatalogHelper.internal();
static BookCatalogHelper get instance => _instance;
BookCatalogHelper.internal(){
_sql = ReadSQL();
}
Future close() async {
var result = _sql.close();
return result;
}
Future<bool> isBookHasCatalog(String bookId)async{
var dbClient = await _sql.db;
int count = Sqflite.firstIntValue(await dbClient.rawQuery("SELECT COUNT(*) FROM ${ReadSQL.tableCatalog} WHERE bookId=$bookId"));
return count > 0;
}
Future<int> getBookCatalogCount(String bookId)async{
var dbClient = await _sql.db;
int count = Sqflite.firstIntValue(await dbClient.rawQuery("SELECT COUNT(*) FROM ${ReadSQL.tableCatalog} WHERE bookId=$bookId"));
return count;
}
///目录保存sql比较复杂
///需要考虑该是否 连载或者完结
///连载的书每次都要更新目录
Future<int> saveCatalogs(String bookId,List<BookChapter> catalog)async{
if(catalog == null || catalog.isEmpty){
return 0;
}
///因为考虑每一章的信息唯一，所以要给目录加上一个mixCode
catalog.forEach((bookChapter){
bookChapter.mixCode = bookId + bookChapter.chapterId.toString();
bookChapter.bookId = bookId;
});
var dbClient = await _sql.db;
bool has = await isBookHasCatalog(bookId);
if(has){
if(AppConstant.DEBUG){
print("saveCatalogs delete");
}
///delete
int deleteCount = await dbClient.rawDelete("DELETE FROM ${ReadSQL.tableCatalog} WHERE bookId=$bookId");
if(AppConstant.DEBUG){
print("saveCatalogs deleteCount:$deleteCount");
}
}
///批量往表中加数据
int startTime = DateTime.now().millisecondsSinceEpoch;
var batch = dbClient.batch();
catalog.forEach((log){
batch.insert(ReadSQL.tableCatalog, log.toMap());
});
batch.commit();
if(AppConstant.DEBUG){
print("saveCatalogs insertCount:${catalog.length}");
print("saveCatalogstime：${DateTime.now().millisecondsSinceEpoch - startTime}");
}
return catalog.length;
}
Future<List<BookChapter>> getCatalogs(String bookId)async{
var dbClient = await _sql.db;
List<Map> maps = await dbClient.rawQuery("SELECT * FROM ${ReadSQL.tableCatalog} WHERE bookId=$bookId");
if (maps == null || maps.length == 0) {
return null;
}
List<BookChapter> list = [];
maps.forEach((it){
list.add(BookChapter.fromMap(it));
});
return list;
}
void deleteCatalog(String bookId)async{
var dbClient = await _sql.db;
var deleteCount = await dbClient.rawDelete("DELETE FROM ${ReadSQL.tableCatalog} WHERE bookId IN ($bookId)");
if(AppConstant.DEBUG){
print("deleteCatalog deleteCount:$deleteCount");
}
}
}
目前为止已经对一个表的具体操作已经写完了。
可是并没有完全满足增删改查的需要，因为确实我这个表用不到更新
下面的例子是补充对数据表增删改查的操作，不是对这个bookCatalog表了，但不影响功能演示
1.查询
///
// var dbClient = await _sql.db;
// List<Map> maps = await dbClient.query(ReadSQL.tableShelf,
// columns: ["bookId","title","readers","briefIntro","charactersCount",
// "chaptersCount","cover","serialState","majorCateId",
// "majorCateName","minorCateId","minorCateName","ratio","author","lastReadTime"],
// where: "bookId = ?",
// whereArgs: [bookId]
// );
///
Future<BookShelf> getBook(String bookId)async{
var dbClient = await _sql.db;
List<Map> maps = await dbClient.rawQuery("SELECT * FROM ${ReadSQL.tableShelf} WHERE bookId=$bookId");
if(maps.length > 0){
return BookShelf.fromMap(maps.first);
}
return null;
}
查询没有使用dbClient.query方法，我觉得没有使用dbClient.rawQuery自己写查询语句来的方便，这个看具体开发人员的习惯
2.更新和插入
Future<int> saveBook(BookShelf bookShelf)async{
bookShelf.lastReadTime = DateTime.now().millisecondsSinceEpoch;
var dbClient = await _sql.db;
List<Map> hasList = await dbClient.rawQuery("SELECT * FROM ${ReadSQL.tableShelf} WHERE bookId=${bookShelf.bookId}");
var result = 0;
if(hasList.length > 0){
if(AppConstant.DEBUG){
print("hasList.length > 0");
}
result = await dbClient.update(ReadSQL.tableShelf, bookShelf.toMap(),where: "bookId = ?",whereArgs: [bookShelf.bookId]);
}else{
if(AppConstant.DEBUG){
print("hasList.length else");
}
result = await dbClient.insert(ReadSQL.tableShelf, bookShelf.toMap());
}
return result;
}
这里我的需求就是保存书籍到书架，如果没有保存过，就直接插入数据，
如果已经在书架，每次需要更新时间，以便书架依据时间排序
3.删除（批量删除，2种方式）
void deleteBooks(List<BookShelf> bookList)async{
var dbClient = await _sql.db;
String in_book = "";
for(int i=0;i<bookList.length;i++){
in_book += "${bookList[i].bookId}";
if(i != bookList.length-1){
in_book+=",";
}
}
dbClient.rawDelete("DELETE FROM ${ReadSQL.tableShelf} WHERE bookId IN ($in_book)");
}
///该方法证明效率高
void deleteBooks(List<BookShelf> bookList)async{
var dbClient = await _sql.db;
var batch = dbClient.batch();
bookList.forEach((book){
batch.rawDelete("DELETE FROM ${ReadSQL.tableShelf} WHERE bookId = ${book.bookId}");
});
batch.commit();
}
这里的删除，删除的可能是一本或者多本书，用到了sql的删除语句的IN操作符, 当然可以使用batch这种批量删，后面我会接着说下，批量操作的效率问题
IN 操作符允许您在 WHERE 子句中规定多个值。
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...);
删除的效率问题
先说结果，使用batch方式最好！！
先说下我的需求，小说要加入本地搜索，解决方法就是第一次运行时，把定义好的书的json数据，批量插入一个表中，用户在搜索界面就可以搜书了（我们的书毕竟不多，不到1200本，没有必要走网络搜索）
最开始我是使用这种方式：
initLocalBooks(List<BookLocalData> books)async{
var dbClient = await _sql.db;
int count = Sqflite.firstIntValue(await dbClient.rawQuery("SELECT COUNT(*) FROM ${ReadSQL.tableSearch}"));
if(books.length == count){
///说明数据库和要加入的书的数量相等，不插入数据库
if(AppConstant.DEBUG){
print("not need insert");
}
return;
}
///先删除
int deleteCount = await dbClient.rawDelete("DELETE FROM ${ReadSQL.tableSearch}");
if(AppConstant.DEBUG){
print("initLocalBooks deleteCount:$deleteCount");
}
String values = "";
int startTime = DateTime.now().millisecondsSinceEpoch;
for(int i=0;i<books.length;i++){
var localBook = books[i];
if(i == books.length-1){
values += "('${localBook.book_id}','${localBook.author_name}','${localBook.brief_intro}'"
",'${localBook.cover}','${localBook.title}','${localBook.book_alias}')";
}else{
values += "('${localBook.book_id}','${localBook.author_name}','${localBook.brief_intro}'"
",'${localBook.cover}','${localBook.title}','${localBook.book_alias}'),";
}
}
int insertCount = await dbClient.rawInsert("INSERT INTO ${ReadSQL.tableSearch} (book_id,author_name,brief_intro,cover,title,book_alias) "
"VALUES $values");
if(AppConstant.DEBUG){
print("initLocalBooks insertCount:$insertCount");
print("initLocalBooks time：${DateTime.now().millisecondsSinceEpoch - startTime}");
}
}
使用的是sql语句中的INSERT INTO
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
打印的日志是：
I/flutter (20456): initLocalBooks deleteCount:0
I/flutter (20456): initLocalBooks insertCount:1568
I/flutter (20456): initLocalBooks time：824
后来研究了批量batch操作，代码如下：
initLocalBooks2(List<BookLocalData> books)async{
var dbClient = await _sql.db;
int count = Sqflite.firstIntValue(await dbClient.rawQuery("SELECT COUNT(*) FROM ${ReadSQL.tableSearch}"));
if(books.length == count){
///说明数据库和要加入的书的数量相等，不插入数据库
if(AppConstant.DEBUG){
print("not need insert");
}
return;
}
///先删除
int deleteCount = await dbClient.rawDelete("DELETE FROM ${ReadSQL.tableSearch}");
if(AppConstant.DEBUG){
print("initLocalBooks2 deleteCount:$deleteCount");
}
var batch = dbClient.batch();
int startTime = DateTime.now().millisecondsSinceEpoch;
books.forEach((book){
batch.insert(ReadSQL.tableSearch, book.toMap());
});
batch.commit();
if(AppConstant.DEBUG){
print("initLocalBooks2 insertCount:${books.length}");
print("initLocalBooks2 time：${DateTime.now().millisecondsSinceEpoch - startTime}");
}
}
日志如下：
I/flutter (28596): initLocalBooks2 deleteCount:0
I/flutter (28596): initLocalBooks2 insertCount:1568
I/flutter (28596): initLocalBooks2 time：28
效率一目了然。
可能还有使用异步操作，其他代码都是一样，最主要都是下面插入的逻辑：
dbClient.transaction((txn)async{
int startTime = DateTime.now().millisecondsSinceEpoch;
books.forEach((book)async{
await txn.insert(ReadSQL.tableSearch, book.toMap());
});
if(AppConstant.DEBUG){
print("initLocalBooks2 insertCount:${books.length}");
print("initLocalBooks2 time：${DateTime.now().millisecondsSinceEpoch - startTime}");
}
});
日志：
I/flutter (25929): initLocalBooks2 deleteCount:0
I/flutter (25929): initLocalBooks2 insertCount:1568
I/flutter (25929): initLocalBooks2 time：291
但是这个是异步的，不影响接下来的业务逻辑，看每个项目的具体需求。
