MiniORMUtils-B4X

Version: 5.20

A mini object–relational mapping (ORM) that can be use for creating db schema and SQL queries.
It is suitable for Web API Template or any database system.
Currently it supports SQLite (for B4A, B4i and B4J), MariaDB and MySQL (B4J only).

Usage examples

Initialize object

Dim DB As MiniORM
DB.Initialize

Initialize object (no execute)

DB.Initialize
DB.DbType = DB.SQLITE
DB.QueryExecute = False
DB.Table = "categories"
Log(DB.Statement)

Set file name for SQLite

DB.Settings.DBFile = "app.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 = "app"
MS.DbHost = "localhost"
MS.User = "root"
MS.Password = "password"
DB.Settings = MS

Check database exists

#If MySQL Or MariaDB
Wait For (DB.ExistAsync) Complete (DbFound As Boolean)
#Else
Dim DbFound As Boolean = DB.Exist
#End If
If DbFound Then
	LogColor($"${DB.DBType} database found!"$, COLOR_BLUE)
	DB.Open
Else
	LogColor($"${DB.DBType} database not found!"$, COLOR_RED)
	CreateDatabase
End If

Create database

#If MySQL Or MariaDB
Wait For (DB.CreateDatabaseAsync) Complete (Success As Boolean)
#Else
Dim Success As Boolean = DB.CreateSQLite
#End If

Connect to database

DB.Open

Create table (text only columns)

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
DB.Close

Select all rows

DB.Table = "categories"
DB.Query

Return single row

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

Read rows

Dim Data As List = DB.Results

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)

Soft delete row

DB.Id = 3
DB.SoftDelete

Permanent delete row

DB.Id = 4
DB.Delete

Batch delete rows

DB.Destroy(Array(2, 3))

Return number of rows in query results

Dim Rows As Int = DB.RowCount

Filter by conditions

DB.Table = "products"
DB.Conditions = Array("category_id = ?", "product_price > ?")
DB.Parameters = Array(2, 50)
DB.OrderBy = CreateMap("id": "DESC")

Join tables

DB.Table = "products p"
DB.Columns = Array("p.*", "c.category_name")
DB.Join = DB.CreateJoin("LEFT", "categories AS c", Array("p.category_id = c.id"))
DB.WhereParam("c.id = ?", CategoryId)

Show query logs

DB.ShowExtraLogs = True

Add query to batch

DB.QueryAddToBatch = True