Share My Creation Web API Template

Web API

Description: A boilerplate for creating CRUD based Web API.

web-api-help.png


Depends on following libraries: ByteConverter, JavaObject, jServer, Json, jSQL
1630138331344.png


Features:
  1. CRUD based - REST-API style (GET, POST, PUT, DELETE)
  2. Front-end (HTML, CSS, JS, Bootstrap)
  3. Support MySQL and SQLite database (Can be modified for MS SQL or SQL Express)
  4. Separate SQL queries file (queries-mysql.ini and queries-sqlite.ini)
  5. Sample database auto-generated for first run (Category and Product tables with dummy data)
  6. Versioning (using ROOT_PATH in config.ini, set as "/" if you don't want versioning)
  7. Auto generated documentation with API test.
How to use:
  1. Copy the "Web API.b4xtemplate" file into B4J platform Additional folder.
  2. Open B4J and create a new project with "Web API" template. Give your project any name you like, for e.g. WebAPI
  3. Run the project in Debug or Release mode. You will see something like this in the Logs:
    B4X:
    Web API server (version = 1.06) is running on port 19800
    Open the following URL from your web browser
    http://127.0.0.1:19800/v1/
  4. Copy the URL showed in Logs and open it using your web browser.

5. To connect to MySQL server, go to Objects folder and open "config.ini". Comment the SQLite section and uncomment the MySQL section.​
1630137963547.png
6. Edit the root password at line #42 (second last line). Save the file.​
7. In B4J project, comment the line '#AdditionalJar: sqlite-jdbc-3.36.0.2 and uncomment the line #AdditionalJar: mysql-connector-java-5.1.49-bin.​
Make sure you are using the correct version of connector.​
B4X:
#Region AdditionalJar
' MySQL connector
#AdditionalJar: mysql-connector-java-5.1.49-bin
' SQLite connector
'#AdditionalJar: sqlite-jdbc-3.36.0.2
#End Region
8. Follow step #3 above.​

Screenshot:
web-api.png


Client App (B4X):
B4A.png
. .
B4i.png
. .
B4J.png


Live demo: https://api.puterise.com:19900

Video:

Comments: I try to make this template as compact as possible (remove user table and email function) but it is still big. If you don't like some features, just remove them. 😄

Updates:
19/10/2021 - v1.11
- Better use of Connection Pool for MySQL (do not close pool)
- Change DataUtils module to DataConnector class
- Clean up some redundant codes
8/10/2021
- WebAPI Client - v1.02
- - Some bugs fix and UI improvements
24/9/2021 - v1.10
- Auto generated documentation/help file based on handler classes (Click on the Question mark icon on top right corner to access the help file)
6/9/2021
- WebAPI Client - v1.01
- - Some bugs fix and UI improvements
4/9/2021 - v1.09
- Fixed PutProductByCategoryAndId to update Product as different Category.
- WebAPI Client (B4X) - v1.00
2/9/2021 - v1.08
- Added ConfigureCORS for ajax request.
1/9/2021 - v1.07
- Changes in Utility module (ReturnSuccess and ReturnSuccess2) require Status Code.
- Changes in ProductHandler (PostCategory and PostProductByCategory) return new record as result with Status Code 201.
27/8/2021 - v1.06
- Support for SQLite database
 

Attachments

  • ajax-test.zip
    30.2 KB · Views: 60
  • Web API 1.09.zip
    454 KB · Views: 78
  • WebAPIClient 1.01.zip
    442.7 KB · Views: 78
  • Web API.b4xtemplate
    462.9 KB · Views: 33
  • WebAPIClient 1.02.zip
    443.3 KB · Views: 25
  • Web API 1.11.zip
    459.2 KB · Views: 1
Last edited:

ilan

Expert
Licensed User
looks really nice (and complicated 😁)

i do get an error that a lib is missing:

1629969313888.png


i have some questions to the code but i will start with only 1,

i see that you are using some kind of template variables in your html files. i cannot find where you are updating them except of 'BuildView' function.

what function is responsible for updating the template vars with real data?

thank you
 

ilan

Expert
Licensed User
ok found it, its inside the config.ini file :)

those 2 functions:
B4X:
Public Sub BuildHtml (strHTML As String, Settings As Map) As String
    ' Replace $KEY$ tag with new content from Map
    strHTML = WebUtils.ReplaceMap(strHTML, Settings)
    Return strHTML
End Sub

Public Sub BuildView (strHTML As String, View As String) As String
    ' Replace @VIEW@ tag with new content
    strHTML = strHTML.Replace("@VIEW@", View)
    Return strHTML
End Sub

calls the function webutils:

B4X:
Public Sub ReplaceMap(Base As String, Replacements As Map) As String
    Dim pattern As StringBuilder
    pattern.Initialize
    For Each k As String In Replacements.Keys
        If pattern.Length > 0 Then pattern.Append("|")
        pattern.Append("\$").Append(k).Append("\$")
    Next
    Dim m As Matcher = Regex.Matcher(pattern.ToString, Base)
    Dim result As StringBuilder
    result.Initialize
    Dim lastIndex As Int
    Do While m.Find
        result.Append(Base.SubString2(lastIndex, m.GetStart(0)))
        Dim replace As String = Replacements.Get(m.Match.SubString2(1, m.Match.Length - 1))
        If m.Match.ToLowerCase.StartsWith("$h_") Then replace = EscapeHtml(replace)
        result.Append(replace)
        lastIndex = m.GetEnd(0)
    Loop
    If lastIndex < Base.Length Then result.Append(Base.SubString(lastIndex))
    Return result.ToString
End Sub
 

aeric

Expert
Licensed User
i do get an error that a lib is missing:

those 2 functions:
Yes. The BuildHtml and BuildView functions are responsible to render the HTML page. BuildView is used to merge the index.html with the main.html if you have other page other than index.html where main.html is the parent page.
Note that WebUtils file is provided by Erel.

As you can see, I am using config.ini and queries.ini and the app loads these 2 files when started.
I have separated these files so it is easy to customize your own values without touching the code.
 

aeric

Expert
Licensed User
I found a bug. Will upload an update. I only done renumeration for GET inside ProcessRequest but forgot for other methods. 😅
 

aeric

Expert
Licensed User
ProductHandler.bas is updated in the template. Please download the file again. Sorry for any inconvenience.
 

ilan

Expert
Licensed User
i cannot test it because i need to run a server with MySQL for that. i will update the code to allow SQLite DB for testing.
i do it a lot in my project where i have conditional symbols for SQLite and MySQL to be able to test it anywhere. :)
 

aeric

Expert
Licensed User
i cannot test it because i need to run a server with MySQL for that. i will update the code to allow SQLite DB for testing.
i do it a lot in my project where i have conditional symbols for SQLite and MySQL to be able to test it anywhere. :)
I believe it is easy to achieve. :)

Edit: I can't imagine using SQLite for the Web API server backend. I know it is possible but I like MySQL and Adminer in Laragon.
 

ilan

Expert
Licensed User
I believe it is easy to achieve. :)

yes, should be.
it would be a great addition to b4j to have a place where people could upload their templates so others can download them. if not inside b4x ide it could be an external app or website.
 

aeric

Expert
Licensed User
There is an issue in MySQL (queries-mysql.ini) in version 1.06
The search queries should be same as SQLite script. The MySQL script was for version 1.05 and it is now fixed.
The issue is at FORMAT(P.product_price, 2) where the currency format is now handled by B4X.
B4X:
Else If res.GetColumnName(i) = "ee" Then
    Map2.Put(res.GetColumnName(i), NumberFormat2(res.GetDouble2(i), 1, 2, 2, True))
# SEARCH
SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_ONEWORD_ORDERED=SELECT P.id AS aa, \
P.product_code AS bb, C.`category_name` AS cc, P.product_name AS dd,
FORMAT(P.product_price, 2) AS ee \
FROM `tbl_products` P JOIN `tbl_category` C ON P.`category_id` = C.`id` \
WHERE C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ?

SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_TWOWORDS_ORDERED=SELECT P.id AS aa, \
P.product_code AS bb, C.`category_name` AS cc, P.product_name AS dd,
FORMAT(P.product_price, 2) AS ee \
FROM `tbl_products` P JOIN `tbl_category` C ON P.`category_id` = C.`id` \
WHERE C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ? \
OR C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ?
 

swamisantosh

Member
Licensed User
http://127.0.0.1:19800/v1/category/1
{
"r": [],
"s": "error",
"e": "Error Execute Query",
"m": null
}

d:\pyhoon\WebApi\Objects
i removed webapi.db file the above issues got resolved.
using the debuging, what was issue, i found that, if the sqlite database it does not recreate.
 
Last edited:

aeric

Expert
Licensed User
http://127.0.0.1:19800/v1/category/1
{
"r": [],
"s": "error",
"e": "Error Execute Query",
"m": null
}

d:\pyhoon\WebApi\Objects
i removed webapi.db file the above issues got resolved.
using the debuging, what was issue, i found that, if the sqlite database it does not recreate.
I am not sure how deleting the sqlite file would solve the issue. The error was handled by try-catch and you can find the exact problem (LastException) by reading the logs while debugging. As I stated above, I have done a test to send POST request using the payload with invalid comma sign which caused the RequestData read invalid data. Maybe I should have validated the Map initialized from the RequestData. In you case, I am not sure how you produce the error. Since you already able to resolved the issue then it is good and I assume everything is good to go.

Until we found a new bug, I hope the current version is a good template to start your own project. I encourage anyone to modify this template and come out an improved version. For myself, I would add back the user token, API key and SMTP features for my own use. I am not sharing it because a template should be minimalist and serve as a starting point for own customization.

Thanks everyone for the likes and testing this template. 🙏🏻

Don’t worry, I will keep answering your questions if you have one about this template. 😆
 
Top