B4J Code Snippet [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 SQLite and MySQL (B4J).

---

B4X project template
Version: 1.06

[B4X] [Project Template] MiniORM

Version 2.00 A quick app using MiniORMUtils library with add, edit, delete item MiniORM-Demo repackaged as b4xtemplate from https://www.b4x.com/android/forum/threads/b4x-miniormutils-sql-query-builder.141446/ GitHub: https://github.com/pyhoon/MiniORM-B4X Depends on: B4XPages...

www.b4x.com

Examples:

Initialization

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

MDB.Table = "tbl_category"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
MDB.Create

Insert Data

MDB.Columns = Array("category_name")
MDB.Insert2(Array As String("Hardwares"))
MDB.Insert2(Array As String("Toys"))

Execute Batch NonQuery

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

MDB.Table = "tbl_category"
MDB.Query
Dim Items As List = MDB.Results

Joining Table

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-B4X
https://github.com/pyhoon/MiniORM-Demo-B4X

 

[aeric]

MiniORMUtils
Version: 1.14
Size: 15KB

What's New

1. Add Create2
2. Add Query2
3. Add getScalar2
4. Add Insert2
5. Add Save2
6. Depreacated setWhereValue, replaced by WhereValue
7. Updated Snippets to use in Web API Server template

Example:
The following code inserts 2 items to table tbl_category.

MDB.Table = "tbl_category"
MDB.Columns = Array("category_name")
MDB.Insert2(Array("Hardwares"))
MDB.Insert2(Array("Toys"))

 

[aeric]

MiniORMUtils
Version: 1.15
Size: 10KB

What's New

1. Added DATE_TIME and TEXT global variables for column type (more useful for MySQL)
2. Added setDefaultUserId sub and StrDefaultUserId variable
3. Added setHaving sub and DBHaving variable for Group By query
4. Updated setGroupBy sub
5. Updated CountChar sub due to TEXT variable
6. Renamed Condition variable to DBCondition
7. Removed all snippets (Code Snippets for server handlers already added to WebApiUtils library)

 

[aeric]

MiniORM Demo
Version: 1.04
Size: 261KB

What's New

1. Added example of using different ways (e.g adding columns) with comments

 

[aeric]

MiniORMUtils
Version: 1.16
Size: 10KB

What's New

1. Added BIG_INT global variables for column type (more useful for MySQL)
2. Updated Create, CreateORMColumn, CreateORMColumn2 and subs

 

[aeric]

MiniORMUtils
Version: 1.17
Size: 14KB

What's New

1. Fix DBExist sub for B4A and B4i
2. Fix DBOpen in GetDate2 and GetDateTime2 subs
3. Fix setParameters for B4A and B4i expect Array As String
4. Fix Find2, Query2, Insert2, Save2, getScalar2 which are using setParameters
5. Fix ConnectionPool only apply to B4J
6. Added JSON dependency for B4A and B4i for showing extra logs in Query sub
7. Update README example

Changes when passing parameters
Old:

DB.Insert2(Array("Hardwares")) ' only works in B4J

New:

DB.Insert2(Array As String("Hardwares")) ' works in B4A, B4i and B4J

 

[Mashiane]

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?

Just saw this as I am browing this.

For PHP, you can use this PHP Wrap.

GitHub - mevdschee/php-crud-api: Single file PHP script that adds a REST API to a SQL database

Single file PHP script that adds a REST API to a SQL database - mevdschee/php-crud-api

github.com

I have tried it and it works perfectly.