B4J Library [B4X] [SQL] MiniORMUtils

MiniORMUtils
Version: 6.04
GitHub: https://github.com/pyhoon/MiniORMUtils-B4X
This library can be use for creating database tables and performing CRUD operations.
It is suitable for B4X apps, non-UI web app or REST API servers.
It is useful to return the query results as JSON since the rows are list of maps.
Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only).

Visor


Project Template:
[B4X] [Project Template] MiniORM

Examples:​


Initialize MiniORM
B4X: Copy 
Private DB As MiniORM
DB.Initialize

Set file name for SQLite
B4X: Copy 
DB.Settings.DBFile = "blog.db"

Set ORMSettings for MySQL
B4X: Copy 
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 = "blog"
MS.DbHost = "localhost"
MS.User = "root"
MS.Password = "password"
DB.Settings = MS

Check if database exist (SQLite)
B4X: Copy 
If Not(DB.Exist) Then
    LogColor($"${DB.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create Database
B4X: Copy 
Dim Success As Boolean = DB.CreateSQLite

Connect to Database
B4X: Copy 
DB.Open

Create Table
B4X: Copy 
DB.Table = "categories"
DB.Columns = Array("category_code", "category_name")
DB.Create

Create Table (with column definitions)
B4X: Copy 
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
B4X: Copy 
DB.Columns = Array("category_id", "product_code", "product_name", "product_price")
DB.Inserts = Array(2, "T001", "Teddy Bear", 99.9)
DB.Inserts = Array(1, "H001", "Hammer", 15.75)
DB.Inserts = Array(2, "T002", "Optimus Prime", 1000)

Execute NonQuery Batch
B4X: Copy 
Wait For (DB.ExecuteBatchAsync) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
End If

Select All Rows
B4X: Copy 
DB.Table = "categories"
DB.Query
If DB.Error.IsInitialized Then
    Log(DB.Error.Message)
Else
    Dim Items As List = DB.Results
End If

Return single row
B4X: Copy 
DB.Find(3)
If DB.Found Then
    Log(DB.First)
End If

Update Row
B4X: Copy 
DB.Table = "products"
DB.Columns = Array("category_id", "product_code", "product_name", "product_price", "modified_date")
DB.Parameters = Array(Category, Code, Name, Price, Modified_Date)
DB.Condition = "id = ?"
DB.Parameter = 2

[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...
www.b4x.com www.b4x.com
 

Attachments

Last edited:
Click to expand...
Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.
 
aeric said:
Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.
Click to expand...

Found the issue.
I can't use DBParameters straight away. This can cause an issue of pass by reference.

Code: Copy 
Public Sub AddNonQueryToBatch
    Dim paramsize As Int = ParametersCount
    Dim Args(paramsize) As Object
    Dim i As Int
    For Each Param In DBParameters
        Args(i) = Param
        i = i + 1
    Next
    DBSQL.AddNonQueryToBatch(DBStatement, Args)
    'DBSQL.AddNonQueryToBatch(DBStatement, DBParameters) ' Wrong
End Sub
 
Version: 3.30
Size: 14KB

Another major update!

What's New

1. BLOB column is now supported
2. ColumnsType property is added as Map of column name (or alias) as key and column type as value.
3. Specifying the ColumnsType is highly recommended especially when working with BLOB. It will fail in B4A if attempt to convert BLOB to String.
4. Parameters variable is now array of Object. It is no longer a List in B4J nor array of String in B4A and B4i.
5. Most subs are affected by #4 and updated
6. Query sub updated
7. DBUtils and JSON libraries are no longer required for B4A and B4i. These libraries have been removed in manifest.txt
8. First property is not returning a default Map
9. Execute2 sub added
 
Version: 3.40

What's New
  1. MariaDB is supported
  2. BLOB column is default to mediumblob for MariaDB and MySQL
  3. Update ExecQuery sub
  4. Update code example in B4XMainPage
  5. Added example for testing MariaDB
  6. Added InitSchema2
  7. Added LogQuery3 sub to print out DBStatements and parameters in DBBatch
  8. ShowExtraLogs in ExecuteBatch using LogQuery3
  9. Update README.md
  10. Change comment links to #Macro tags
 
Version: 3.60

What's New
  1. Changed Error property from String type to Exception type
    • You can now check Error with
      B4X: Copy 
      DB.Error.IsInitialized
    • and then get the description using
      B4X: Copy 
      DB.Error.Message
  2. ExecQuery sub has added with Try-Catch
  3. ExecNonQuery sub has added with Try-Catch
  4. Added #Macro tags
    • #Macro: Title, Release, ide://run?File=%WINDIR%\SysWOW64\explorer.exe&Args=%PROJECT%\..\..\release
    • #Macro: Title, Sync, ide://run?File=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
    • #Macro: Title, Publish, ide://run?File=%JAVABIN%\jar.exe&WorkingDirectory=..\..\..\release&Args=-cMf&Args=%ADDITIONAL%\..\B4X\%PROJECT_NAME%.b4xlib&Args=*
 
aeric said:
More examples on GitHub README page
Click to expand...
Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:
B4X: Copy 
'con.DBType = "MySQL"
Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? :mad: 😁)
 
LucaMs said:
Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:
B4X: Copy 
'con.DBType = "MySQL"
Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? :mad: 😁)
Click to expand...
I am happy to see questions! :)
Let me answer them one by one.

I was wondering: "More examples," but where's an example here?
Click to expand...
There are actually indeed more examples on GitHub Readme.
The extra examples are:
  1. Soft delete row
  2. Permanent delete row
  3. Batch delete rows
  4. Return number of rows in query results
  5. Return single row
  6. Return multiple rows
  7. Join tables
Then I downloaded the library and the project template and realized the template itself "is" the example.
Click to expand...
Yes! You are right.
This template [B4X] [Project Template] MiniORM is a complete working project.

It's both a mistake and a useful thing that the template contains an actual project.
Click to expand...
It's useful because you immediately have a complete example;
Click to expand...
Yes, because I think it can be a good starter project to extend further for many more purposes. e.g as a POC.

it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
Click to expand...
My concern is developers new to MiniORM will face difficulty to understand the concept.
The project template can let them see an immediate result without starting to read the documentation. It is almost zero configuration.
In fact, my real world projects are much more complicated.

Another advantage of project template is you can use any name for the project and usually created in a frequently use development path (more organized).

There are more reasons and benefits of project template instead of a "zipped" project file but I stopped here.

By the way, the main purpose of this project template is to showcase one of the uses of this library; for creating B4X cross platforms client apps (B4A, B4i, B4J).
Whilst MiniORMUtils can be use for UI apps for B4X, it is also part of the dependencies of Pakai framework which is a non-ui web server solution.

MiniORMUtils has a great "history".
Actually there is a more powerful library (now call CapORM) which is not generally available.
It supports wider database type i.e MS SQL Server, PostgreSQL and Firebird instead of only SQLite and MySQL/MariaDB
MiniORM is just a sub library.

The project template is guaranteed in high quality, well tested and clean of redundant files.
The file size is very small.
If you want, you can modify the template or remove the parts you think you don't want.
Then create your own version. The freedom is on your hand.

LucaMs said:
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.
Click to expand...
I don't agree it is a mistake.
Project Template can also be use as DRY principle (Don't Repeat Yourself).
It is a good way to start a project from a clean but included with features that are ready to be used.
My Pakai Server template is the best example I can think of.
Everything is ready to run. I can extend the generated boilerplate to many types of server apps.
You help other developers saving their time looking for other piece of puzzle.

LucaMs said:
I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?
Click to expand...
Mentioned above, Pakai Server is the best mate for MiniORMUtils. You can just run it with SQLite backend. Zero configuration.
Another example is User Login Server but it is based on older version of libraries.

LucaMs said:
Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants
Click to expand...
The constants are already existed in MiniORM class.
B4X: Copy 
Public Const MYSQL As String = "MYSQL"
Public Const SQLITE As String = "SQLITE"
Public Const MARIADB As String = "MARIADB"

For other questions, I welcome you to start a new thread.
 
Version: 3.70

What's New
  1. Handle error when ExecQuery failed in Query sub
  2. Pass Exception to Error property in subs:
    • DBCreate
    • InitPool
    • InitSchema
    • DBExist2
    • DBOpen2
    • GetDate
    • GetDate2
    • GetDateTime
    • GetDateTime2
  3. Add #Macro tag in B4XMainPage:
    • #Macro: Title, Copy, ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=copy&Args=%PROJECT%\..\*ORM*.bas&Args=%PROJECT%\..\..\release\
 
Last edited:
Version: 3.80

What's New​

  1. Added Sub ListTables
  2. Added Sub ListTables2
  3. Added Sub ShowCreateTable
  4. Added Sub ShowCreateTable2
  5. Remove redundant code in "release" folder. It is now only keep compressed b4xlib.
  6. Remove unused color constant (MiniORM)
  7. Update build configuration (B4J)
  8. Update #AdditionalJar based on conditional compilation (Main)
  9. Sub ConfigureDatabase (B4XMainPage)
    • Update ConnectionInfo based on conditional compilation
    • Update Conn.InitPool based on DBType
    • Update #If B4J conditional compilation to exclude code for MySQL/MariaDB in B4A/B4i
    • Update targetSdkVersion = 35 for B4A
  10. Fix default (datetime('now')) for SQLite in subs (MiniORM)
    • Create
    • Insert
    • Save
    • Save3
  11. Type ORMColumn (MiniORM)
    • Added property UseFunction As Boolean
  12. Sub Create (MiniORM)
    • Updated code for DefaultValue logic
    • Updated code for BlnAutoIncrement logic
  13. Sub Save (MiniORM)
    • Do not return NewID if BlnAutoIncrement = False
  14. Sub CreateColumn (MiniORM)
    • Added code for UseFunction logic
  15. Sub CreateColumn2 (MiniORM)
    • Added code for UseFunction logic
  16. Macro tags (B4XMainPage)
    • Update Version
    • Create B4xLib
    • Release folder
    • Copy to AddLibs
 
Version: 3.90

What's Updated​

  1. setGroupBy sub
  2. Save3 sub

What's Changed​

  1. setColumns sub is replacing setSelect sub
  2. SelectOnly sub renamed to FirstPick
  3. SelectOnly marked as deprecated
  4. setSelect marked as deprecated
  5. Some variables name
  6. Some logs message
 
Version: 4.00

This is a breaking changes!

What's Updated​

  1. ORMConnector is removed
  2. ConnectionInfo is removed (following by the removal of ORMConnector)
  3. ORMSettings is introduced to replace the function of ConnectionInfo
  4. Initialize sub now doesn't accept parameters
  5. Some subs renamed e.g ExecuteBatchAsync, CreateDatabasesAsync (MySQL, MariaDB) and InitializeSQLite (SQLite)

What's Changed​


Initialize MiniORM
B4X: Copy 
Private DB As MiniORM
Private MS As ORMSettings

DB.Initialize
MS.Initialize
MS.DBType = "SQLite"
MS.DBFile = "data.db"
MS.DBDir = File.DirApp
DB.Settings = MS

Create ConnectionInfo object
B4X: Copy 
Dim info As ConnectionInfo
info.Initialize
info.DBType = "SQLite"
info.DBFile = "data.db"

Create ORMConnector object
B4X: Copy 
Dim conn As ORMConnector
conn.Initialize(info)

Initialize MiniORM
B4X: Copy 
Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)
DB.QueryAddToBatch = True

Check if database exist
B4X: Copy 
If Not(DB.Exist) Then
    LogColor($"${MS.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

B4X: Copy 
Dim DBFound As Boolean = conn.DBExist
If DBFound = False Then
    LogColor($"${conn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create Database
B4X: Copy 
Dim Success As Boolean = DB.InitializeSQLite

B4X: Copy 
Dim Success As Boolean = conn.DBCreate

Check the updated examples:

B4X: https://www.b4x.com/android/forum/threads/b4x-project-template-miniorm-b4a-b4i-b4j.165499/
B4J Server:
https://www.b4x.com/android/forum/threads/web-project-template-pakai-server-v6.169224/
 
Last edited:
Version: 4.10
Note: This version has breaking changes

What's New​

  1. NewCondition (Private)
  2. AppendWhereOrAndClause (Private)
  3. ClearConditions (Private)
  4. Last
  5. Last2
  6. Last3
  7. First
  8. First2
  9. FirstId
  10. RowCount
  11. Found
  12. QueryExecute (Property)
  13. OptionalNull (Property)
  14. Condition (Property)
  15. Conditions (Property)
  16. Parameter (Property)
  17. Parameters (Property)
  18. Primary (Property)
  19. Foreign (Property)
  20. References

What's Changed​

  1. Changes from property to functions
    1. getFirst -> First
    2. getFirst2 -> First2
    3. getLast -> Last
    4. getFirstId -> FirstId
    5. getRowCount -> RowCount
    6. getFound -> Found
    7. getPrimary -> Primary
  2. Changes from function to property
    1. Primary -> setPrimary
  3. setQueryAddToBatch set mQueryExecute = False if setQueryAddToBatch = True
  4. setQueryExecute set mQueryAddToBatch= False if mQueryExecute = True
  5. If mQueryExecute Then Execute -> If mQueryExecute Then ExecNonQuery
  6. Execute -> ExecNonQuery in Execute2
  7. ORMTable added Last2 As Map
  8. Rename FirstPick -> First3
  9. Rename setWhere -> Conditions
  10. Rename LogQueryWithArg -> LogQuery4
  11. setLimit
  12. Create
  13. Create2
  14. ExecuteBatchAsync
  15. Query
  16. Open
  17. Save
  18. Save3
  19. Delete
  20. Drop
  21. Destroy
  22. SoftDelete
  23. WhereParam
  24. WhereParams
  25. Join Table now is a function instead of property (breaking change)
    Join (Target As String, Statements As String, Modifier As String)
    B4X: Copy 
    'DB.Join = DB.CreateJoin("tbl_categories c", "p.category_id = c.id", "")
DB.Join("tbl_categories c", "p.category_id = c.id", "")
  26. Initialize object (no execute)
    B4X: Copy 
    DB.Initialize
DB.DbType = DB.SQLITE
DB.QueryExecute = False
DB.Table = "categories"
Log(DB.Statement)

What's Removed​

  1. ORMFilter Type
  2. ORMJoin Type
  3. setJoin
  4. setWhere
  5. CreateFilter
  6. CreateJoin
  7. LogQueryWithArg

Other Updates​

  1. Added comments
  2. Variables Casing
  3. Variables Rename
  4. Remove unnecessary symbols
  5. Added MiniORMUtilsLib project for non-ui app tests
  6. Update README
 
Version: 4.30

What's Changed​

  1. Fixed bug in Query sub returning previous data due to error not cleared
 
Last edited:
Version: 5.00

This is a breaking changes!

What's Changed​

  1. Change project license to MIT.
  2. Rework Create sub to handle both map-based column definitions and string column lists
  3. Rename internal fields mType -> mDbType
  4. Rename InitializeSQLite -> CreateSQLite
  5. Pluralized unique/foreign/constraint fields
  6. Added Constraint field to ORMColumn
  7. Added ExecuteScalar sub
  8. Added ExecuteScalar2 sub
  9. Added Map2Column sub
  10. Updated CreateColumn sub
  11. Updated ExecQuery sub
  12. Updated Exist sub
  13. Updated Find sub
  14. Updated Initialize sub
  15. Updated WhereParams sub
  16. Updated Query sub
  17. Updated Scalar sub
  18. Updated getConditions sub
  19. Updated setConditions sub
  20. Updated getStatement sub
  21. Updated setPrimary sub
  22. Updated setForeign sub
  23. Updated References sub
  24. Updated Insert2 -> setInserts
  25. Updated Join -> setJoin
  26. Updated Save2 -> setSave2
  27. Updated Save3 -> setSave3
  28. Updated Scalar2 -> setScalars
  29. Updated Query2 -> setQuery2
  30. Updated Constraint -> setConstraint
  31. Updated Unique -> setUnique
  32. Removed CreateColumn2 sub
  33. Moved default values from Class_Globals to Initialize sub
  34. Deprecated ordered map subs: First2, Last2, Results2
  35. Removed deprecated subs: LogQueryWithArg
 
Version: 5.10

What's Changed​

  1. New Object property
  2. New Union sub
  3. New QueryRaw property (write only)
  4. New BuildColumns sub (private)
  5. New ClearColumns sub (private)
  6. New ClearJoins sub (private)
  7. Updated ClearConditions
  8. Updated Create sub
  9. Updated Query sub
  10. Updated Insert sub
  11. Updated Save sub
  12. Updated setSave3 sub
  13. Updated Drop sub
  14. Updated Destroy sub
  15. Updated getStatement property sub
  16. Updated ExecuteScalar sub
  17. Updated ExecuteScalar2 sub
  18. Updated LogQuery sub
  19. Updated LogQuery2 sub
  20. Updated LogQuery3 sub
  21. Updated LogQuery4 sub
  22. Updated setJoin sub (DB.Join)
  23. Added getJoins sub

Version: 5.20

What's Changed​

  1. Renamed ORMSettings to MiniORMSettings
  2. Updated setConditions sub
  3. Updated setParameters sub
  4. Updated LogQuery sub
  5. Updated LogQuery2 sub
  6. Updated LogQuery3 sub
  7. Updated LogQuery4 sub

Version: 5.30

What's Changed​

  1. Renamed MiniORMSettings.DriverClass to MiniORMSettings.Driver
  2. Updated ExistAsync sub
  3. Minor bug fixes
 
You must log in or register to reply here.

Similar Threads

aeric
  • Article Article
Share My Creation [Project Template] [B4X] MiniORM
Replies
6
Views
7K
aeric
aeric
Mashiane
Mashy Teaches WebApp/Website Development with BANanoVuetifyAD3 - The New Series
7 8 9
Replies
164
Views
124K
bridge1
B
Back
Top