B4J Library [B4X] MiniORMUtils

MiniORMUtils
Version: 2.62

---

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).

Project Template:
[B4X] [Project Template] MiniORM

Examples:
Create ConnectionInfo object

Dim Info As ConnectionInfo
Info.Initialize
info.DBType = "SQLite"
Info.DBFile = "data.db"

Create ORMConnector object

Dim Conn As ORMConnector
Conn.Initialize(Info)

Check if database exist

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

Create database

Private Sub CreateDatabase
    Dim Success As Boolean = Conn.DBCreate
    If Success = False Then
        Log("Database creation failed!")
        Return
    End If
    ' The rest of code for creating tables
End Sub

Initialize MiniORM object

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

Create table

DB.Table = "tbl_category"
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_name")))
DB.Create

Insert rows

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

Execute NonQuery Batch

Wait For (DB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
End If
DB.Close

Select All Rows

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

Update row

DB.Table = "tbl_products"
DB.Columns = Array As String("category_id", "product_code", "product_name", "product_price")
DB.Id = 2
DB.Save2(Array As String(Category_Id, Product_Code, Product_Name, Product_Price))

More examples on GitHub README page
https://github.com/pyhoon/MiniORMUtils-B4X

Spoiler: Older version

[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

 

[aeric]

Migration from version 1.x

1. DatabaseConnector is now ORMConnector
2. ORMConnector requires a ConnectionInfo object to pass as a parameter for initialization.
   
   Dim Info As ConnectionInfo
   Info.Initialize
   Info.DBType = "SQLite"
   Info.DBFile = "test.db"
3. Then we can initialize ORMConnector as follow:
   
   Dim Con As ORMConnector
   Con.Initialize(Info)
4. To initialize MiniORM object, we need to specify the database type as first parameter and an SQL object as second parameter.
5. We can use DBType and DBOpen from ORMConnector object.
   
   Dim DB As MiniORM
   DB.Initialize(DBType, DBOpen)
6. Some methods name have been renamed.
7. CreateORMColumn and CreateORMColumn2 renamed to CreateColumn and CreateColumn2.
8. CreateORMFilter renamed to CreateFilter.
9. CreateORMJoin renamed to CreateJoin.
10. setRawSQL changed to setStatement
11. ToString changed to getStatement
12. Need to specify journal mode for SQLite when use in server. Default is "DELETE".
    
    Info.JournalMode = "WAL"
13. When using MySQL, connect to main schema using InitSchema before calling DBExist2 or DBCreate.
14. AddQuery replaced by AddNonQueryToBatch
15. BlnAddAfterCreate and BlnAddAfterInsert replaced by setQueryAddToBatch
16. BlnExecuteAfterCreate and BlnExecuteAfterInsert replaced by setQueryExecute

 

[aeric]

Version: 2.10
Size: 14KB

What's New

1. Fixed bug in setParameters sub for B4J
2. Replaced Where3 sub with WhereParam and WhereParams
3. Show logs when ShowExtraLogs = True with LogQuery, LogQuery2 and LogQueryWithArg

 

[aeric]

Version: 2.11
Size: 14KB

What's New

1. Update WhereParam sub to accept mCondition instead of mColumn as first parameter to avoid confusion with WhereParams

 

[aeric]

Version: 2.20
Size: 14KB

What's New

1. Fixed bugs for B4A and B4i

 

[aeric]

Version: 2.21
Size: 14KB

What's New

1. Updated AddParameters sub
2. Updated ORMConnector Initialize sub for B4A and B4i
3. Removed ByteConverter from manifest for B4A and B4i
4. Removed Merge sub
5. Fixed Find sub
6. Fixed Find2 sub
7. Fixed setId sub
8. Updated Query sub
9. Added setShowDBUtilsJson sub for B4A and B4i

 

[aeric]

Version: 2.30
Size: 14KB

What's New

1. Added View property
2. Added ResetParameters sub
3. Added IfNull sub
4. Added Create2 sub
5. Added setSelect2 sub
6. Added SelectFromView sub
7. Added SelectFromTable sub
8. Added ViewExists sub
9. Added ViewExists2 sub
10. Updated setSelect sub

Thanks to Support Ticketing System.

Many new functions in DatabaseBuilder class such as IfNull, Create2, setSelect2, SelectFromView, ViewExists and ViewExists2.

 

[aeric]

Version: 2.40
Size: 14KB

What's New

1. Added Save3 sub
2. Updated Create sub
3. Updated ORMConnector class

 

[Mashiane]

Hi, a kind request

In the readme, please include examples of doing executing conditions for updates & deletes

1. Update Where e.g. UPDATE Users Where ...
2. Delete Where e.g. DELETE Users Where...

Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution?

Thanks in advance.

PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?

 

[aeric]

Hi, a kind request

In the readme, please include examples of doing executing conditions for updates & deletes

1. Update Where e.g. UPDATE Users Where ...
2. Delete Where e.g. DELETE Users Where...

Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution?

Thanks in advance.

PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?

Thanks for your suggestion. I will try to add more tutorials when I am free.
For any new questions, please post new threads.

 

[aeric]

Version: 2.50
Size: 14KB

What's New

1. Changed MiniORM class Initialize sub parameters switch order, SQL object is now second parameter which can be Null
2. Renamed Engine or DBEngine property to DBType
3. Added Error property
4. Updated ORMConnector class with DBType and Error property

This is a breaking update.

Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)

 

[aeric]

Version: 2.51
Size: 14KB

What's New

1. updated setView

 

[amorosik]

The project seems very interesting
But it is really difficult to do something without following some working examples, at least for me it is impossible
I have tried several times to spend time to understand how it works, but so far I have not succeeded
It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it

 

[aeric]

The project seems very interesting
But it is really difficult to do something without following some working examples, at least for me it is impossible
I have tried several times to spend time to understand how it works, but so far I have not succeeded
It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it

Check the B4X project template (B4A, B4i, B4J)
[B4X] [Project Template] MiniORM

There is also tutorial for using this library in server solution
[Tutorial] Using MiniORMUtils in Web API Server v3

Edit: Some examples added to first post

 

[aeric]

Version: 2.60
Size: 14KB

What's New

1. Fix unsupported function Initialized in B4i
2. Update DBOpen sub

 

[aeric]

Version: 2.61
Size: 14KB

What's New

1. Fix Close RS in Query sub (B4i)

Version: 2.62
Size: 14KB

What's New

1. Fix Close RS in Query sub (B4A)