Thanks sqflite plugin
using sqflite plugin
Use this package as a library
$ flutter pub add crazy_db
This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):
dependencies:
crazy_db: ^1.0.4
Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.
Import it
Now in your Dart code, you can use:
dependencies:
import 'package:crazy_db/crazy_db.dart';
Introduction
What is CrazyDB?
Secondary encapsulation with sqflite, using point syntax more convenient operation database
Getting started
1.Create the assets folder in the root directory
2.Create the .sqlite file in the assets directory. The default name crazyDB.sqlite
3.Call once before using
import 'package:crazy_db/crazy_db.dart';
await CrazyDB.connection("crazyDB.sqlite");
It copies the file to the sandbox file and if debug mode print .sqlite location
Open database by default
Usage
insert data
int ret = await CrazyDB.table("request_cache")
.create({"url":"https://baidu.com"});
delete data
int ret = await CrazyDB.table("request_cache")
.where(column: "url", value: "https://baidu.com")
.delete();
update data
int ret = await CrazyDB.table("request_cache")
.where(column: "url", value: "https://baidu.com")
.update({"json":"{}"});
select data
List list = await CrazyDB.table("request_cache")
.where(column: "id", value: 1)
.where(column: "url", value: "https://baidu.com")
.whereNotNull(column: "url")
.orWhere(column: "id", value: 2)
.whereNotBetween("id",[500,600])
.orderByDesc("id")
.whereNotIn("id", [1,3])
.get();
Using native SQL processing
await CrazyDB.selectRaw("select * from request_cache");
await CrazyDB.insertRaw("insert into request_cache(url) values('http://')");
await CrazyDB.updateRaw("update request_cache set url='http://' where id = 1 ");
await CrazyDB.deleteRaw("delete from request_cache where id =1");
Using transaction
//waring: You must use CrazyDB.commit() or CrazyDB.rollback() At least once
try {
await CrazyDB.transaction();
//Your SQL Please Write here
//example:
int id = await CrazyDB.table("request_cache").create({"url":"https://sina.cn"});
CrazyDB.table("request_cache").where(column: "id", value: id).delete();
//you must use the function
await CrazyDB.commit();
} catch (e) {
//you must use the function
CrazyDB.rollback();
debugPrint("transaction=>$e");
}
api
method | return | description |
---|---|---|
CrazyDB.openDB() | bool | open database |
CrazyDB.closeDB() | void | close database |
CrazyDB.connection(String db) | CrazyDB | connection db |
CrazyDB.transaction() | void | transaction closure use try catch |
CrazyDB.commit() | void | Execute SQL and The last line You must use the function |
CrazyDB.rollback() | void | if throw Exception() You must use the function |
CrazyDB.table(String db) | CrazyDB | use table name return instance |
CrazyDB.select({List? list}) | CrazyDB | Filter query field |
CrazyDB.create(Map<String, dynamic> map) | int (id) | insert data return id |
CrazyDB.db.join(String tab2, String tab1Column, String symbol, String tab2Column) | CrazyDB | inner join |
CrazyDB.db.leftJoin(String tab2, String tab1Column, String symbol, String tab2Column) | CrazyDB | left join |
CrazyDB.db.rightJoin(String tab2, String tab1Column, String symbol, String tab2Column) | CrazyDB | right join |
CrazyDB.db.where({required String? column, String? symbol, required value}) | CrazyDB | where |
CrazyDB.db.whereNotNull({required String column}) | CrazyDB | $1000 |
CrazyDB.db.whereNull({required String column}) | CrazyDB | $1000 |
CrazyDB.db.orWhere({required String column, String? symbol, required value}) | CrazyDB | $1000 |
CrazyDB.db.whereBetween(String column, List list) | CrazyDB | $1000 |
CrazyDB.db.whereNotBetween(String column, List list) | CrazyDB | $1000 |
CrazyDB.db.whereIn(String column, List list) | CrazyDB | $1000 |
CrazyDB.db.whereNotIn(String column, List list) | CrazyDB | $1000 |
CrazyDB.db.update(Map<String, dynamic> map) | int (count) | update data return count |
CrazyDB.db.delete() | int (count) | delete data return count |
CrazyDB.db.get() | List<Map<String, Object?>> | select data |
CrazyDB.db.first() | Map<String, Object?> | select first data |
CrazyDB.db.find(int id) | Map<String, Object?> | select from table where id = {id} |
CrazyDB.db.paginate(int page, {int? perPage}) | List<Map<String, Object?>> | limit 1,1 |
CrazyDB.db.count({String? column}) | int | count({column}) |
CrazyDB.db.sum({String? column, String? alias}) | List<Map<String, Object?>> | sum() |
CrazyDB.db.avg({String? column}) | int | avg() |
CrazyDB.db.pluck(String column, {String? key}) | dynamic | data to heavy. If the key is null, return list |
CrazyDB.db.value(String column) | dynamic | first data and get column |
CrazyDB.db.orderBy(String column, {String? soft}) | CrazyDB | order by asc |
CrazyDB.db.orderByDesc(String column) | CrazyDB | order by desc |
CrazyDB.db.groupBy(String column) | CrazyDB | group by |
CrazyDB.db.having(String sql) | CrazyDB | having |
CrazyDB.getSql() | String | print native sql |