Share My Creation [Project Template] [Web] API Server

Web API Server
Version: 1.16
Description: Create Web API Server with CRUD functionalities


1681305482137.png


1681305567463.png


1681305621295.png


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


Features:
  1. CRUD based - RESTful Web API (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 Server (1.16).b4xtemplate" file into B4J Additional Libraries folder.
  2. Open B4J and create a new project with "Web API Server" 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.16) 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.​
1681306109357.png
6. Edit the root password at line #45 (second last line). Save the file.​
7. In B4J project, comment the line '#AdditionalJar: sqlite-jdbc-3.39.3.0 and uncomment the line #AdditionalJar: mysql-connector-java-8.0.30.​
Make sure you are using the correct version of connector.​
B4X:
#Region AdditionalJar
' MySQL connector
#AdditionalJar: mysql-connector-java-8.0.30
' SQLite connector
'#AdditionalJar: sqlite-jdbc-3.39.3.0
#End Region
8. Follow step #3 above.​

Note: "Web API Client.b4xtemplate" is also attached to demonstrate how to consume the Web API using B4A, B4i and B4J

About auto generated help file (version 1.10+)

https://www.b4x.com/android/forum/threads/web-api-template.133764/post-851114

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


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:
12/04/2023
- Web API Server - v1.16
- Similar to Web API Template 2.0, it is now less confusing by using #Desc and #Path for documentation
- Web API Client - v1.0.5 (compatible with Web API Server v1.16)

03/10/2022
- Web API Server - v1.15
- JS: Fixed bug point to wrong id for Category dropdown in Edit dialog
- JS: Remove comma in thousand for price textbox in Edit dialog
- Add a Home button in Category page

01/10/2022
- Web API Server - v1.14
- Manage Category in new page
- Dynamic populate Category dropdown in add/edit product dialog
- Improve JS scripts
- SQLite version point to sqlite-jdbc-3.39.3.0

27/08/2022
- Web API Client - v1.0.4 (compatible with Web API Server v1.13)

25/08/2022
- Web API Server - v1.13
- Home page with Add, Edit and Delete Product (using Bootstrap modal and jQuery Validate)
- Help or API documentation page improvements
- Many variables and SQL commands are renamed!
- SQLite version point to sqlite-jdbc-3.39.2.0
- Separate Category and Product handlers
- FindHandler added for more search endpoints
- Endpoints structure changed!
- Warning: This version is not compatible with Web API Client v1.0.3 and below

28/05/2022
- Web API - v1.12 (fixed a bug in HelpHandler.bas when reading commented line)
- Updated to use jServer v4.0
- Web API Client - v1.0.3 (Converted to B4XTemplate)

26/01/2022
- Create Publish.zip using #CustomBuildAction
- Create Publish.zip using Comment Link

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

08/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)

06/09/2021
- WebAPI Client - v1.01
- - Some bugs fix and UI improvements

04/09/2021 - v1.09
- Fixed PutProductByCategoryAndId to update Product as different Category.
- WebAPI Client (B4X) - v1.00

02/09/2021 - v1.08
- Added ConfigureCORS for ajax request.

01/09/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/08/2021 - v1.06
- Support for SQLite database
 

Attachments

  • ajax-test.zip
    30.2 KB · Views: 919
  • Web API Server (1.16).b4xtemplate
    481.3 KB · Views: 350
  • Web API Client (1.05).b4xtemplate
    443.9 KB · Views: 343
Last edited:

aeric

Expert
Licensed User
Longtime User
I don't know how excited you are to the coming new version. As for me, I am super excited.

Maybe no one is interested to what I want to share next but just allow me to express some of my emotional feelings. Through out the journey of this project, I learned a lot and I hope everyone is learning everyday. Try to be independent and not just rely completely to others help without really try it "enough". Recently I come across some friends of mine, who are asking for "quick" answers. Some questions, I already have the answers for them but I was reluctant to give the answers straight away. As a programmer/coder/developer, there is an important quality or skill we need to have that is "Google search". I believe the answers could be found if we have done more searches and willing to spend more time to work on it. Of course, forgive me if I am the hypocrite person if you saw me asking a "simple" question in this forum. I seldom ask question and when I asked, I have the objective that my questions will be useful for other members too. When recently my friends were asking for some questions regarding .NET project (of course these friends I am referring to are not active members from this forum), I was lazy to answer them. One reason is I am not interested on .NET as I am now more focus on B4X. I have already tired providing "free" answers and "spoon-feed" some people. What I am today is what some people who are not willing to spend the time and effort to achieve it. I may have spend 5x or 10 times the effort as theirs to get the answer I know today. I am now selfish to share. I wanted to be a mentor to guide anyone and I already give them some hints but they come back to me saying "not working. can you help me to take a look?". I would say, "sorry, I don't know when I am free to do it".

This is the most sophisticated B4X project I ever work on! When you see like 400 lines of code, but underneath or behind the scene, there are many deleted and cleaned up testing code that no one would ever see. Sad, but these code actually contribute to the final work.
In my work, I believe the principles of "D-R-Y" (Don't Repeat Yourself) and "K-I-S-S" (Keep It Simple, Stupid). This applies to this project where I tried to rewrite repeating code into single methods. When I was cleaning up the "commented" code, I found the following lines and I was laughed to myself.
B4X:
If index > -1 Then
    ' commented code
    ' commented code
    If index > -1 Then

    End If
End If
Some times, we made stupid mistakes. But the code works. It just unnecessary redundant code, don't blame ourselves.
However, I want to admit that I am not good in JavaScript or JQuery. I did look up on Google search how to implement things. So, don't ask me how the AJAX works, I have no idea how it works in this project. I also broke my principle as I stated above by repeating the Javascript code. I am sure there is one or more elegant ways to do it.

Lastly, I don't expect anyone would understand how the Help file page that can generated dynamically. As I mentioned above, I spent hours and weeks to implement the ideas inside my mind which I consider quite sophisticated for some, especially I already deleted all the testing Log "history". I used a lot of For loops to Log the items from Map and List. I used a lot of IndexOf, Contains, Replace and SubString methods. At the end. I am happy this version has this "smart" feature (Swagger-like Documentation). I also thought whether I should call this version 2 but end up I still let it as version 1.x and I make it compatible with older ways of returning response.

For example:
Old way:
If List1.Size = 0 Then
    Utility.ReturnError("Category Not Found", 404, Response)
Else
    Utility.ReturnSuccess2(List1, 200, Response)
End If

New way:
Dim HRM As HttpResponseMessage
HRM.Initialize
If List1.Size = 0 Then
    HRM.ResponseCode = 404
    HRM.ResponseError = "Category Not Found"
Else
    HRM.ResponseCode = 200
    HRM.ResponseData = List1
End If

Utility.ReturnHttpResponse( HRM, Response)

So, that's all I want to say for now about the changes. Stay tune for the release of version 1.10 and I hope you will like the "magic". ;)
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Why not take the API generation online too in this awesome project but then add flexibility? Im not sure if its already there, I will make time to study your awesome project well.

I recently came across this Web App Generator from flatlogic, https://flatlogic.com/generator

What picked my interest is that they have a Vuetify Template, so i watched. What blew my mind was the generated API, which was automated and uploaded to the server as soon as one creates their schema and generate the API. I didnt mind much about the UI generation, thats not new.

I was like is Aeric's Web API doing this already. I know it generated API code, but dynamically where you create your schema, perhaps change your mind here and there and regenerate the API based on conditions you indicate.

What if one could for any schema that they want to create, can do so dynamically? Just as an example.

1. Indicate the DB Name.
2. Indicate the Table Name
3. Capture the fields names and their types
4. Indicate the primary key (optional)
5. Indicate autoincrement key (optional)
6. Indicate whatever else is needed to generate the API.
4. Click a button the api is generated automatically.

Its an awesome tool and thanks for doing this. I need it and will have to test it with one of my projects.

Awesome and congratulations, I sent coffee money too for your awesome work ;)
 
Last edited:

aeric

Expert
Licensed User
Longtime User
Why not take the API generation online too in this awesome project but then add flexibility? Im not sure if its already there, I will make time to study your awesome project well.

I recently came across this Web App Generator from flatlogic, https://flatlogic.com/generator

What picked my interest is that they have a Vuetify Template, so i watched. What blew my mind was the generated API, which was automated and uploaded to the server as soon as one creates their schema and generate the API. I didnt mind much about the UI generation, thats not new.

I was like is Aeric's Web API doing this already. I know it generated API code, but dynamically where you create your schema, perhaps change your mind here and there and regenerate the API based on conditions you indicate.

What if one could for any schema that they want to create, can do so dynamically? Just as an example.

1. Indicate the DB Name.
2. Indicate the Table Name
3. Capture the fields names and their types
4. Indicate the primary key (optional)
5. Indicate autoincrement key (optional)
6. Indicate whatever else is needed to generate the API.
4. Click a button the api is generated automatically.

Its an awesome tool and thanks for doing this. I need it and will have to test it with one of my projects.

Awesome and congratulations.
I don't know how flexible it is. I am creating an API generator that powered by B4J server. It is similar with working with jRDC2 but I am using JSON instead of the client side RequestManager. Sometimes, we want to work with multiple join tables so I think this part is not straight forward if using wizard style generator. If you want, you can also replace the jQuery AJAX in my code and use Vue. I think it is not diificult if you are experience with Vue. And this project is open source where you can learn my coding style if you are curious. 😅
I will upload the project within 1 or 2 hours.
 

aeric

Expert
Licensed User
Longtime User
I will upload the project within 1 or 2 hours.
I afraid there is a slight delay as I found an issue. Once the project is compiled, the handler class and project files are not accessible (if I don’t copy the source code to production). After my dinner, I will modify the project to support Release mode.
 

aeric

Expert
Licensed User
Longtime User
Web API template v1.10 is now available for download in post #1 (Web API.b4xtemplate)

What do I mean by auto generated help file?
The HelpHandler class when run in debug mode will create the help file HTML and JavaScript based on the methods defined in your handler class. You can ignore some files so the HelpHandler wouldn't read it. The class first read any handler class that you added in Main class in AppStart sub. If you commented the line then it won't be included in the help file. You can try uncomment the DefaultHandler class to see a simple example. After you added or uncomment the handler class, you need to make sure the class is not added in the IgnoredHandlers list inside ReadProjectFile sub of HelpHandler. Open the DefaultHandler class and you will see some important variables in the Class_Globals which are required in order for the HelpHandler to generate the documentation correctly.

Any method you define, with the name begins with the RESTful API verb such as GET, POST, PUT and DELETE (not case sensitive) will be consider as the API methods and will be appear in the help file based on following requirements:
1. The whole method is not commented
2. The method does not have both #desc1 and #desc2 comments set as (N/A)

Why I have DESC1 and DESC2?
====================
B4X currently doesn't support Method Overloading (optional parameters)
Since I have a common GET method to return a specific row which requires an ID
and the "second" method with same name to fetch a list of all items,
I am using this trick to differentiate them so they both would appear as
two different Methods in documentation (with different descriptions and parameter)
Example:
GetCategories(3) -> load the row where id = 3
GetCategories(0) -> load all rows where id is optional (zero or empty string depend on parameter type)
For method that does not require a trailing ID (usually POST), set the #desc1 as (N/A)
For other Methods that mandatory require a trailing ID (PUT and DELETE), set the #desc2 as (N/A)
By default, #desc2 is set as (N/A) and #desc1 is empty, it means the method with ID is always show by default (but no description)
If you set both as (N/A) then the method would not appear in the documentation.

Comments with #keyword (case insensitive) are important:
B4X:
    #region Documentation
    ' #Desc1 = Update existing category by id
    ' #Desc2 = (N/A)
    ' #Elems = 2
    ' #Body = {<br>&nbsp; "name": "category_name"<br>}
    #End region
All comments are optional and handled by default. Order is not important as long as it is inside the method. #Elems provides the format for path in GenerateLink sub. #Body provides the format for the request body.


Additional info:
To compile and run the API server in production, you need to run the project in debug mode and navigate to the Help file once. Make sure blnGenFile is set to True in HelpHandler class. The app will generate a help.html and help.js file. Make sure you copy the jar file, www folder, the 3 ini files and these html and js files from the project Objects folder to your production server.
 
Last edited:

B4XDev

Member
Licensed User
Would it be possible to build a secure API solution with this template? That is, I'd like to allow only authorized access to the API (say, from my app only). How would that be done?
 

aeric

Expert
Licensed User
Longtime User

ilan

Expert
Licensed User
Longtime User
hi @aeric,

really awesome work you did here.

i have a question, please. I see in your code that you are opening and closing the pool connection and i would like to ask if this is necessary?

so the project i am working on right now will look like this. the web app and sql server will run on the same server. the server connects to the MySQL server and the connection is always open. now every call a client is doing i am getting the data from the DB and return it as a JSON object.

NO client will connect directly to the DB. only the web app is connecting to the MySQL server. do i still need to open/close the connection?

thanx
 

aeric

Expert
Licensed User
Longtime User
hi @aeric,

really awesome work you did here.

i have a question, please. I see in your code that you are opening and closing the pool connection and i would like to ask if this is necessary?

so the project i am working on right now will look like this. the web app and sql server will run on the same server. the server connects to the MySQL server and the connection is always open. now every call a client is doing i am getting the data from the DB and return it as a JSON object.

NO client will connect directly to the DB. only the web app is connecting to the MySQL server. do i still need to open/close the connection?

thanx
If you "forget" or purposely don't close the connection then you may encounter MaxPool used when more and more connections are made.
So the answer from me is Yes.
 

ilan

Expert
Licensed User
Longtime User
If you "forget" or purposely don't close the connection then you may encounter MaxPool used when more and more connections are made.
So the answer from me is Yes.

thanx for your answer @aeric

so i made some tests with sql workbench and i am still not sure if i have to open/close connection.

i will explain again how i build my api webapp

i have a b4j webapp server that is connecting to a remote sql server.

this is the only app that is connecting to the server.

now every client that is making a request to the webapp the webapp is fetching the data from the sql and return back to the client.
so i am not creating a new connection. there is only 1 single connection that is open and every client (web browser, mobile app, etc...) is making get/post requests to the webapp from a websocket or from a server handler and the webapp is sending back the data that ONLY the webapp is collecting from the mysql db.

do i still need to make a open/close connection each time a client is making a request?

seems not right to me but i may be wrong.
:confused:

EDIT: if i would use a b4x app that connects directly to the MySQL db that i would open/close connection each time i would want to read/write from the DB to avoid to many connections but in the scenario i have described above i am not connecting to the DB i am such making get/post requests to the webapp!!
 
Top