JDBCX

<img align="right" width="96" height="96" src="https://avatars.githubusercontent.com/u/137983508">

JDBCX extends JDBC with enhanced data format and compression support, object mapping, advanced type conversion, and multi-language query capabilities. It simplifies federated queries through dynamic embedding and offers a remote bridge for seamless multi-source connectivity.

Quick Start

Getting started with JDBCX is easy. Use it as a standard JDBC driver, a standalone bridge server, or combine both functionalities.

# Using the standard JDBC driver
$ docker run --rm -it jdbcx/jdbcx 'jdbc:duckdb:' 'select 2 as num'
num
2

# Using JDBCX as a drop-in replacement (with extensions)
$ docker run --rm -it jdbcx/jdbcx 'jdbcx:duckdb:' 'select {{script:1+1}} as num'
num
2
$ docker run --rm -it jdbcx/jdbcx 'jdbcx:' "{{ db.ch-[ap]*: select '\${_.id}' db, version() ver }}"
db	ver
ch-play	25.5.1.664
ch-altinity	25.3.3.42

# Using JDBCX as a bridge server (HTTP API for data access)
$ docker run --rm -d --name bridge -p8080:8080 jdbcx/jdbcx:full server
$ curl -s -d 'select 2 as num' 'http://localhost:8080/query'
num
2
$ curl -s -d '{{ db.duckdb-local: select 2 as num }}' 'http://localhost:8080/query'
num
2

# Combining JDBCX driver features with the bridge server for federated querying
$ curl -s -d "select * from {{ table.mcp.everything(target=prompt) }}
where name like 'simple%'" 'http://localhost:8080/query'
name,description,arguments
simple-prompt,A prompt without arguments,
$ curl -s -d 'select c.name
from {{ table.db.ch-play: show databases }} c
left outer join {{ table.db.ch-altinity: show databases }} a
  on c.name = a.name
where a.name is null' 'http://localhost:8080/query'
name
blogs
git_clickhouse
mgbench

Features

<table> <tr> <td> Feature </td> <td> Examples </td> </tr> <tr> <td> Chained query </td> <td>

-- ask a question(check out ~/.jdbcx/web/baidu-*.properties for details)
{{ web.baidu-llm(pre.query=web.baidu-auth): who are you? }}

-- get messages of a chat(see ~/.jdbcx/web/m365-*.properties for details)
{{ web.m365-graph(
	pre.query=web.m365-auth,
	result.json.path=value,
	m365.api="chats/<URL encoded chat ID>/messages?$top=50")
}}

</td> </tr> <tr> <td> Dynamic query </td> <td>

-- https://clickhouse.com/docs/en/sql-reference/aggregate-functions/parametric-functions#retention
{% var(delimiter=;): dates=['2020-01-01','2020-01-02','2020-01-03'] %}
SELECT
    uid,
    retention({{ script: "date='" + ${dates}.join("',date='") + "'" }}) AS r
FROM retention_test
WHERE date IN ({{ script: "'" + ${dates}.join("','") + "'" }})
GROUP BY uid
ORDER BY uid ASC

</td> </tr> <tr> <td> Multi-language query </td> <td>

{% var: num=3 %}
select {{ script: ${num} - 2 }} one,
    {{ shell: echo 2 }} two,
    {{ db.ch-play: select ${num} }} three

</td> </tr> <tr> <td> Query substitution </td> <td>

{% var: func=toYear, sdate='2023-01-01' %}
SELECT ${func}(create_date) AS d, count(1) AS c
FROM my_table
WHERE create_date >= ${sdate}
GROUP BY d

</td> </tr> <tr> <td> Scripting </td> <td>

-- benchmark on ClickHouse
select a[1] `CPU%`, a[2] `MEM(KB)`, a[3] `Elapsed Time(s)`,
	a[4] `CPU Time(s)`, a[5] `User Time(s)`, a[6] `Switches`,
	a[7] `Waits`, a[8] `File Inputs`, a[9] `File Outputs`, a[10] `Swaps`
from (
select splitByChar(',', '{{ shell.myserver(cli.stderr.redirect=true):
/bin/time -f '%P,%M,%e,%S,%U,%c,%w,%I,%O,%W' du -sh . > /dev/null
}}') a
)

-- runtime inspection
{{ script: helper.table(
  // fields
  ['connection_class_loader', 'current_class_loader', 'context_class_loader'],
  // rows
  [
    [
      Packages.io.github.jdbcx.WrappedDriver.__javaObject__.getClassLoader(),
      helper.getClass().getClassLoader(),
      java.lang.Thread.currentThread().getContextClassLoader()
    ]
  ]
)
}}

</td> </tr> </table>

Known Issues

| # | Issue | Workaround | | --- | ----------------------------------------- | ----------------------------------- | | 1 | Query cancellation is not fully supported | avoid query like {{ shell: top }} | | 2 | Connection pooling is not supported | - | | 3 | Nested query is not supported | - | | 4 | MCP extension requires JDK 17+ | - |

Security

To secure datasource credentials, encrypt them with a secret key. This key is typically stored as an encrypted secret file in k8s or docker swarm. Remember, if you rename a datasource, you must re-encrypt its credentials.

$ cat mysql1.properties
jdbcx.description=My MySQL server for development.
jdbcx.driver=com.mysql.cj.jdbc.Driver
jdbcx.url=jdbc:mysql://localhost:3306
user=root
password=<my root password>

# Generate secret key
$ docker run --rm -it jdbcx/jdbcx keygen > secret.key

# Use the secret key to encrypt password for specific datasource
$ docker run --rm -it -v `pwd`/secret.key:/app/.jdbcx/secret.key jdbcx/jdbcx encrypt '<my root password>' 'mysql1'
Gm7+r5vldBu+irReosAWUosbWhNWpiYaocmspi5oeRK/tLsNH0U/zUp7jDmAqGQ=

# Verify the encrypted password
$ docker run --rm -it -v `pwd`/secret.key:/app/.jdbcx/secret.key jdbcx/jdbcx decrypt 'Gm7+r5vldBu+irReosAWUosbWhNWpiYaocmspi5oeRK/tLsNH0U/zUp7jDmAqGQ=' 'mysql1'
<my root password>

# Update datasource configuration to use encrypted password
$ sed -i .bak 's|^\(password\)=.*|\1.encrypted=Gm7+r5vldBu+irReosAWUosbWhNWpiYaocmspi5oeRK/tLsNH0U/zUp7jDmAqGQ=|' mysql1.properties
$ cat mysql1.properties
jdbcx.description=My MySQL server for development.
jdbcx.driver=com.mysql.cj.jdbc.Driver
jdbcx.url=jdbc:mysql://localhost:3306
user=root
password.encrypted=Gm7+r5vldBu+irReosAWUosbWhNWpiYaocmspi5oeRK/tLsNH0U/zUp7jDmAqGQ=

# Test the encrypted datasource
$ docker run --rm -i -d -p8080:8080 -v `pwd`/secret.key:/app/.jdbcx/secret.key jdbcx/jdbcx -v `pwd`/mysql1.properties:/app/.jdbcx/db/mysql1.properties server
$ curl -s -d 'select * from {{table.db.mysql1: show processlist}}' 'http://localhost:8080/query'

For server authentication, please refer to here.

Performance

Test Environment

• JDK: openjdk version "17.0.7" 2023-04-18
• Tool: Apache JMeter 5.6.2
• Database: ClickHouse 22.8
• JDBC Driver: clickhouse-jdbc v0.4.6

Test Configuration

• Concurrent Users: 20
• Loop Count: 1000
• Connection Pool:
  • Size: 30
  • Init SQL and Validation Query are identical

Test Results

| Connection | Init SQL | Test Query | Avg Response Time (ms) | Max Response Time (ms) | Throughput (qps) | | ----------------- | -------------------------------------------- | ------------------------------------------------ | ---------------------- | ---------------------- | ---------------- | | jdbc:ch | select * from system.numbers limit 1 | select * from system.numbers limit 50000 | 69 | 815 | 279.87 | | jdbcx:ch | select * from system.numbers limit 1 | select * from system.numbers limit 50000 | 71 | 891 | 272.99 | | jdbcx:script:ch | 'select * from system.numbers limit 1' | 'select * from system.numbers limit ' + 50000 | 72 | 1251 | 270.65 | | jdbcx:shell:ch | echo 'select * from system.numbers limit 1' | echo 'select * from system.numbers limit 50000' | 91 | 650 | 214.45 | | jdbcx:prql:ch | from `system.numbers` | take 1 | from `system.numbers` | take 50000 | 106 | 1103 | 184.27 |