Java操作ClickHouse数据库的完整教程

<!-- Maven -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.6</version> <!-- 请替换为最新稳定版本 -->
    <classifier>http</classifier> <!-- 如果使用HTTP协议连接 -->
</dependency>

// Gradle
implementation 'com.clickhouse:clickhouse-jdbc:0.4.6:http' // 同样，请替换为最新稳定版本

import java.sql.*;

public class ClickHouseJdbcDemo {

    private static final String JDBC_URL = "jdbc:clickhouse://localhost:8123/default";
    private static final String USER = "default";
    private static final String PASSWORD = ""; // 你的密码，如果没有就留空

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            System.out.println("成功连接到ClickHouse！");

            // 创建表
            createTable(connection);

            // 插入数据
            insertData(connection);

            // 查询数据
            queryData(connection);

            // 删除数据 (ClickHouse的删除操作比较特殊，是ALTER TABLE ... DELETE)
            deleteData(connection);

        } catch (SQLException e) {
            System.err.println("连接或操作ClickHouse时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void createTable(Connection connection) throws SQLException {
        String createSql = "CREATE TABLE IF NOT EXISTS my_test_table (id UInt64, name String, event_time DateTime) ENGINE = MergeTree() ORDER BY id";
        try (Statement statement = connection.createStatement()) {
            statement.execute(createSql);
            System.out.println("表 'my_test_table' 创建或已存在。");
        }
    }

    private static void insertData(Connection connection) throws SQLException {
        String insertSql = "INSERT INTO my_test_table (id, name, event_time) VALUES (?, ?, ?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
            // 批量插入是ClickHouse的王道，单条插入效率不高
            for (int i = 0; i < 1000; i++) {
                preparedStatement.setLong(1, i);
                preparedStatement.setString(2, "用户_" + i);
                preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                preparedStatement.addBatch();
            }
            int[] results = preparedStatement.executeBatch();
            System.out.println("成功插入 " + results.length + " 条数据。");
        }
    }

    private static void queryData(Connection connection) throws SQLException {
        String querySql = "SELECT id, name, event_time FROM my_test_table WHERE id < 10 LIMIT 5";
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(querySql)) {
            System.out.println("\n查询结果:");
            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                Timestamp eventTime = resultSet.getTimestamp("event_time");
                System.out.println("ID: " + id + ", 姓名: " + name + ", 时间: " + eventTime);
            }
        }
    }

    private static void deleteData(Connection connection) throws SQLException {
        // ClickHouse的DELETE是异步的ALTER TABLE操作，不是传统意义上的行级删除
        String deleteSql = "ALTER TABLE my_test_table DELETE WHERE id < 5";
        try (Statement statement = connection.createStatement()) {
            statement.execute(deleteSql);
            System.out.println("\n已发起删除ID小于5的数据请求 (异步执行)。");
            // 实际数据删除可能需要一些时间，取决于ClickHouse的MergeTree引擎后台合并
        }
    }
}

import java.sql.*;
import java.util.Arrays;
import java.util.List;

// ... (假设你已经有了Connection对象)

private static void insertArrayData(Connection connection) throws SQLException {
    String insertSql = "INSERT INTO my_array_table (id, tags) VALUES (?, ?)";
    try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
        // 创建表（如果不存在）
        String createTableSql = "CREATE TABLE IF NOT EXISTS my_array_table (id UInt64, tags Array(String)) ENGINE = MergeTree() ORDER BY id";
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(createTableSql);
        }

        preparedStatement.setLong(1, 1L);
        List<String> tags1 = Arrays.asList("Java", "ClickHouse", "BigData");
        preparedStatement.setObject(2, tags1); // 使用setObject来设置List
        preparedStatement.addBatch();

        preparedStatement.setLong(1, 2L);
        List<String> tags2 = Arrays.asList("SQL", "Database");
        preparedStatement.setObject(2, tags2);
        preparedStatement.addBatch();

        int[] results = preparedStatement.executeBatch();
        System.out.println("成功插入 " + results.length + " 条包含数组的数据。");
    }
}

// ... (假设你已经有了Connection对象)

private static void queryArrayData(Connection connection) throws SQLException {
    String querySql = "SELECT id, tags FROM my_array_table WHERE id > 0 LIMIT 2";
    try (Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery(querySql)) {
        System.out.println("\n查询数组类型数据:");
        while (resultSet.next()) {
            long id = resultSet.getLong("id");
            Array sqlArray = resultSet.getArray("tags");
            if (sqlArray != null) {
                // 将SQL Array转换为Java String数组
                String[] tagsArray = (String[]) sqlArray.getArray();
                System.out.println("ID: " + id + ", 标签: " + Arrays.toString(tagsArray));
                // 或者转换为List
                // List<String> tagsList = Arrays.asList((String[]) sqlArray.getArray());
            } else {
                System.out.println("ID: " + id + ", 标签: null");
            }
        }
    }
}

// 示例：更细致的异常处理
try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
    // ... 数据库操作
} catch (SQLTransientException e) {
    // 瞬时错误，比如网络抖动、连接池暂时耗尽，可以考虑重试
    System.err.println("瞬时错误，尝试重试: " + e.getMessage());
    // retryLogic();
} catch (SQLSyntaxErrorException e) {
    // SQL语法错误，通常是代码问题，需要人工介入
    System.err.println("SQL语法错误，请检查SQL语句: " + e.getMessage());
} catch (SQLException e) {
    // 其他SQL错误
    System.err.println("数据库操作通用错误: " + e.getMessage() + ", SQLState: " + e.getSQLState() + ", ErrorCode: " + e.getErrorCode());
    // Log error, notify ops, etc.
}