B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder

MiniORMUtils
Version: 1.10

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.

Initialization
B4X:
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
B4X:
MDB.Table = "tbl_category"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
MDB.Create

Insert Data
B4X:
MDB.Columns = Array("category_name")
MDB.Parameters = Array As String("Hardwares")
MDB.Insert
MDB.Parameters = Array As String("Toys")
MDB.Insert

Execute Batch NonQuery
B4X:
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
B4X:
MDB.Table = "tbl_category"
MDB.Query
Dim Items As List
Items.Initialize
If MDB.RowCount > 0 Then Items = MDB.Results

Joining Table
B4X:
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.setWhereValue(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
 

Attachments

  • MiniORMUtils.b4xlib
    13.8 KB · Views: 6
  • Demo.zip
    259.2 KB · Views: 7
Last edited:

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.02
Size: 12KB

What's New
  • (new) Query sub - if BlnUseTimestampsAsTicks = True returns Date Time columns as Ticks otherwise return as String
  • (new) Insert sub - if BlnUseTimestampsAsTicks = True and created_date value is not specified, value defaulted to current date time
  • (new) Save sub - if BlnUseTimestampsAsTicks = True and created_date value is not specified, value defaulted to current date time for insert
  • (new) Save sub - if BlnUpdateModifiedDate = True and modified_date value is not specified, value defaulted to current date time for update
  • (new) Columns for User Id audit (created, modified, deleted) can be added independent on BlnUseTimestamps
  • (update) Reset sub does not reset value for Boolean properties to False
 

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.03
Size: 14KB

What's New
  • (new) DatabaseConnector included
  • (new) Support B4J and (B4A and B4i only SQLite)

Dependencies:
B4J: JavaObject, jSQL
B4i: DBUtils, iSQL
B4A: DBUtils, SQL
 

aeric

Expert
Licensed User
Longtime User
MiniORM Demo
Version: 1.00

MiniORM Demo is a B4X UI app. It is modified from Web API Client but connect to SQLite database locally. For B4J, it can connect to MySQL database.
This demo demonstrates how to use MiniORMUtils library to CREATE tables, INSERT dummy data, SELECT, INSERT, UPDATE and DELETE records.
 

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.08
Size: 15KB

What's New
  • (new) support for B4i
  • (new) getFound property
  • (new) setShowExtraLogs property
  • (new) SortByLastId sub
  • (fix) Query sub - Use DBUtils.ExecuteJSON instead of DBUtils.ExecuteMap and DBUtils.ExecuteMemoryTable which causing wrong order of map key-value items in B4i
  • (fix) getScalar sub
  • (fix) DatabaseConnector class: clean up conditional symbols for B4J, server, B4A and B4i
  • (fix) bug: parameter Nullable changed to AllowNull due to compilation error in B4i
  • (fix) bug: First sub returns a default map if ORMTable is not initialized
  • (fix) bug: AddQuery sub creating a wrong copy of second last item due to passing List by reference
  • (fix) bug: Save sub return map of Insert or Update
  • (fix) Snippets: WebApi snippets updated, clean up some conditional symbols
  • (fix) Snippets: change default #Version = dev
  • (fix) Snippets: CreateDBConnection added
  • (remove) AddQuery2 sub - unused
  • (remove) CopyObject sub - independent from B4XSerializator (RandomAccessFile library)

Demo (B4XPages)
Version: 1.02
Size: 259KB

What's New
  • (fix) B4i app is now tested and working
  • (fix) new app icon
  • (fix) Pass String array as Parameters to support B4i/B4A
  • (fix) Bug cannot add item when Category table is empty
  • (fix) Check entered Product Price is a number
  • (fix) some UI changes
edit: A bug found in setRawSQL sub where DBParameters is not initialized. Will be fix in next version.
 
Last edited:

delgadol

Member
great job, now i try to performance a old code to sqlite b4a, a real room based on reflection , accept real entities; and very simple to use . i review your code and i will try to extract it. thanks in avance
 

Attachments

  • sqliteorm.png
    sqliteorm.png
    42.9 KB · Views: 21

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.09
Size: 15KB

What's New
  • (new) SelectOnly sub
  • (new) LogQuery sub
  • (fix) bug: setRawSQL sub DBParameters is not initialized

SelectOnly can be used when we want to return only certain columns (as JSON) in Web API since by default all columns from the new inserted row are returned.
Example:
B4X:
DB.Table = "tbl_invoices"
DB.Columns = Columns
DB.Parameters = Values
DB.Save

Dim ProcessedMap As Map = DB.SelectOnly(Array As String("invoice_identifier", "invoice_type", "supplier_tin", "buyer_tin", "invoice_submitted_type", "created_date"))
HRM.ResponseCode = 201
HRM.ResponseObject = ProcessedMap
HRM.ResponseMessage = "Invoice has been processed"

LogQuery can be used after DB.Query to show the executed SQL statement and the parameters passed to the query in the Logs.
Example:
B4X:
DB.Table = "tbl_statuses"
DB.Where = Array("status_name = ?", "id <> ?")
DB.Parameters = Array As String(data.Get("status_name"), id)
DB.Query
DB.LogQuery
 
Last edited:

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.10
Size: 14KB

What's New
  • (update) DatabaseConnector
    • (remove) DBFile
    • (add) DBHost
    • (add) DBPort
    • (clean) Initialize sub
      • JdbcUrl using placeholder
    • (update) DBCreate sub
      • return Boolean (Resumable sub) instead of SQL object
      • JdbcUrl using placeholder
    • (clean) DBExist sub
      • JdbcUrl using placeholder
    • (remove) DBExist2 sub
    • (clean) DBOpen sub
    • (clean) GetDate sub
    • (clean) GetDateTime sub
    • (clean) getLastInsertIDQuery sub
 
Top