B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder

MiniORMUtils
Version: 1.14

Demo
Version: 1.03

This library can be use for creating db schema and performing CRUD operations.
It is suitable for Web API Template or any database system.
Currently it supports SQLite and MySQL (B4J).

A B4XPages Demo project attached. It is a modified app from Web API Client but use local SQLite. For B4J, it can be modified to connect to MySQL database.
Note: For SQLite, change DBName to DBFile if using MiniORMUtils v1.12+

con.DBFile = "MiniORM.db"

Initialization

Dim MDB As MiniORM
MDB.Initialize(Main.DBOpen, Main.DBEngine)
MDB.UseTimestamps = True
MDB.AddAfterCreate = True
MDB.AddAfterInsert = True

Take note: Before calling MDB.Create and MDB.Insert, set AddAfterCreate and AddAfterInsert to True.

Create Table

MDB.Table = "tbl_category"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
MDB.Create

Insert Data

MDB.Columns = Array("category_name")
MDB.Insert2(Array("Hardwares"))
MDB.Insert2(Array("Toys"))

Execute Batch NonQuery

Wait For (MDB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
    Log(LastException)
End If
MDB.Close

Select All Rows

MDB.Table = "tbl_category"
MDB.Query
Dim Items As List = MDB.Results

Joining Table

MDB.Table = "tbl_products p"
MDB.Select = Array("p.*", "c.category_name")
MDB.Join = MDB.CreateORMJoin("tbl_category c", "p.category_id = c.id", "")
MDB.WhereValue(Array("c.id = ?"), Array As String(CategoryId))
MDB.Query
Dim Items As List = MDB.Results

GitHub:
https://github.com/pyhoon/MiniORMUtils-B4X
https://github.com/pyhoon/MiniORM-Demo-B4X

 

[aeric]

MiniORMUtils
Version: 1.14
Size: 15KB

What's New

1. Add Create2
2. Add Query2
3. Add getScalar2
4. Add Insert2
5. Add Save2
6. Depreacated setWhereValue, replaced by WhereValue
7. Updated Snippets to use in Web API Server template

Example:
The following code inserts 2 items to table tbl_category.

MDB.Table = "tbl_category"
MDB.Columns = Array("category_name")
MDB.Insert2(Array("Hardwares"))
MDB.Insert2(Array("Toys"))