MiniORMUtils-B4X

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 MiniORMSettings 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 = "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)
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.InsertWithParams = Array(2, "T001", "Teddy Bear", 99.9)
DB.InsertWithParams = Array(1, "H001", "Hammer", 15.75)
DB.InsertWithParams = 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.Open
DB.Table = "categories"
DB.Query

Return single row

DB.Open
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.SaveWithParams = 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("LEFT", "categories 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