JDBCX enhances the JDBC driver by supporting additional data formats, compression algorithms, object mapping, type conversion, and query languages beyond SQL. It simplifies complex federated queries with dynamic query embedding and remote bridge server connectivity for multiple data sources.
Getting started with JDBCX is easy. You can use JDBCX driver, bridge server, or both.
| 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 | Scripting does not work on DBeaver | use JDK instead of JRE |
| 3 | Connection pooling is not supported | - |
| 4 | Multi-ResultSet is not fully supported | - |
| 5 | Nested query is not supported | - |
- 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 |