B4J Tutorial Using MiniORMUtils in Web API Server v3

aeric · Nov 2, 2024

Using MiniORMUtils inside Server Handler

Referring to the generated code using Code Snippets in the Part #5: Add Boilerplate Code to Handler Using Code Snippets tutorial.

On top of the code, we see there are some variables declared as Private.

B4X:

Sub Class_Globals
        Private Request As ServletRequest
        Private Response As ServletResponse
        Private HRM As HttpResponseMessage
        Private DB As MiniORM
        Private Method As String
        Private Elements() As String
        Private ElementId As Int
End Sub

Request and Response are passing from Handle sub so we can use them through out the entire handler class.

Code:

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    ...
End Sub

We declared a variable HRM as HttpResponseMessage for generating the JSON output or response.
B4X:
```
Private HRM As HttpResponseMessage
```
HttpResponseMessage is a custom type defined in WebApiUtils library.
We declared DB as MiniORM for working with our database.
B4X:
```
Private DB As MiniORM
```
MiniORM is a class from MiniORMUtils library.
We declared a String array Elements() for storing the elements from the request URL.
B4X:
```
Private Elements() As String
```
Last variable is ElementId for storing the Id element.
B4X:
```
Private ElementId As Int
```

Initialization

We use the Initialize sub to initialize 2 variables, HRM and DB from the public variables we declared in Main module.

B4X:

Public Sub Initialize
    HRM.Initialize
    HRM.SimpleResponse = Main.Config.SimpleResponse
    DB.Initialize(Main.DBOpen, Main.DBEngine)
End Sub

Handle sub

The Handle sub look like following:

B4X:

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    Method = Request.Method.ToUpperCase
    Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
    Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
    Select Method
        Case "GET"
            ...
        Case "POST"
            ...
        Case "PUT"
            ...
        Case "DELETE"
            ...
        Case Else
            Log("Unsupported method: " & Method)
            ReturnMethodNotAllow
            Return
    End Select
    ReturnBadRequest
End Sub

The Method variable read from the Request variable will be use for further routing.
We can use GetUriElements method of WebApiUtils to fill the FullElements array.
B4X:
```
Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
```
Instead of counting the elements from 0 for the root element from the URL, what we are interested are the elements after the /api (for Api Handler).
Therefore we use CropElements method to "slice" or crop out the first elements.
B4X:
```
Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
```
From this point, we can think that the first Element e.g Id is accessed using the index 0.
B4X:
```
ElementId = Elements(0)
```
You can see the logic inside ElementMatch sub.
Next, we will use Select-Case to control the flow of the request falls to the right sub.

Control Flow

Base on the Select-Case of variable Method, we can route to 2 API end points in this example.

The first end point is without Id where we pass an empty string pattern to the ElementMatch sub.
e.g URL: http://127.0.0.1:8080/api/users

B4X:

Select Method
    Case "GET"
        If ElementMatch("") Then
            GetUsers
            Return
        End If
        ...
    ...
End Select

If ElementMatch returns True then we can proceed to call GetUsers sub to return all the rows inside users table.
The second end point is with an Id where we pass the "id" pattern to the ElementMatch sub.
e.g URL: http://127.0.0.1:8080/api/users/2
B4X:
```
If ElementMatch("id") Then
    GetUserById(ElementId)
    Return
End If
```
Important: For every match, insert Return keyword before the End If keywords like the examples above.
Missing this keyword will cause the response Api returns the Bad Request output too. In other word, a malformed JSON will be returned.
The same steps apply to other Methods such as POST, PUT and DELETE depending on the Endpoint pattern you want to create.
We also check if there are request by other Method (e.g PATCH) and we don't want to allow such request to proceed.
B4X:
```
Case Else
    Log("Unsupported method: " & Method)
    ReturnMethodNotAllow
    Return
```
This is also the case where you want to create an Api that only allow POST then the remaining request can be consider under the Case Else flow.
Any route that does not match the desired pattern will fall under Bad Request and handled by ReturnBadRequest sub.

Commonly Use Subs

We can shorten some of the long codes by using extra subs.

This is an example:

B4X:

Private Sub ReturnApiResponse
    WebApiUtils.ReturnHttpResponse(HRM, Response)
End Sub

So instead of writing the full code, we can just use one word in our code.
You can see examples of using ReturnBadRequest, ReturnMethodNotAllow and ReturnErrorUnprocessableEntity.

aeric · Nov 3, 2024

Examples

GetUsers

B4X:

Private Sub GetUsers
    ' #Desc = Read all Users
    DB.Table = "tbl_users"
    DB.Query
    HRM.ResponseCode = 200
    HRM.ResponseData = DB.Results
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched an empty pattern with GET method.
It is use to get all the rows in users table.
Important: In order for HelpHandler to read this handler to generate the API Documentation, the name of the sub must be started with "Get".
The first line is a comment. We use #Desc to tell HelpHandler class that the text after the equals sign is the description of the API that will appear in the documentation.
Similar to how we create a table, we need to pass the table name using the Table property when we want to query rows from a table.
B4X:
```
DB.Table = "tbl_users"
```
Next, we just need to call Query method.
B4X:
```
DB.Query
```
We want to return a status code 200 to the response.
B4X:
```
HRM.ResponseCode = 200
```
Since we are expecting the rows are returned as a JSON array or list, we pass the query result DB.Results (which is a list) to HRM.ResponseData.
B4X:
```
HRM.ResponseData = DB.Results
```
Then we call ReturnApiResponse to write the JSON output to the server response.
Finally we close the MiniORM object which will close the SQL resultset object especially for MySQL database.
B4X:
```
DB.Close
```

GetUserById

B4X:

Private Sub GetUserById (Id As Int)
    ' #Desc = Read one User by id
    ' #Elements = [":id"]
    DB.Table = "tbl_users"
    DB.Find(Id)
    If DB.Found Then
        HRM.ResponseCode = 200
        HRM.ResponseObject = DB.First
    Else
        HRM.ResponseCode = 404
        HRM.ResponseError = "User not found"
    End If
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched an id pattern with GET method.
It is use to get a single row in users table if the id is existed.
Adding #Elements comment and parameter with type (e.g Id As Int) for the sub dictates how the documentation will appear.
Similar to the GetUsers sub, we pass the table name to MiniORM.
B4X:
```
DB.Table = "tbl_users"
```
To query for a single row from users table by id, we can use the Find method.
B4X:
```
DB.Find(Id)
```

If a row is found then we return the row as a single object.

B4X:

If DB.Found Then
    HRM.ResponseCode = 200
    HRM.ResponseObject = DB.First

We use the First method to ensure that only one object is read.
WebApiUtils through ReturnHttpResponse method will determine which format to return as JSON output based on the SimpleResponse settings.
It means the return value can be an array or an object.
If the id is not existed, we want to return a message to the API.
We assign status code 404 to indicate the item is not found.
B4X:
```
Else
    HRM.ResponseCode = 404
    HRM.ResponseError = "User not found"
```
Finally, we write the JSON output to server response and close the MiniORM object.
B4X:
```
ReturnApiResponse
DB.Close
```

PostUser

B4X:

Private Sub PostUser
    ' #Desc = Add a new User
    ' #Body = {<br>&nbsp;"name": "User_name"<br>}
    Dim data As Map = WebApiUtils.RequestData(Request)
    If Not(data.IsInitialized) Then
        HRM.ResponseCode = 400
        HRM.ResponseError = "Invalid json object"
        ReturnApiResponse
        Return
    End If

    ' Check whether required keys are provided
    Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
    For Each requiredkey As String In RequiredKeys
        If data.ContainsKey(requiredkey) = False Then
            HRM.ResponseCode = 400
            HRM.ResponseError = $"Key '${requiredkey}' not found"$
            ReturnApiResponse
            Return
        End If
    Next
   
    ' Check conflict User name
    DB.Table = "tbl_users"
    DB.Where = Array("User_name = ?")
    DB.Parameters = Array As String(data.Get("user_name"))
    DB.Query
    If DB.Found Then
        HRM.ResponseCode = 409
        HRM.ResponseError = "User already exist"
        ReturnApiResponse
        DB.Close
        Return
    End If
   
    ' Insert new row
    DB.Reset
    DB.Columns = Array("User_name", "created_date")
    DB.Parameters = Array(data.Get("User_name"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))
    DB.Save
   
    ' Retrieve new row
    HRM.ResponseCode = 201
    HRM.ResponseObject = DB.First
    HRM.ResponseMessage = "User created successfully"
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched the empty pattern with POST method.
It is use to create a new resource in this case the users table.
Meaning it will execute an INSERT query to the database using MiniORM.
Notice that we use a comment ' #Body to display the sample body. (Similar for PUT end point)

Take note that we only can write the comment in a single line.
For POST request, we need to read the data submitted in the POST body.
B4X:
```
Dim data As Map = WebApiUtils.RequestData(Request)
```
If the submitted payload is a valid JSON string, it can be parse using RequestData method of WebApiUtils and assign the result into a Map.
B4X:
```
If Not(data.IsInitialized) Then
    HRM.ResponseCode = 400
    HRM.ResponseError = "Invalid json object"
    ReturnApiResponse
    Return
End If
```
If the string is invalid, we return a result with status code 400 and exit the process.

We also want to check if all the required keys are provided.

B4X:

' Check whether required keys are provided
Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
For Each requiredkey As String In RequiredKeys
    If data.ContainsKey(requiredkey) = False Then
        HRM.ResponseCode = 400
        HRM.ResponseError = $"Key '${requiredkey}' not found"$
        ReturnApiResponse
        Return
    End If
Next

For e.g we can replace key1 with user_name, key2 with email and key3 with password.

Then we check for existing records to avoid duplicate.

B4X:

' Check conflict User name
DB.Table = "tbl_users"
DB.Where = Array("User_name = ?")
DB.Parameters = Array As String(data.Get("User_name"))
DB.Query
If DB.Found Then
    HRM.ResponseCode = 409
    HRM.ResponseError = "User already exist"
    ReturnApiResponse
    DB.Close
    Return
End If

In the case of users, it is more useful if we check for unique emails. We can replace the above code to check for emails.
B4X:
```
' Check conflict emails
DB.Table = "tbl_users"
'DB.Where = Array("user_email = ?")
'DB.Parameters = Array As String(data.Get("user_email"))
DB.WhereValue(Array("user_email = ?"), Array(data.Get("user_email")))
DB.Query
```
There is a new shortcut method WhereValue in MiniORMUtils to replace method Where and Parameters.
Next, we can insert the data.
Notice we use method Reset to ensure any conditions or parameters are cleared before we reuse the MiniORM object.
B4X:
```
' Insert new row
DB.Reset
```
We specify the column names in order inside an array and assign to Columns property of MiniORM.
B4X:
```
DB.Columns = Array("user_name", "user_email", "user_password", "created_date")
```

Next, we specify the values associated with the columns into Parameters property as an array of objects.

B4X:

DB.Parameters = Array(data.Get("user_name"), data.Get("user_email"), data.Get("user_password"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))

Then we call the method Save. This will create an INSERT command and MiniORM will execute it.
B4X:
```
DB.Save
```
If the command executed successfully, we return a result with status code 201 to indicate a new resource is created.
We pass the first object from MiniORM which is queried based on the newly created id.
We can return the object by assigning it to ResponseObject.
We can also assign a message using the ResponseMessage property.
Finally, we write the JSON output to server response and close the MiniORM object.
B4X:
```
ReturnApiResponse
DB.Close
```
Take note of the key names inside the data map. For e.g use the lowercase data.Get("user_name") instead of data.Get("User_name").

aeric · Nov 4, 2024

Adding New Table

Often time we need to add a new table after the database is created.
To do it, we need to check whether a table is exist then we create the table.
For e.g we want to add a new table tbl_users.

TableExists sub

Let's create a sub for checking the table in Main module.
We will pass the table name into this sub.

The sub will return True if the table exist or otherwise False.

B4X:

Private Sub TableExists (TableName As String) As Boolean
    Dim MDB As MiniORM
    MDB.Initialize(DBOpen, DBEngine)
    #If MySQL
    Return MDB.TableExists2(TableName, ctx.Get("DbName"))
    #Else
    Return MDB.TableExists(TableName)
    #End If
End Sub

Create Table

We will add the code for creating table inside CreateConnection sub.

B4X:

If DBFound Then
    LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
    ' Add code for checking table exist
    ...
Else
    LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

We use the sub we have created previously to check for the table name.
B4X:
```
If TableExists("tbl_users") = False Then
```

Then we can use MiniORM to create the table.

B4X:

Dim MDB As MiniORM
MDB.Initialize(DBOpen, DBEngine)
MDB.UseTimestamps = True
MDB.Table = "tbl_users"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
MDB.Create
MDB.Execute
LogColor($"Table tbl_users is created!"$, COLOR_BLUE)

After calling the Create method, we call Execute to execute the SQL command.
The new table will be created when we run the project again.

The complete code is as following:

B4X:

Private Sub CreateConnection
    Try
        LogColor("Checking database...", COLOR_BLUE)
        #If MySQL
        Dim DBType As String = "MySQL"
        #Else
        Dim DBType As String = "SQLite"
        #End If
        If cnn.DBType.EqualsIgnoreCase(DBType) = False Then
            ShowBuildConfigurationNotMatch(cnn.DBType)
            Return
        End If
        DBConnector.Initialize(cnn)
        #If MySQL
        Wait For (DBConnector.DBExist2) Complete (DBFound As Boolean)
        #Else
        Dim DBFound As Boolean = DBConnector.DBExist
        #End If
        If DBFound Then
            LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
            ' Check table exist
            If TableExists("tbl_users") = False Then
                Dim MDB As MiniORM
                MDB.Initialize(DBOpen, DBEngine)
                MDB.UseTimestamps = True
                MDB.Table = "tbl_users"
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
                MDB.Create
                MDB.Execute
                LogColor($"Table tbl_users is created!"$, COLOR_BLUE)
            End If
        Else
            LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
            CreateDatabase
        End If
    Catch
        LogError(LastException.Message)
        LogColor("Error checking database!", COLOR_RED)
        Log("Application is terminated.")
        ExitApplication
    End Try
End Sub

This is the end of tutorial. Thanks for taking your time to read it.

aeric · Jan 9, 2025

Creating Nested JSON Response

Sometimes we want to return a more complex JSON response.
Let say we have 2 categories and each category has its own products associated with it by category_id.

We want to return a JSON like the following:

Code:

[
  {
    "category_name": "Hardware",
    "id": 1,
    "products": [
      {
        "id": 2,
        "product_name": "Hammer"
      }
    ]
  },
  {
    "category_name": "Toys",
    "id": 2,
    "products": [
      {
        "id": 1,
        "product_name": "Teddy Bear"
      },
      {
        "id": 3,
        "product_name": "Optimus Prime"
      }
    ]
  }
]

First we need to add a new endpoint e.g http://127.0.0.1:8080/api/categories/products.

In CategoriesApiHandler class, add a new variable ElementKey in Class_Globals to support string element.

Code:

Sub Class_Globals
    ...
    Private ElementId As Int        ' numeric key
    Private ElementKey As String    ' string key
End Sub

In Handle sub, add a new check for ElementMatch.

Code:

Sub Handle (req As ServletRequest, resp As ServletResponse)
    ...
    Select Method
        Case "GET"
            ...
            If ElementMatch("id") Then
                GetCategoryById(ElementId)
                Return
            End If
            If ElementMatch("key") Then
                If ElementKey = "products" Then
                    GetCategoriesAndProducts
                    Return
                End If
            End If
        ...
    End Select
    ReturnBadRequest
End Sub

We also need to update the code for ElementMatch. Add the highlighted code.

Code:

Private Sub ElementMatch (Pattern As String) As Boolean
    Select Pattern
        Case ""
           ...
        Case "id"
           ...
        Case "key"
            If Elements.Length = 1 Then
                If IsNumber(Elements(0)) = False Then
                    ElementKey = Elements(0)
                    Return True
                End If
            End If
    End Select
    Return False
End Sub

Now add a new sub call GetCategoriesAndProducts.

B4X:

Private Sub GetCategoriesAndProducts
    ' #Desc = Get all Categories with their Products
    ' #Elements = ["products"]
    DB.Table = "tbl_categories"
    DB.Select = Array("id", "category_name")
    DB.Query
    Dim Categories As List = DB.Results
    For Each category As Map In Categories
        DB.Table = "tbl_products"
        DB.Select = Array("id", "product_name")
        DB.WhereValue(Array("category_id = ?"), Array(category.Get("id")))
        DB.Query
        Dim products As List = DB.Results
        category.Put("products", products) ' add new key
    Next
    HRM.ResponseCode = 200
    HRM.ResponseData = Categories
    ReturnApiResponse
    DB.Close
End Sub

You can see we first query table "tbl_categories" for all categories. To simplify view, we only select column id and category_name.
We pass the DB.Results into Categories list.
Then we loop the list to get the item as category map.
We will need to get the id of each category and use it to query the products from "tbl_products" having the same category id.
Again to simplify view of this tutorial, we only select column id and product_name.
We pass the DB.Results into products list.
Then we add a new key "products" with the products list as its value to put back to the category map.
The list we are interested is Categories to pass to HRM.ResponseData
Finally call ReturnApiResponse and close the MiniORM object as usual.

That's it.

B4J Tutorial Using MiniORMUtils in Web API Server v3

aeric

Expert

aeric

Expert

aeric

Expert

aeric

Expert

Similar Threads