JDBC

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

前置准备

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

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

连接 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>