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.
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| Feature | Examples |
| Chained query |
-- 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")
}} |
| Dynamic query |
-- 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 |
| Multi-language query |
{% var: num=3 %}
select {{ script: ${num} - 2 }} one,
{{ shell: echo 2 }} two,
{{ db.ch-play: select ${num} }} three |
| Query substitution |
{% 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 |
| Scripting |
-- 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()
]
]
)
}} |
| # | 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+ | - |
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.
- 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
- Concurrent Users: 20
- Loop Count: 1000
- Connection Pool:
- Size: 30
- Init SQL and Validation Query are identical
| 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 |