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.
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=1property 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:- JdbcUrl: The connection string to your database (e.g., jdbc:mysql://localhost/mydb).
- user: The database username.
- password: The database password.
- DriverClass: The fully qualified name of the JDBC driver class (e.g., com.mysql.jdbc.Driver).
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),DriverShortNamemust be defined and the prefix driver.SHORTNAME. must be used for the properties.
B4X:DriverShortName=mysql driver.mysql.cachePrepStmts=true
4. Security Validations and Parameters
- 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.
- 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.
- 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.
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.
JSON:
{ "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.
6.2. JSON Payload Format
The structure of the JSON object is the same for both methods:
JSON:
{ "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).
Attachments
Last edited: