You need to enable JavaScript to run this app.
导航
JDBC
最近更新时间:2023.11.16 11:58:47首次发布时间:2022.12.22 10:16:15

JAVA 应用可以通过 ClickHouse JDBC Driver 与 ClickHouse 进行交互。
图片

前置准备

  1. 系统安装有 Java 环境。
  2. 下载 ClickHouse 官方 JDBC 驱动: https://github.com/ClickHouse/clickhouse-java

注意

在使用 ByteHouse BitMap64 数据类型时,需要安装ByteHouse JDBC专用补丁,详见后文。

  1. 您可以参考获取集群连接信息来查看相关集群的连接信息。

连接 ByteHouse

创建一个 HelloClickHouse.java,将以下代码贴入,即可连接 ByteHouse 并进行一个简单查询:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 */
public class SimpleQuery {

  public static void main(String[] args) throws Exception {
    Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
    Connection connection = DriverManager.getConnection("jdbc:clickhouse://<host>:<ip>", <username>, <password>);
    //<host>:<ip> 替换为 ByteHouse 节点的服务的地址和端口, <username>替换为用户名,<password>替换为密码
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT (number % 3 + 1) as n, sum(number) FROM numbers(10000000) GROUP BY n");

    while (rs.next()) {
      System.out.println(rs.getInt(1) + "\t" + rs.getLong(2));
    }
  }

以下示例则拼接了一系列复杂 SQL:

package examples;

import java.sql.*;

/**
 * BatchQuery
 */
public class BatchQuery {
    public static void main(String[] args) throws Exception {
        try (Connection connection = DriverManager.getConnection("jdbc:clickhouse://<host>:<port>", username, password)) {
         //<host>:<ip> 替换为 ByteHouse 节点的服务的地址和端口, usernmae 替换为用户名, password 替换为密码
            try (Statement stmt = connection.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("drop table if exists test_jdbc_example")) {
                    System.out.println(rs.next());
                }
                try (ResultSet rs = stmt.executeQuery("create table test_jdbc_example(day Date, name String, age UInt8) Engine=Log")) {
                    System.out.println(rs.next());
                }
                try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO test_jdbc_example VALUES(?, ?, ?)")) {
                    for (int i = 1; i <= 200; i++) {
                        pstmt.setDate(1, new Date(System.currentTimeMillis()));
                        if (i % 2 == 0)
                            pstmt.setString(2, "Zhang San" + i);
                        else
                            pstmt.setString(2, "Zhang San");
                        pstmt.setByte(3, (byte) ((i % 4) * 15));
                        System.out.println(pstmt);
                        pstmt.addBatch();
                    }
                    pstmt.executeBatch();
                }

                try (PreparedStatement pstmt = connection.prepareStatement("select count(*) from test_jdbc_example where age>? and age<=?")) {
                    pstmt.setByte(1, (byte) 10);
                    pstmt.setByte(2, (byte) 30);
                    printCount(pstmt);
                }

                try (PreparedStatement pstmt = connection.prepareStatement("select count(*) from test_jdbc_example where name=?")) {
                    pstmt.setString(1, "Zhang San");
                    printCount(pstmt);
                }
                try (ResultSet rs = stmt.executeQuery("drop table test_jdbc_example")) {
                    System.out.println(rs.next());
                }
            }
        }
    }

    public static void printCount(PreparedStatement pstmt) throws SQLException {
        try (ResultSet rs = pstmt.executeQuery()) {
            System.out.println(pstmt);
            if (rs.next())
                System.out.println(rs.getInt(1));
        }
    }
}

BitMap64 JDBC 驱动补丁

在使用官方 JDBC 0.3.2-patch11 连接 ByteHouse 企业版 2.1.0 执行查询时,可能会产生报错:java.sql.SQLException: Unknown data type: BitMap64,此时需要安装驱动补丁来保障程序正常运行:

  1. 下载 ByteHouse JDBC 补丁程序:
    • 完整包(包含 JDBC + CLI + gRPC + HTTP 模块)
      clickhouse-jdbc-0.3.3-SNAPSHOT-ce-patch-v1-all.jar
      未知大小
    • 单独包(仅 JDBC 模块)
      clickhouse-jdbc-0.3.3-SNAPSHOT-ce-patch-v1.jar
      未知大小
  2. 将文件拷贝到工程的src/main/resources/lib文件夹下。
  3. 以 maven 构建,在项目的 pom.xml 文件里添加下面依赖项。
<dependencies>
    <dependency>
        <groupId>0.3.2-patch11-ce-patch-v1</groupId>
        <artifactId>0.3.2-patch11-ce-patch-v1</artifactId>
        <version>0.3.2-patch11-ce-patch-v1</version>
        <scope>system</scope>
        <systemPath>${project.basedir}/src/main/resources/lib/clickhouse-jdbc-0.3.3-SNAPSHOT-ce-patch-v1-all.jar</systemPath>
        <classifier>all</classifier>
        <exclusions>
            <exclusion>
                <groupId>*</groupId>
                <artifactId>*</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <!-- 0.3.3-SNAPSHOT-ce-patch-v1 依赖 -->
        <groupId>org.roaringbitmap</groupId>
        <artifactId>RoaringBitmap</artifactId>
        <version>0.9.36</version>
    </dependency>
</dependencies>

注意

<systemPath>填写绝对路径,可以通过${project.basedir}函数辅助获得部分路径。