Share My Creation jRDC2 MultiDB Server (JSON, HikariCP)

1. Introduction and Architecture​

This project is a modified version of the original jRDC2 Server. The multi-database support is based on Erel's JDC2 Multiple Database modification, and includes three main structural changes:

• HikariCP Migration: The connection pool was migrated from C3P0 to HikariCP (v4.0.3), known for being faster and more efficient. This migration was made possible by adapting the base code presented in this B4X forum thread on HikariCP integration.
• JSON Handler Addition: A new handler was added that accepts requests and returns responses in JSON format, allowing connection from web clients (JavaScript, NodeJS, React, Vue, Angular, etc), in addition to native clients (B4A/B4i/B4J/B4R).
• Multi-Database Core Support: Integration of multi-database capabilities, allowing the server to manage multiple separate database configurations simultaneously via a single process/port.

This project's modifications were driven by dual motivations: The new JSON handler was developed to allow my NodeJS-based WhatsApp Bot and other web clients to securely access the same database as my other B4A applications. Separately, the Multi-Database support was introduced to overcome port limitations on test servers, allowing many database instances to run securely behind a single, shared server port.

Important Note on Complexity
While this modified server introduces advanced features like hot-swapping, multiple database support, and HikariCP tuning, its initial use is still Plug and Play. For a single database and standard setup, you only need to configure the essential parameters in config.properties (DriverClass, JdbcUrl, User and Password) and the sql commands, making the barrier to entry very low. The additional features are for power users and scaling and are NOT necesary.

Version Note: 5.10.25 (First Stable Release)
This is the FIRST official stable version of the server. The high numbering (5.10.25) is due to an internal date system (Year.Month.Day) and does not imply prior public releases.

2. Key Features​

• High-Performance Connection Pool: Complete and consolidated migration to HikariCP.
• Support for Multiple Dynamic Databases: The server can load and manage an "unlimited" number of configurations (config.XXX.properties) simultaneously ... unlimited in theory, BUT limited by hardware resources.
• Dual Request Handlers:
  • Classic Handler: Full compatibility with the B4X DBRequestManager.
  • JSON Handler: New endpoint for web clients using JSON requests and responses.
• Security Validations: Verifies the existence of SQL commands and the correspondence in the number of parameters before execution.
• Granular Hot-Swap Configuration Management: The reload command allows safely reloading the configuration for each database separately, without needing to restart the server.
• Advanced Monitoring: Precise pool metrics (Busy Connections, Total Connections) and application metrics (Active Requests) are exposed.
• Protected Remote Administration: Allows checking status, reloading configuration, and restarting the server via specific URLs, with an authentication system.
• Configurable Parameter Tolerance: The parameterTolerance=1 property allows trimming surplus parameters in a query, logging a warning instead of forcing an error.

---

3. Configuration​

3.0. Quick Start (Plug and Play!)​

To get the server up and running with a single database, you only need to define the following essential parameters in your config.properties file:

1. JdbcUrl: The connection string to your database (e.g., jdbc:mysql://localhost/mydb).
2. user: The database username.
3. password: The database password.
4. DriverClass: The fully qualified name of the JDBC driver class (e.g., com.mysql.jdbc.Driver).

All other advanced HikariCP tuning parameters have sensible defaults and can be configured later or not at all.

3.1. Configuration Files (Dynamic Naming)​

The server detects and loads any file following the pattern config.XXX.properties. The XXX portion becomes the uppercase connection key (DBKey).

File	Connection Key (DBKey)
config.properties	DB1 (Default)
config.my_client.properties	MY_CLIENT

Important Notes:

• The server port is taken only from the main config.properties file. Port settings in other files are ignored.
• Connection details (JdbcUrl, user, password, DriverClass) are taken from the corresponding file for each database.
• To connect to other database types (e.g. Oracle), you must add the driver's .jar file to the project before compiling (e.g. #AdditionalJar: ojdbc11).

3.2. Pool and Driver Configuration​

Technical Warning: Only the parameters listed below are explicitly mapped and supported in the B4J wrapper's SetProperties logic. Any other HikariCP property not on this list will be ignored by the server code.

All HikariCP tuning properties must be prefixed with pool.hikari.. All time values are expressed in milliseconds (ms).

• Pool Configuration (Supported Parameters)​

Parameter	Type	Default Value (Code)	Technical Description and Recommendation
maximumPoolSize	INT	10	Maximum connection limit. Should be based on the DB server's resources (approx. (DB Cores * 2) + Spindles).
minimumIdle	INT	Equal to maximumPoolSize	Fixed Pool Axiom. Recommended to equal maximumPoolSize to eliminate pool instability (thrashing).
maxLifetime	LONG	1,750,000 ms (30 min)	CRITICAL. Maximum life time for a connection. Must be less than the firewall/DB timeout to prevent stale connections.
keepalivetime	LONG	300,000 ms (5 min)	"Ping" frequency for idle connections. Acts to prevent the infrastructure from killing the connection due to inactivity.
connectionTimeout	LONG	30,000 ms (30 sec)	Maximum waiting time for the client for an available connection. A low value is recommended for fail-fast.
leakDetectionThreshold	LONG	35,000 ms	Diagnostic. Time threshold a connection can be held. ONLY warns, does not recover the connection.
idletimeout	LONG	600,000 ms (10 min)	Idle time before retiring the connection. Only applies if minimumIdle < maximumPoolSize.
initializationfailtimeout	INT	1	Time to acquire the first connection. Recommended 1 (or greater) for fail-fast.
validationtimeout	INT	5,000 ms (5 sec)	Maximum time allowed for a connection liveness test.
autocommit	BOOLEAN	true	Controls the default auto-commit behavior.
poolname	STRING	Auto-generated	User-defined name for the pool.
connectiontestquery	STRING	none	Query to validate the connection. (Use only if the driver DOES NOT support JDBC4).
registermbeans	BOOLEAN	true	Controls whether JMX Management Beans (MBeans) are registered.
isolateinternalqueries	BOOLEAN	false	Advanced property.
allowpoolsuspension	BOOLEAN	false	Advanced property.
readonly	BOOLEAN	false	Indicates if connections are retrieved in read-only mode.
connectioninitsql	STRING	none	SQL executed when establishing a new connection.

---

• Driver Configuration (Crucial for Performance):​

  For low-level optimizations (e.g., Statement Caching), DriverShortName must be defined and the prefix driver.SHORTNAME. must be used for the properties.
  
  DriverShortName=mysql
  driver.mysql.cachePrepStmts=true

4. Security Validations and Parameters​

1. Command Existence Check: The server checks that the requested SQL command name (e.g., "get_user") exists as a valid key in the corresponding .properties file. If not found, it will return an error and will not attempt to execute anything.
2. Parameter Count: If the SQL command in the configuration file expects parameters (contains ?), the server counts how many there are and compares it with the number of parameters received in the request. If the quantities do not match, it will return a specific error, preventing a failed execution in the database.
3. Parameter Tolerance: Implements configurable parameter tolerance. This feature is primarily intended for migration periods or for maintaining compatibility with legacy clients that may inadvertently send surplus parameters due to previous bugs or version mismatches. Enabling tolerance avoids the need to recompile and reinstallall existing client applications for minor parameter count errors.
   • parameterTolerance=0 (Strict): Rejects requests if the number of received parameters is greater than expected.
   • parameterTolerance=1 (Tolerant): Trims the surplus parameters to match the SQL and logs a warning.

These validations ensure that the developer receives immediate and clear feedback if a request is malformed.

5. Using the Classic Handler (For B4X Clients)​

This handler maintains compatibility with DBRequestManager. Database selection is performed dynamically via the URL (e.g.,port=8090).

• For config.properties => http://your-domain.com:8090
• For config.CLIENT2.properties => http://your-domain.com:8090/CLIENT2
• For config.DB2.properties => http://your-domain.com:8090/DB2
• For config.TEST.properties => http://your-domain.com:8090/TEST

6. Using the DBHandlerJSON (For Web Clients)​

This handler is designed for clients that communicate via JSON, such as JavaScript web applications.

6.1. Endpoint and Submission Methods
Requests should be directed to the endpoint /DBJ. The handler is flexible and accepts data in two ways:

Recommended Method: POST with JSON Body

• HTTP Method: POST
• URL: http://your-domain.com:8090/DBJ
• Required Header: Content-Type: application/json
• Body (Payload): The JSON object is sent directly in the request body.

Example Body:

{ "dbx": "DB2", "query": "get_user", "exec": "executeQuery", "params": [ "CDAZA" ] }

Legacy Method: GET with j Parameter

• HTTP Method: GET
• URL: The complete JSON is sent as the value of the j parameter in the URL.

Example with GET: http://your-domain.com:8090/DBJ?j={"dbx":"DB2","query":"get_user","exec":"executeQuery","params":["CDAZA"]}

6.2. JSON Payload Format​

The structure of the JSON object is the same for both methods:

{ "exec": "executeQuery", "query": "sql_command_name", "dbx": "DB1", "params": [ "CDAZA", 123 ] }

• exec: "executeQuery" (for SELECT) or "executeCommand" (for INSERT, UPDATE, DELETE).
• query: Name of the SQL command as defined in the configuration file.
• dbx (optional): The database key (DB1, DB2, etc.). If omitted, DB1 will be used.
• params (optional): An array containing the parameters for the SQL query, in the exact order expected.

6.3. JSON Responses​

Server responses are always in JSON format and include a boolean success field.

• If success is true, the data will be found under the result key.
• If success is false, the error message will be found under the error key.

7. Server Administration​

Go to http://your-domain.com:8090/login

• User: admin
• Password: 12345

7.1. Protected Commands (Hot-Swap Reload) (requires authentication)​

Granular reloading allows updating a single connector without affecting the others.

• Full Reload: /manager?command=reload
• Selective Reload: /manager?command=reload&db=DBKEY (e.g., DB2 or CLIENTE_A)

7.2. Monitoring Commands​

• Check Availability (Ping): /ping (Returns a simple PONG to confirm the server is running).
• Check Connectivity: /manager?command=test (Forces the acquisition/release of a connection in all pools to verify their liveness status, implementing the fail-fast mechanism).
• Detailed Configuration: /manager?command=getconfiginfo (Shows the actual applied HikariCP configuration and Driver Properties for tuning diagnostics).