SQLBridge v2

A Spring Boot REST API service that provides configurable database connectivity through XML-based configuration. Execute SQL queries against multiple databases without code changes.

Technology Stack: Spring Boot 3.3.5 | Groovy | Java 17 | HikariCP

---

Overview

SQLBridge v2 acts as a database connectivity bridge, providing HTTP endpoints for executing SQL queries against multiple heterogeneous databases. Configuration is entirely XML-driven, allowing new queries and data sources to be added without code deployment.

Key Benefits:

• Configuration-driven SQL execution (no code changes for new queries)
• Support for multiple databases simultaneously
• Multiple response formats (XML, JSON, tab-separated)
• Connection pooling with HikariCP
• Secure credential management via environment variables

---

Features

• Multi-Database Support: SQL Server (with Windows integrated auth), Oracle, IBM DB2/iSeries, HSQLDB, CSV
• REST API Endpoints: Three endpoints with different response formats
• XML Configuration: Define SQL actions, scripts, and database mappings
• Groovy Scripting: Dynamic SQL generation with parameter substitution
• Connection Pooling: HikariCP for efficient database connections
• Environment Variables: Secure credential management

---

Quick Start

Prerequisites

• Java 17+
• Gradle 8.x (or use included wrapper)

Setup

1. Clone the repository

   git clone <repository-url>cd sqlbridge_v2

2. Configure environment variables

   cp .env.example .env
   # Edit .env with your database credentials

3. Run the application

   ./gradlew bootRun

4. Test the API

   curl "http://localhost:8080/sqlbridge/dataconnect/jsonAction?key=DEFAULTtest&searchfor=example"

---

Configuration

Environment Variables

Copy .env.example to .env and configure your database credentials:

# Database 0
DB0_JDBC_URL=jdbc:sqlserver://your-server:1433;encrypt=false
DB0_USERNAME=your_username
DB0_PASSWORD=your_password
# Database 1
DB1_JDBC_URL=jdbc:sqlserver://your-server:1433;encrypt=false
DB1_USERNAME=your_username
DB1_PASSWORD=your_password
# Database 2
DB2_JDBC_URL=jdbc:sqlserver://your-server:1433;encrypt=false
DB2_USERNAME=your_username
DB2_PASSWORD=your_password

Up to 6 databases (db0-db5) can be configured.

DataSource Configuration

Database connections are configured in conf/sqlbridge-datasource.properties:

spring.datasource.db0.jdbcUrl=${DB0_JDBC_URL}
spring.datasource.db0.username=${DB0_USERNAME}
spring.datasource.db0.password=${DB0_PASSWORD}
spring.datasource.db0.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
# HikariCP settingsspring.datasource.db0.hikari.minimum-idle=5
spring.datasource.db0.hikari.maximum-pool-size=10
spring.datasource.db0.hikari.idle-timeout=6000
spring.datasource.db0.hikari.max-lifetime=18000

SQL Action Configuration

SQL queries are defined in conf/sqlbridge-config.xml:

<Configurations>
<Actions>
<Action>
<Name>select</Name>
<KeyScript>config + key</KeyScript>
<Configuration>
<Key>DEFAULTcustomers</Key>
<SQLLookupScript><![CDATA[ "SELECT id, name, email FROM customers WHERE name LIKE '%${searchfor}%'"]]></SQLLookupScript>
<JndiName>db0</JndiName>
</Configuration>
<Configuration>
<Key>DEFAULTorders</Key>
<SQLLookupScript><![CDATA[ "SELECT order_id, total FROM orders WHERE customer_id = '${customerid}'"]]></SQLLookupScript>
<JndiName>db1</JndiName>
</Configuration>
</Action>
</Actions>
</Configurations>

Configuration Elements:

• Name: Action type (select, json, xml)
• KeyScript: Groovy script that determines which configuration to use
• Key: Unique identifier matching the KeyScript result
• SQLLookupScript: SQL query with ${parameter} placeholders
• JndiName: Database identifier (db0-db5)

---

API Reference

Base URL:http://localhost:8080/sqlbridge/dataconnect

GET /selectAction

Returns query results in tab-separated format.

curl "http://localhost:8080/sqlbridge/dataconnect/selectAction?key=DEFAULTcustomers&searchfor=john"

Response:

RESULTSET|john@example.com	John Doe	|jane@example.com	Jane Smith	|

GET /jsonAction

Returns query results as JSON with execution metadata.

curl "http://localhost:8080/sqlbridge/dataconnect/jsonAction?key=DEFAULTcustomers&searchfor=john"

Response:

{
"success": true,
"message": "",
"executionTimeMs": 45,
"data": [
{"email": "john@example.com", "name": "John Doe"},
{"email": "jane@example.com", "name": "Jane Smith"}
]
}

POST /xmlPostAction

Accepts XML request body and returns structured XML response.

curl -X POST "http://localhost:8080/sqlbridge/dataconnect/xmlPostAction" \
-H "Content-Type: application/xml" \
-d '<request><key>DEFAULTcustomers</key><searchfor>john</searchfor></request>'

Response:

<?xml version="1.0" encoding="ISO-8859-1"?>
<response>
<requeststatus>true</requeststatus>
<errordescription></errordescription>
<fields>
<row><emailtype="string"><![CDATA[john@example.com]]></email><nametype="string"><![CDATA[John Doe]]></name></row>
</fields>
</response>

Common Parameters

Parameter	Description
key	Configuration key (matches <Key> in XML config)
config	Configuration prefix (default: "DEFAULT")
searchfor	Search term for LIKE clauses
Custom parameters	Any parameter referenced in SQLLookupScript

---

Build & Development

Build Commands

Command	Description
./gradlew bootRun	Run development server on port 8080
./gradlew bootJar	Build JAR artifact (build/libs/sqlbridge.jar)
./gradlew test	Run all tests
./gradlew clean build	Clean and rebuild project

Project Structure

sqlbridge_v2/
├── src/main/groovy/dk/bpas/sqlbridge/
│ ├── SqlbridgeV2Application.groovy # Main entry point
│ ├── rest/
│ │ └── DataLinkRestController.groovy # REST endpoints
│ ├── config/
│ │ ├── ConfigXml.groovy # XML config loader
│ │ └── ShellFunctions.groovy # SQL helper functions
│ └── database/
│ ├── MultiDataSourceConfig.groovy # DataSource beans
│ └── DatabaseService.groovy # DataSource accessor
├── conf/
│ ├── sqlbridge-config.xml # SQL action configuration
│ └── sqlbridge-datasource.properties # Database configuration
├── .env.example # Environment template
└── build.gradle # Build configuration

Testing

# Run all tests
./gradlew test# Run specific test class
./gradlew test --tests ConfigXmlTest
# Run with verbose output
./gradlew test --info

---

Deployment

JAR Deployment

# Build the JAR
./gradlew clean bootJar
# Run with environment variables
java -DDB0_JDBC_URL=jdbc:sqlserver://server:1433 \
-DDB0_USERNAME=user \
-DDB0_PASSWORD=pass \
-jar build/libs/sqlbridge.jar

Logging

Logs are written to logs/sqlbridge-info.log with daily rotation (7-day retention).

Configure in application.properties:

logging.file.name=logs/sqlbridge-info.log
logging.logback.rollingpolicy.max-history=7

Health Check

curl http://localhost:8080/actuator/health

---

Architecture

Request Flow

HTTP Request
│
▼
DataLinkRestController
│
├─► Evaluate KeyScript (Groovy)
│ │
│ ▼
├─► Lookup Configuration by Key
│ │
│ ▼
├─► Resolve DataSource (db0-db5)
│ │
│ ▼
├─► Evaluate SQL Script with parameters
│ │
│ ▼
├─► Execute Query (Groovy SQL + HikariCP)
│ │
│ ▼
└─► Format Response (XML/JSON/Tab-separated)
│
▼
HTTP Response

Supported Database Drivers

Database	Driver
SQL Server	com.microsoft.sqlserver.jdbc.SQLServerDriver
SQL Server (jTDS)	net.sourceforge.jtds.jdbc.Driver
Oracle	oracle.jdbc.OracleDriver
IBM DB2/iSeries	com.ibm.as400.access.AS400JDBCDriver
HSQLDB	org.hsqldb.jdbc.JDBCDriver
CSV	org.relique.jdbc.csv.CsvDriver

---

License

This is proprietary software developed by BPA Solutions A/S. All rights reserved.

---

Support

For internal support, contact the development team.