B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder

MiniORMUtils
Version: 1.14

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.
Note: For SQLite, change DBName to DBFile if using MiniORMUtils v1.12+
B4X: 
con.DBFile = "MiniORM.db"

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.
Click to expand...

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.Insert2(Array("Hardwares"))
MDB.Insert2(Array("Toys"))

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 = 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.WhereValue(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

  • Demo.zip
    259.2 KB · Views: 167
  • MiniORMUtils.b4xlib
    14.4 KB · Views: 30
Last edited:
Click to expand...
aeric

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

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

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

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: 117
aeric

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

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
 
aeric

aeric

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

What's New
  1. Remove default assignment for INTEGER, DECIMAL and VARCHAR
  2. Add TIMESTAMP for column type
  3. Add DBUniqueKey
  4. Add DBConstraint
  5. Add DisableAutoIncrementId
  6. Add ExecuteAfterCreate
  7. Add ExecuteAfterInsert
  8. Add Collation in ORMColumn
  9. Add Find2 sub
  10. Update Create sub
  11. Add Unique sub
  12. Update Execute sub
  13. Update ExecuteBatch sub
  14. Update Query sub
  15. Update Insert sub
  16. Update Save sub, skipping appending "= ?" if "=" already added
  17. Remove localtime in sqlite
  18. DBSaveStatement for showing original statement during LogQuery
  19. Add ParametersCount
  20. Add TableExists2 sub
  21. Update CreateORMColumn
  22. Update CreateORMColumn2
  23. Remove redundant variables in DatabaseConnector
  24. Update DBCreate only support SQLite
  25. Add DBCreateSQLite to create db in WAL journal mode
  26. Add DBCreateMySQL for MySQL
  27. Update DBExist only support SQLite
  28. Add DBExists2 for MySQL
  29. Update DBOpen
  30. Add DBOpen2
  31. Remove H2 support
  32. Update GetDate
  33. Add GetDate2
  34. Update GetDateTime
  35. Add GetDateTime2
 
aeric

aeric

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

What's New
  1. Update DatabaseConnector Initialize sub not to replace DbName in Conn.JdbcUrl
  2. Update DatabaseConnector Initialize sub to replace DbFile in Conn.JdbcUrl
  3. Update replace DbName in DBOpen and DBOpen2 for MySQL
  4. Update not to close SQL object in GetDate, GetDate2, GetDateTime, GetDateTime2 subs
  5. Add AutoIncrement in Create sub
  6. Remove Int size for MySQL column in Create sub
  7. Update setting Primary key when DisableAutoIncrementId = False in Create sub
  8. Remove Logs for parameters in Create sub
  9. Add new "column++" for increment 1 update query in Save sub
 
Xfood

Xfood

Expert
Licensed User
hi aeric
, do you think it could be possible to integrate the possibility of managing a remote mysql db with php, for support such as altervista, aruba? maybe adding to this a small MiniORMUtils.php exchange file to upload to the altervista or aruba server?
 
aeric

aeric

Expert
Licensed User
Longtime User
Xfood said:
hi aeric
, do you think it could be possible to integrate the possibility of managing a remote mysql db with php, for support such as altervista, aruba? maybe adding to this a small MiniORMUtils.php exchange file to upload to the altervista or aruba server?
Click to expand...
PHP ? No, this is a B4X library. Please do not mix up. I am not going to write a PHP library.
I am not sure what you mean by altervista and aruba. I think it is a blogging software and cloud hosting respectively.
 
Xfood

Xfood

Expert
Licensed User
no, sorry, maybe I can't explain, but if you have a mysql db on altervista or on aruba, to read and write data from b4x more or less similar techniques are used

B4A Altervista mySql PHP

Ciao A tutti, ho attivato un account su altervista, creata un db di esempio, ma adesso mi piacerebbe potermi connette da un'app B4A, qualcuno mi puo aiutare a configurare un file php che mi permetta tramite parametri di inserire,ricercare,modificare i record della tabella. Grazie mille a tutti
www.b4x.com www.b4x.com

It would be nice to be able to adapt yours to these specs by now
 
aeric

aeric

Expert
Licensed User
Longtime User
Xfood said:
no, sorry, maybe I can't explain, but if you have a mysql db on altervista or on aruba, to read and write data from b4x more or less similar techniques are used

B4A Altervista mySql PHP

Ciao A tutti, ho attivato un account su altervista, creata un db di esempio, ma adesso mi piacerebbe potermi connette da un'app B4A, qualcuno mi puo aiutare a configurare un file php che mi permetta tramite parametri di inserire,ricercare,modificare i record della tabella. Grazie mille a tutti
www.b4x.com www.b4x.com

It would be nice to be able to adapt yours to these specs by now
Click to expand...
Not very clear of your question. Can you start a new thread?
If you are looking for PHP solution, I already posted https://www.b4x.com/android/forum/threads/user-login-mysql-php-api.117826/
 
You must log in or register to reply here.
Top