Hand-in-hand teaching you how to use ORM database gracefully in Flutter project--Part II

Posted by hijack on Tue, 07 May 2019 05:00:05 +0200

A orm database Flutter plugin.

I posted an article earlier. Hand-in-Hand Teaching You Elegant Use of ORM Database in Flutter Project Many people also put forward some valuable suggestions on consulting and using, saying that they do not want to write lua, which is not elegant enough, but also increases the cost of learning. On reflection, it is true that for flutter project development, the best is the pure flutter version of the orm framework, so I wrote a flutter version of the ORM plug-in flutter_orm_plugin , used demo I put it on github and you can download it for fun. Let me introduce all the APIs provided by flutter_orm_plugin.

Add orm table

An orm in flutter_orm_plugin corresponds to a table, such as the Student table in demo. Its db name is School and the table name is Student. It contains the following four fields:

StuddentId is Integer type, primary key, self-increasing in database.

name is a Text type in the database

Class is a Text type, a foreign key, and the associated table is another table, Class table.

score is a Real type in the database

The code to create such a table is in demo's main.dart in

    Map<String , Field> fields = new Map<String , Field>();
    fields["studentId"] = Field(FieldType.Integer, primaryKey: true , autoIncrement: true);
    fields["name"] = Field(FieldType.Text);
    fields["class"] = Field(FieldType.Text, foreignKey: true, to: "School_Class");
    fields["score"] = Field(FieldType.Real);

A column of data in the database is defined by the Field class. Let's first look at the Field definition to see what attributes our orm object supports.

class Field {

  final FieldType type;//Types include Integer, Real, Blob, Char, Text, Boolean

  bool unique;//Is it unique?

  int maxLength;

  bool primaryKey;//primary

  bool foreignKey;//Is it foreign key?

  bool autoIncrement;//Is it self increasing?

  String to;//Associate foreign key tables, named after DBName_TableName

  bool index;//Is there an index?

insert data

Single insertion

 Map m = {"name":"william", "class":"class1", "score":96.5};
 FlutterOrmPlugin.saveOrm("Student", m);

Batch insertion

 List orms = new List();
 for(int i = 0 ; i < 100 ; i++) {
      Map m = {"name":name, "class":className, "score":score};
 FlutterOrmPlugin.batchSaveOrms("Student", orms);

Query data

All queries

  Query("Student").all().then((List l) {


Query Article 1

 Query("Student").first().then((Map m) {

Query by primary key

  Query("Student").primaryKey([1,3,5]).all().then((List l) {

where Conditional Query

  Query("Student").whereByColumFilters([WhereCondiction("score", WhereCondictionType.EQ_OR_MORE_THEN, 90)]).all().then((List l) {

where sql statement query

  Query("Student").whereBySql("class in (?,?) and score > ?", ["class1","class2",90]).all().then((List l) {

where to query and sort

  Query("Student").orderBy(["score desc",]).all().then((List l) {

Query the specified column

  Query("Student").needColums(["studentId","name"]).all().then((List l) {

group by, have queries

  Query("Student").needColums(["class"]).groupBy(["class"]).havingByBindings("avg(score) > ?", [40]).orderBy(["avg(score)"]).all().then((List l) {

Update data

Update all

  Query("Student").update({"name":"test all update"});

Update according to primary key

  Query("Student").primaryKey([11]).update({"name":"test update by primary key"});

Update according to specific conditions

  Query("Student").whereByColumFilters([WhereCondiction("studentId", WhereCondictionType.LESS_THEN, 5),WhereCondiction("score", WhereCondictionType.EQ_OR_MORE_THEN, 0)]).update({"score":100});

Update according to custom where sql

  Query("Student").whereBySql("studentId <= ? and score <= ?", [5,100]).update({"score":0});

Delete data

Delete all


Delete by primary key


Delete by condition

  Query("Student").whereByColumFilters([WhereCondiction("studentId", WhereCondictionType.IN, [1,3,5])]).delete();

Delete according to custom where sql

  Query("Student").whereBySql("studentId in (?,?,?)", [1,3,5]).delete();

Linked table query

inner join

  JoinCondiction c = new JoinCondiction("Match");
  c.type = JoinType.INNER;
  c.matchColumns = {"studentId": "winnerId"};
  Query("Student").join(c).all().then((List l) {

left join

  JoinCondiction c = new JoinCondiction("Match");
  c.type = JoinType.LEFT;
  c.matchColumns = {"studentId": "winnerId"};
  Query("Student").join(c).all().then((List l) {

Using foreign keys to join tables

  JoinCondiction c = new JoinCondiction("Class");
  c.type = JoinType.INNER;
  Query("Student").join(c).all().then((List l) {


where sql join table

  JoinCondiction c = new JoinCondiction("Match");
  c.type = JoinType.INNER;
  c.matchColumns = {"studentId": "winnerId"};
  Query("Student").join(c).whereBySql("Student.score > ?",[60]).all().then((List l) {

Partial column Joint Table Query

  JoinCondiction c = new JoinCondiction("Match");
  c.type = JoinType.INNER;
  c.matchColumns = {"studentId": "winnerId"};
  Query("Student").join(c).needColums(["name","score"]).all().then((List l) {

group by, have join table query

  JoinCondiction c = new JoinCondiction("Class");
  c.type = JoinType.INNER;
  Query("Student").join(c).needColums(["class"]).groupBy(["Student.class"]).havingByBindings("avg(Student.score) > ?", [40]).all().then((List l) {

order by join table query

  JoinCondiction c = new JoinCondiction("Class");
  c.type = JoinType.INNER;
  Query("Student").join(c).orderBy(["Student.score desc"]).all().then((List l) {


Flutter_orm_plugin has been released to the flutter plug-in repository. As long as the configuration is simple, add the flutter_orm_plugin dependency and the Lua source file required by the ORM framework to the yaml file. The flutter_orm_plugin encapsulates all the Lua code. The final use only needs to care about the dart interface, and is insensitive to the lua.

  flutter_orm_plugin: ^1.0.0
    - packages/flutter_orm_plugin/lua/DB.lua
    - packages/flutter_orm_plugin/lua/orm/model.lua
    - packages/flutter_orm_plugin/lua/orm/cache.lua
    - packages/flutter_orm_plugin/lua/orm/dbData.lua
    - packages/flutter_orm_plugin/lua/orm/tools/fields.lua
    - packages/flutter_orm_plugin/lua/orm/tools/func.lua
    - packages/flutter_orm_plugin/lua/orm/class/fields.lua
    - packages/flutter_orm_plugin/lua/orm/class/global.lua
    - packages/flutter_orm_plugin/lua/orm/class/property.lua
    - packages/flutter_orm_plugin/lua/orm/class/query.lua
    - packages/flutter_orm_plugin/lua/orm/class/query_list.lua
    - packages/flutter_orm_plugin/lua/orm/class/select.lua
    - packages/flutter_orm_plugin/lua/orm/class/table.lua
    - packages/flutter_orm_plugin/lua/orm/class/type.lua

Add luakit dependencies to podfile in ios project

source 'https://github.com/williamwen1986/LuakitPod.git'
source 'https://github.com/williamwen1986/curl.git'


pod 'curl', '~> 1.0.0'
pod 'LuakitPod', '~> 1.0.17'

Add luakit dependencies to build.gradle for android project app

repositories {

    maven { url "https://jitpack.io" }



implementation 'com.github.williamwen1986:LuakitJitpack:1.0.9'

Configuration is complete and ready to use.

Topics: iOS Database github SQL git