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 settings
spring.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><email type="string"><![CDATA[john@example.com]]></email><name type="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.