B4J Library [B4X] MiniORMUtils

MiniORMUtils
Version: 5.40

---

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 ORMSettings
MS.Initialize
MS.DBType = DB.MYSQL
MS.JdbcUrl = "jdbc:mysql://{DbHost}:{DbPort}/{DbName}?characterEncoding=utf8&useSSL=False"
MS.DriverClass = "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")
DB.Id = 2
DB.Save2 = Array(Category_Id, Product_Code, Product_Name, Product_Price)

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

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