文档中心

云数据库ClickHouse可通过JDBC进行连接。

本节以JAVA语言为示例演示JDBC连接云数据库ClickHouse。

添加依赖

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1</version>
</dependency>复制

以下代码演示了如何使用 JDBC 驱动连接实例。示例代码包含五个方法，init方法获取数据库连接，close方法关闭连接，insertData方法插入数据，selectData表示查询数据，conditionsQuery为条件查询示例。

import org.junit.jupiter.api.Test;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.UUID;

public class SomeTest {

    // 数据库连接地址
    public static final String URL = "jdbc:clickhouse://IP:PORT/default";

    // 数据库账户名
    public static final String USER = "USERNAME";

    // 数据库账户密码
    public static final String PASSWORD = "PASSWORD";

    public static Connection connection = null;

    // 初始化
    public static Connection init() throws SQLException {
        ClickHouseProperties props = new ClickHouseProperties();
        props.setConnectionTimeout(600);
        props.setSessionId(UUID.randomUUID().toString());
        props.setUser(USER);
        props.setPassword(PASSWORD);
        props.setMaxExecutionTime(300);
        ClickHouseDataSource dataSource = new ClickHouseDataSource(URL, props);
        return dataSource.getConnection();
    }

    public static void close() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void createDatabase() {
        // 如果是集群创建数据库需要加上 ON CLUSTER 集群名
        try {
            connection = init();
            ResultSet rs = null;
            boolean result = connection.createStatement().execute( "create database test;");
            System.out.println( "result:"+result);
            rs = connection.createStatement().executeQuery( "SHOW databases;");
            while (rs.next()) {
                String name = rs.getString( "name");
                System.out.println(name);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }

    }

    @Test
    public void createTable() {
        ResultSet rs = null;
        try {
            connection = init();
            boolean result = connection.createStatement().execute( "CREATE TABLE IF NOT EXISTS test.demo_local \\ "+
                            " (ts_date Date, ts_date_time DateTime, user_id Int64, event_type String, site_id Int64) \\ " +
                    " ENGINE = MergeTree() PARTITION BY ts_date \\ " +
                    " ORDER BY (ts_date, toStartOfHour(ts_date_time), site_id, event_type) \\ " +
                    " SETTINGS index_granularity = 8192;");
            System.out.println( " create table success !" +result);
            rs = connection.createStatement().executeQuery( "SELECT COUNT () FROM test.demo_local ");
            while (rs.next()) {
                Long count = rs.getLong( "COUNT()");
                System.out.println(count);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void selectData() throws SQLException {
        ResultSet rs = null;
        try {
            connection = init();
            rs = connection.createStatement().executeQuery( "select * from test.demo_local;");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            System.out.println( "总列数：" +columnCount);
            int rowCont = 0;
            while (rs.next()) {
                String id = rs.getString( "CounterID");
                String create_time = rs.getString( "StartDate");
                String comment = rs.getString( "UserID");
                System.out.println( " CounterID: " +id + ",StartDate: " +create_time + " ,UserID: " +comment);
                rowCont++;
            }
            System.out.println( "总行数：" + rowCont);
        } finally {
            if (rs != null) {
                rs.close();
            }
            close();
        }
    }

    @Test
    public void insertData() throws SQLException {
        ResultSet rs = null;
        try {
            connection = init();
            PreparedStatement pstmt = connection.prepareStatement( "INSERT INTO test.demo_local(ts_date, \\ " +
                    " ts_date_time, user_id, event_type, site_id)VALUES( ?, ?, ?, ? ,? )");

            for (int i = 0; i < 20 ; i++){
                pstmt.setDate(1, new Date(System.currentTimeMillis() - i * 86400_000));
                pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
                pstmt.setLong(3, i * 1000L);
                pstmt.setString(4, "event type" + i);
                pstmt.setLong(5, i * 10 + 1000L);
                pstmt.addBatch();
            }
            int[] result = pstmt.executeBatch();
            System.out.println( "executeBatch result : " +Arrays.toString(result));

            rs = connection.createStatement().executeQuery( "select * from test.demo_local;");
            int rowCont = 0;
            while (rs.next()) {
                Date tsDate = rs.getDate( "ts_date");
                Timestamp tsDateTime = rs.getTimestamp( "ts_date_time");
                Long userId = rs.getLong( "user_id ");
                String eventType = rs.getString( "event_type");
                Long siteId = rs.getLong( "site_id");
                System.out.println( " tsDate: " + tsDate.toString() + ",tsDateTime: " +tsDateTime.toString()
                        + " ,userId: " +userId  + ",eventType: " +eventType + " ,siteId: " +siteId);
                rowCont++;
            }
            System.out.println( " 总行数： " +rowCont);
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    @Test
    public void conditionsQuery() throws SQLException {
        ResultSet rs = null;
        try {
            connection = init();
            String queryStr = " SELECT ts_date AS ts, AVG (user_id) AS userIdAvg FROM test.demo_local WHERE ts_date \\"
                + " BETWEEN '2020-09-24' AND '2020-10-12' GROUP BY ts; ";
            rs = connection.createStatement().executeQuery(queryStr);
            while (rs.next()) {
                Date ts = rs.getDate( "ts");
                Long userIdAvg = rs.getLong( "userIdAvg");
                System.out.println( " ts: " +ts.toString() + ",userIdAvg: " +userIdAvg);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            close();
        }
    }
}