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

aeric · Aug 26, 2021

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

Updates:
Web API Server 2.0 is available. Check https://www.b4x.com/android/forum/threads/project-template-web-api-server-2.143310/

Template:

Web API Server (1.16).b4xtemplate

Depends on following libraries:

ByteConverter
JavaObject
jServer
Json
jSQL

Features:

CRUD based - RESTful Web API (GET, POST, PUT, DELETE)
Front-end (HTML, CSS, JS, Bootstrap)
Support MySQL and SQLite database (Can be modified for MS SQL or SQL Express)
Separate SQL queries file (queries-mysql.ini and queries-sqlite.ini)
Sample database auto-generated for first run (Category and Product tables with dummy data)
Versioning (using ROOT_PATH in config.ini, set as "/" if you don't want versioning)
Auto generated documentation with API test.

How to use:

Copy the "Web API Server (1.16).b4xtemplate" file into B4J Additional Libraries folder.
Open B4J and create a new project with "Web API Server" template.
Give your project any name you like, for e.g. WebAPI

Run the project in Debug 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/

Copy the URL showed in Logs and open it using your web browser.
To connect to MySQL server, go to Objects folder and open "config.ini".
Comment the SQLite section and uncomment the MySQL section.
Edit the root password at line #45 (second last line). Save the file.
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
```
Follow step #3 above.

Client Template:

Web API Client (1.05).b4xtemplate

. . . .

How to use:

Copy the "Web API Client (1.05).b4xtemplate" file into B4X Additional Libraries folder.
Open B4A (B4J or B4i) and create a new project with "Web API Client (1.05)" template.

Edit the URL with your Web API Server host or IP

B4X:

Private URL As String = "http://192.168.50.42:17178/v1/" ' Change to your Web API Server URL

Run in debug.

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.

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

About auto generated help file (version 1.10+)
https://www.b4x.com/android/forum/threads/web-api-template.133764/post-851114

Pendrush · Oct 15, 2021

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:

ilan · Oct 15, 2021

Pendrush said:
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:

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.

Open/Close Connection?

hi i am building a Rest Api using B4j WebApp running on a LAMP Server. My Questions is: the web app is the only client that is connecting to the MySql DB. Every other client can make get/post requests to the b4j webapp => b4j webapp read/write data to the mysql db and returns back to the...

www.b4x.com

aeric · Oct 15, 2021

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.

aeric · Oct 18, 2021

19/10/2021 - v1.11
- Better use of Connection Pool for MySQL (pool is not close)
- Change DataUtils module to DataConnector class
- Clean up some redundant codes

Mashiane · Oct 27, 2021

Hi

Can I please suggest that on the first post, you just only keep the latest versions of the awesome files. I just got confused.

aeric · Oct 27, 2021

Mashiane said:
Hi

Can I please suggest that on the first post, you just only keep the latest versions of the awesome files. I just got confused.

Good suggestion. Will do it.

juangtju · Oct 28, 2021

hi excuse me , i use your example for create a web app with connect to a mysql database , but i dont know how is the procedure to consult a new database for save record in other database with other field on it created in a form, you can help me with that??

aeric · Oct 29, 2021

juangtju said:
hi excuse me , i use your example for create a web app with connect to a mysql database , but i dont know how is the procedure to consult a new database for save record in other database with other field on it created in a form, you can help me with that??

I construct the database table using script.
You can check queries_mysql.ini inside the Objects folder. The MySQL connection username and password are set in config.ini
You can also design your own database using other tools such as Heidi, PHPMyAdmin or Adminer.

tchart · Oct 29, 2021

Pendrush said:
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:

Slightly off topic but I recently did some tests with HikariCP and I got worse performance compared to having a single DB connection (defined in Main). I did some further reading on this and having a connection pool offers no advantage for a local database.

This was doing the same stress test I've documented on here before. Using the H2 database I get about 4000-5000 requests per second. When run with HikariCP it goes down to 1500-3500 requests per second (I noticed it did fluctuate a lot).

aeric · Oct 29, 2021

tchart said:
Slightly off topic but I recently did some tests with HikariCP and I got worse performance compared to having a single DB connection (defined in Main). I did some further reading on this and having a connection pool offers no advantage for a local database.

This was doing the same stress test I've documented on here before. Using the H2 database I get about 4000-5000 requests per second. When run with HikariCP it goes down to 1500-3500 requests per second (I noticed it did fluctuate a lot).

Can I summarize that it is sufficient to use the normal connection pool as Erel’s example?

tchart · Oct 29, 2021

aeric said:
Can I summarize that it is sufficient to use the normal connection pool as Erel’s example?

I would say yes, if you are using a TCP based database connection (eg MySQL, Postgres etc) then it makes sense to use a pool.

If you are using a file based database then don't use a pool.

juangtju · Oct 29, 2021

aeric said:
I construct the database table using script.
You can check queries_mysql.ini inside the Objects folder. The MySQL connection username and password are set in config.ini
You can also design your own database using other tools such as Heidi, PHPMyAdmin or Adminer.

yes i know that , the problem is not the creation of a new table , i have the existing table with i workaround , but who i dont know is how to call these tables in a b4xtables example you have posted here , that is my principal problem

aeric · Oct 30, 2021

juangtju said:
yes i know that , the problem is not the creation of a new table , i have the existing table with i workaround , but who i dont know is how to call these tables in a b4xtables example you have posted here , that is my principal problem

You can post a new question. I think you can’t send me a personal message since you are new here.

juangtju · Nov 1, 2021

ok excelent , thanks for help me i post here the link for my question

aeric · Jan 25, 2022

aeric said:
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.

You can follow this tutorial to create a single zip file for uploading to your hosting server using FTP client such as FileZilla or WinSCP.
https://www.b4x.com/android/forum/threads/using-custombuildaction-with-winrar.137900/

aeric · Jan 25, 2022

Create a Publish.zip using Comment Link

B4X:

'Click to create Publish.zip: ide://run?file=%JAVABIN%\jar.exe&WorkingDirectory=../Objects&Args=-cMf&Args=Publish.zip&Args=www&Args=*.jar&Args=*.ini&Args=*.db&Args=help.html&Args=help.js&Args=comments.txt

Related: https://www.b4x.com/android/forum/threads/using-custombuildaction-with-winrar.137900/post-872979

joulongleu · Jan 27, 2022

HI Aeric:Web API is very good,Can I use any Table if I want? Thank You.

aeric · Jan 27, 2022

joulongleu said:
HI Aeric:Web API is very good,Can I use any Table if I want? Thank You.

Of course you may modify to use your own database table.

joulongleu · Jan 27, 2022

aeric said:
Of course you may modify to use your own database table.

Is there an example of how to modify it?Thank you.

aeric · Jan 27, 2022

joulongleu said:
Is there an example of how to modify it?Thank you.

This template is already showing an example.

Please start a new thread and tell us what you don't understand about this example.

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

Attachments

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Member

Expert

Well-Known Member

Expert

Well-Known Member

Member

Expert

Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency