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: 76
  • WebAPIClient 1.01.zip
    442.7 KB · Views: 77
  • Web API.b4xtemplate
    462.9 KB · Views: 33
  • WebAPIClient 1.02.zip
    443.3 KB · Views: 24
  • Web API 1.11.zip
    459.2 KB · Views: 0
Last edited:

Pendrush

Well-Known Member
Licensed User
As general rule you should open connection for client, when server return result connection should be closed.
Better way is to use some MySQL (MariaDB) connection pool. As open/close connection is expensive. When client request connection "hot" (pool) connection is given to the client. On connection close, connection is returned to the pool. HikariCP is my preffered.

Pool performance chart:

HikariCP-bench-2.6.0.png
 

ilan

Expert
Licensed User
As general rule you should open connection for client, when server return result connection should be closed.
Better way is to use some MySQL (MariaDB) connection pool. As open/close connection is expensive. When client request connection "hot" (pool) connection is given to the client. On connection close, connection is returned to the pool. HikariCP is my preffered.

Pool performance chart:

HikariCP-bench-2.6.0.png

sorry, but I have not understood your answer. only 1 client is making the connection. all other clients are just making get/post requests to the webapp.

anyway, I have opened a new thread since I don't want to make a big mess in @aeric's thread.

 

aeric

Expert
Licensed User
You can do an experiment if you don’t believe you need to close the connection. 😊

I share my experience here. I have jAdmin hosted in my VPS and I didn’t close the connection correctly last time. I also didn’t set the MaxPoolConnection. Then after a few minutes, the app cannot be access. The server has reached the default maximum number of connection.

REST API is designed to be stateless. It is different from web socket. However you still can keep user session in server.

Let say I am making a Get request from Chrome, which involve a database query, a connection is made. Then I make second call to the same request, a new connection is made. So after a while, many connections are made. I may close the browser and open again multiple times and each time, a new connection is created. The server doesn’t do garbage collection and not smart enough to close the connections for me automatically. It doesn’t know if you don’t implicitly close it. This is my understanding.
 
Top