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: 187
  • MiniORMUtils.b4xlib
    14.4 KB · Views: 51
Last edited:
Click to expand...
aeric

aeric

Expert
Licensed User
Longtime User
aeric said:
B4X: 
' INSERT
db1.DataColumn = CreateMap("category_id": 2, "product_code": "T003", "product_name": "YoYo", "product_price": 9.25)
db1.Save
Click to expand...

We can also write using Map.Put("ColumnName", "Value")
B4X: 
' INSERT
Dim rec As Map
rec.Initialize
rec.Put("category_id", cid)
rec.Put("product_code", data.Get("code"))
rec.Put("product_name", data.Get("name"))
rec.Put("product_price", data.Get("price"))
Dim db1 As ORM
db1.Initialize(con)
db1.Table = "tbl_products"
db1.DataColumn = rec
db1.Save

Example of modified PostProductByCategory from Web API template.
ProductHandler.bas: 
Sub PostProductByCategory (cid As Int) As HttpResponseMessage
    #region Documentation
    ' #Body = {<br>&nbsp; "code": "product_code",<br>&nbsp; "name": "product_name",<br>&nbsp; "price": "product_price"<br>}
    ' #Desc1 = (N/A)
    ' #Desc2 = Add a new product by category id
    ' #Elems = 4
    #End region
    Dim con As SQL = Main.DB.GetConnection
    Dim strSQL As String
    Try
        strSQL = Main.queries.Get("GET_CATEGORY_BY_ID")
        Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(cid))
        If res.NextRow Then
            Dim data As Map = Utility.RequestData(Request)
            If data.IsInitialized Then
                'strSQL = Main.queries.Get("ADD_NEW_PRODUCT_BY_CATEGORY")
                con.BeginTransaction
                'con.ExecNonQuery2(strSQL, Array As String(cid, data.Get("code"), data.Get("name"), data.Get("price")))
            
                ' INSERT
                Dim rec As Map
                rec.Initialize
                rec.Put("category_id", cid)
                rec.Put("product_code", data.Get("code"))
                rec.Put("product_name", data.Get("name"))
                rec.Put("product_price", data.Get("price"))
                Dim db1 As ORM
                db1.Initialize(con)
                db1.Table = "tbl_products"
                db1.DataColumn = rec
                db1.Save
            
                strSQL = Main.queries.Get("GET_LAST_INSERT_ID")
                Dim NewId As Int = con.ExecQuerySingleResult(strSQL)
                strSQL = Main.queries.Get("GET_PRODUCT_BY_CATEGORY_AND_ID")
                Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(cid, NewId))
                con.TransactionSuccessful
                Dim List1 As List
                List1.Initialize
                Do While res.NextRow
                    Dim Map2 As Map
                    Map2.Initialize
                    For i = 0 To res.ColumnCount - 1
                        If res.GetColumnName(i) = "product_price" Then
                            Map2.Put(res.GetColumnName(i), res.GetDouble2(i))
                        Else If res.GetColumnName(i) = "category_id" Or res.GetColumnName(i) = "id" Then
                            Map2.Put(res.GetColumnName(i), res.GetInt2(i))
                        Else
                            Map2.Put(res.GetColumnName(i), res.GetString2(i))
                        End If
                    Next
                    List1.Add(Map2)
                Loop
                HRM.ResponseCode = 201
                HRM.ResponseMessage = "Created"
                HRM.ResponseData = List1
            End If
        Else
            HRM.ResponseCode = 404
            HRM.ResponseError = "Category Not Found"
        End If
    Catch
        LogError(LastException)
        HRM.ResponseCode = 422
        HRM.ResponseError = "Error Execute Query"
    End Try
    Main.DB.CloseDB(con)
    Return HRM
End Sub
 
Last edited:
aeric

aeric

Expert
Licensed User
Longtime User
Teaser of project "EndsMeet" - a B4J MVC web framework using this Mini ORM
This is an ambitious project that I don't know when I have time to create. ?
 

Attachments

  • 1656584449556.png
    1656584449556.png
    115.1 KB · Views: 754
  • 1656584523311.png
    1656584523311.png
    123.3 KB · Views: 714
Last edited:
aeric

aeric

Expert
Licensed User
Longtime User
Mashiane said:
Q. For the filters, how do you add multiple filters e.g. other fields?
Q. How do you perform an "or" filter on some column (same / different)

Thanks.
Click to expand...
This is an old thread.
MiniORM latest version 0.03 is attached in post #2 of Web API Template 2.

A quick one.
Actually I seldom use MiniORM and it is in beta development. ?

Let say the SQL I want to create is:
SQL: 
SELECT *
FROM
tbl_users
WHERE
role = 'cashier' AND status = 1 AND deleted_at IS Null
I can write:
B4X: 
DB.Table = "tbl_users"
DB.Where = Array("role = ?", "status = ?", "deleted_at IS Null")
DB.Parameters = Array("cashier", 1)
DB.Query

I also seldom use OR in my SQL query.
Let say the query is:
SQL: 
SELECT
user_name, user_email
FROM
tbl_users
WHERE
role = 'sales' AND status = 1 OR status = 2

I can write:
B4X: 
DB.Table = "tbl_users"
DB.Select = Array("user_name", "user_email")
DB.Where = Array("role = ?", "status = ? OR status = ?")
DB.Parameters = Array("sales", 1, 2)
DB.Query
 
MichalK73

MichalK73

Well-Known Member
Licensed User
Longtime User
aeric said:
' CREATE TABLE Dim fields As List fields.Initialize Dim Col1 As ORMColumn = db1.CreateORMColumn("category_id", db1.INTEGER, "", "", False, False) Dim Col2 As ORMColumn = db1.CreateORMColumn("product_code", db1.VARCHAR, "", "", True, False) Dim Col3 As ORMColumn = db1.CreateORMColumn("product_name", db1.VARCHAR, "", "", True, False) Dim Col4 As ORMColumn = db1.CreateORMColumn("product_price", db1.DECIMAL, "", "'0.00'", True, False) fields.AddAll(Array(Col1, Col2, Col3, Col4)) db1.Table = "tbl_products" db1.Create(fields, True) db1.Foreign("category_id", "id", "tbl_category", "", "") db1.Execute
Click to expand...
I welcome you.

Well done !!
I recently work in python with SQLAlchemy and there is that if I add a new column to the table, at the beginning of creation, the code checks if all the columns are already in the table. If not, it probably does 'ALTER TABLE' and adds the missing columns.
I don't know if your code does it, if you can't add it.
That's my little suggestion.
 
aeric

aeric

Expert
Licensed User
Longtime User
MichalK73 said:
I welcome you.

Well done !!
I recently work in python with SQLAlchemy and there is that if I add a new column to the table, at the beginning of creation, the code checks if all the columns are already in the table. If not, it probably does 'ALTER TABLE' and adds the missing columns.
I don't know if your code does it, if you can't add it.
That's my little suggestion.
Click to expand...
Thanks for your suggestion.
Usually I would ensure that I am clear that I need to CREATE or ALTER a table.
I know sometimes we need to create a new migration using framework like Django.
Although it is possible but I haven't encounter such situation in my projects. I seldom modify database tables which already in production.

In situation where I need to alter a table, I would :
For database on server:
Directly modify the table structure using database management tool like Adminer/PHPMyAdmin/SQLite DB Browser
For database on client:
Create a temporary table and insert the old data and drop the old table. Create the new table and copy the old data to the new table. Finally drop the temporary table.

So, I would not put this as priority.
 
MichalK73

MichalK73

Well-Known Member
Licensed User
Longtime User
aeric said:
Thanks for your suggestion.
Usually I would ensure that I am clear that I need to CREATE or ALTER a table.
I know sometimes we need to create a new migration using framework like Django.
Although it is possible but I haven't encounter such situation in my projects. I seldom modify database tables which already in production.

In situation where I need to alter a table, I would :
For database on server:
Directly modify the table structure using database management tool like Adminer/PHPMyAdmin/SQLite DB Browser
For database on client:
Create a temporary table and insert the old data and drop the old table. Create the new table and copy the old data to the new table. Finally drop the temporary table.

So, I would not put this as priority.
Click to expand...
I understand you. I just use it in python and don't think about the structure, it adds a new column or deletes the unnecessary one and the code does it for me. You don't have to do it on the database. This is what ORM is all about.
 
aeric

aeric

Expert
Licensed User
Longtime User
This class is a sub product of my Web API and Ticketing system projects.

It tries to simplify some code.

I am not lucky to work on complex project. Most projects I work with are too simple. ?
 
aeric

aeric

Expert
Licensed User
Longtime User
This class has been updated to version 0.08
It is now supporting MySQL database, batch non query, new CreateORMColumn2 sub and fixed some bugs.

CreateORMColumn
B4X: 
DB1.Table = "tbl_products"
DB1.Columns.Add(DB1.CreateORMColumn("category_id", DB1.INTEGER, "", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_code", DB1.VARCHAR, "12", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_name", DB1.VARCHAR, "200", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_price", DB1.DECIMAL, "10,2", "0.00", True, False))
DB1.UseTimestamps = True
DB1.Foreign("category_id", "id", "tbl_category", "", "")
DB1.Create

CreateORMColumn2
B4X: 
DB1.Table = "tbl_products"
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_id", "Type": DB1.INTEGER)))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_code", "Length": "12")))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_name")))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_price", "Type": DB1.DECIMAL, "Length": "10,2", "Default": "0.00")))
DB1.UseTimestamps = True
DB1.Foreign("category_id", "id", "tbl_category", "", "")
DB1.Create
Default column type is varchar with length 255 characters (or Text in SQLite), Nullable and AutoIncrement = False.
 
giannimaione

giannimaione

Well-Known Member
Licensed User
Longtime User
where is the error?

B4X: 
#Region Project Attributes 
    #MainFormWidth: 600
    #MainFormHeight: 600 
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region
*
*
B4X: 
Sub Process_Globals
    Dim SQL As SQL
End Sub
*
*
*
B4X: 
SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY (no table created)
    Dim DB1 As MiniORM
    DB1.Initialize(SQL)
    DB1.Engine = "SQLite"
    '
    DB1.Table = "tbl_category"
    DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
    DB1.UseTimestamps = True
    DB1.Create
    '
    DB1.Columns = Array("category_name")
    DB1.Parameters = Array("Hardwares")
    DB1.Insert
    DB1.Parameters = Array("Toys")
    DB1.Insert
    '
    Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
    Log(Success)
    If Success Then
        Log("Database is created successfully!") 'always success
    End If
    DB1.Close
 
aeric

aeric

Expert
Licensed User
Longtime User
giannimaione said:
where is the error?

B4X: 
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region
*
*
B4X: 
Sub Process_Globals
    Dim SQL As SQL
End Sub
*
*
*
B4X: 
SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY (no table created)
    Dim DB1 As MiniORM
    DB1.Initialize(SQL)
    DB1.Engine = "SQLite"
    '
    DB1.Table = "tbl_category"
    DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
    DB1.UseTimestamps = True
    DB1.Create
    '
    DB1.Columns = Array("category_name")
    DB1.Parameters = Array("Hardwares")
    DB1.Insert
    DB1.Parameters = Array("Toys")
    DB1.Insert
    '
    Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
    Log(Success)
    If Success Then
        Log("Database is created successfully!") 'always success
    End If
    DB1.Close
Click to expand...
I haven't tested on UI app.
Can you upload it?
 
aeric

aeric

Expert
Licensed User
Longtime User
Sorry, I didn't mention about the following settings:

B4X: 
    ' These 2 lines are required
    DB1.AddAfterCreate = True
    DB1.AddAfterInsert = True

B4X: 
Sub runit
    SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY
    Dim DB1 As MiniORM
    DB1.Initialize(SQL)
    DB1.Engine = "SQLite"
    '
    ' These 2 lines are required
    DB1.AddAfterCreate = True
    DB1.AddAfterInsert = True
   
    DB1.Table = "tbl_category"
    DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
    DB1.UseTimestamps = True
    DB1.Create
    '
    DB1.Columns = Array("category_name")
    DB1.Parameters = Array("Hardwares")
    DB1.Insert
    DB1.Parameters = Array("Toys")
    DB1.Insert
    '
    Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
    Log(Success)
    If Success Then
        Log("Database is created successfully!")
    End If
    DB1.Close
End Sub

Edit: I have updated the remarks in post #1. Thanks @giannimaione for reporting.
 
Last edited:
aeric

aeric

Expert
Licensed User
Longtime User
MiniORMUtils
Version: 1.01
Size: 11KB

What's New
  • (fix) Save sub returns map for Insert and Update using First sub
  • (new) Initialize sub now expects DBEngine as second parameter
  • (update) Support for Web API Server Template (version 2.05)
  • (update) Return ID with getID
  • (update) Code snippets
 
You must log in or register to reply here.

Similar Threads

Erel
  • Locked
  • Article
Android Tutorial [B4X] B4XPages + B4XDrawer
Replies
4
Views
17K
Erel
Erel
Erel
  • Article
Android Code Snippet [B4X] Comment link to build b4xlib
Replies
4
Views
5K
Sandman
Sandman
LucaMs
  • Article
B4A Library [B4X] B4XTblClmUtils V. 1.0
Replies
1
Views
2K
hatzisn
hatzisn
Alessandro71
  • Article
Android Tutorial [B4X] B4X lib automatic build
Replies
0
Views
2K
Alessandro71
Alessandro71
Erel
  • Article
B4J Library [B4X] DBUtils 2
2 3 4
Replies
76
Views
52K
Fizzy
F
Top