B4J Library [B4X] [SQL] MiniORMUtils

MiniORMUtils
Version: 6.04
GitHub: https://github.com/pyhoon/MiniORMUtils-B4X

---

This library can be use for creating database tables and performing CRUD operations.
It is suitable for B4X apps, non-UI web app or REST API servers.
It is useful to return the query results as JSON since the rows are list of maps.
Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only).

Project Template:
[B4X] [Project Template] MiniORM

Examples:​

Initialize MiniORM

Private DB As MiniORM
DB.Initialize

Set file name for SQLite

DB.Settings.DBFile = "blog.db"

Set ORMSettings for MySQL

DB.Initialize
Dim MS As MiniORMSettings
MS.Initialize
MS.DBType = DB.MYSQL
MS.JdbcUrl = "jdbc:mysql://{DbHost}:{DbPort}/{DbName}?characterEncoding=utf8&useSSL=False"
MS.Driver = "com.mysql.cj.jdbc.Driver"
MS.DBName = "blog"
MS.DbHost = "localhost"
MS.User = "root"
MS.Password = "password"
DB.Settings = MS

Check if database exist (SQLite)

If Not(DB.Exist) Then
    LogColor($"${DB.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create Database

Dim Success As Boolean = DB.CreateSQLite

Connect to Database

DB.Open

Create Table

DB.Table = "categories"
DB.Columns = Array("category_code", "category_name")
DB.Create

Create Table (with column definitions)

DB.Table = "products"
DB.Columns.Add(CreateMap("N": "category_id", "T": DB.INTEGER))
DB.Columns.Add(CreateMap("N": "product_code", "S": 12))
DB.Columns.Add(CreateMap("N": "product_name"))
DB.Columns.Add(CreateMap("N": "product_price", "T": DB.DECIMAL, "S": "10,2", "D": 0.0))
DB.Columns.Add(CreateMap("N": "product_image", "T": DB.BLOB))
DB.Foreign = "category_id"
DB.References("categories", "id")
DB.Create

Insert Rows

DB.Columns = Array("category_id", "product_code", "product_name", "product_price")
DB.Inserts = Array(2, "T001", "Teddy Bear", 99.9)
DB.Inserts = Array(1, "H001", "Hammer", 15.75)
DB.Inserts = Array(2, "T002", "Optimus Prime", 1000)

Execute NonQuery Batch

Wait For (DB.ExecuteBatchAsync) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
End If

Select All Rows

DB.Table = "categories"
DB.Query
If DB.Error.IsInitialized Then
    Log(DB.Error.Message)
Else
    Dim Items As List = DB.Results
End If

Return single row

DB.Find(3)
If DB.Found Then
    Log(DB.First)
End If

Update Row

DB.Table = "products"
DB.Columns = Array("category_id", "product_code", "product_name", "product_price", "modified_date")
DB.Parameters = Array(Category, Code, Name, Price, Modified_Date)
DB.Condition = "id = ?"
DB.Parameter = 2

Spoiler: Version 1

[B4X] MiniORMUtils - SQL Query Builder

Updates Version 2.00 is available https://www.b4x.com/android/forum/threads/b4x-miniormutils.166030/ MiniORMUtils Version: 1.17 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...

www.b4x.com

 

[aeric]

Version: 5.40

What's Changed​

1. Update ExecuteBatchAsync sub
   • Set QueryAddToBatch to False
   • Clear batch statement and parameters

 

[aeric]

Version: 5.50

What's New​

1. Update Join sub (breaking)
2. Update Query sub
3. Update SelectFromObject sub
4. Update BuildColumns sub
5. Update ExecuteScalar sub
6. Update ExecuteScalar2 sub
7. Update getCondition sub
8. Update setCondition sub
9. Update setConditions sub
10. Update setParameters sub
11. Update WhereParams sub
12. Update Save sub
13. Update Save3 sub
14. Update Delete sub
15. Update Destroy sub
16. Update SoftDelete sub
17. Update getStatement sub
18. Remove setJoin sub
19. Remove getJoins sub
20. Remove CreateORMColumn sub
21. Remove CreateORMJoin sub
22. Add IsNotNull sub (Private)
23. Add ReturnNewRow property
24. Add QueryAutoClose property

 

[aeric]

Version: 5.60

What's New​

1. Rename ReturnNewRow property to ReturnRow

 

[aeric]

Version: 6.02

Support for Pakai Server Ultimate v6.99 template
accidentally set "L" for column length shortcut instead of using "S" for size.

 

[aeric]

Version: 6.03

Small fix for UNION sub

 

[aeric]

Version: 6.04

1. Removed QueryRaw public property
2. Added FinalizeQuery (private)