基于springboot操作clickhouse进行大数据查询和批量插入

注：此例子可直接进行现实具体业务功能处理

1.所需依赖

    ru.yandex.clickhouse
    clickhouse-jdbc
    0.1.53

sql表

-- test.bigtest definition
 
CREATE TABLE test.bigtest
(
 
    `id` Int64,
 
    `name` String,
 
    `age` String,
 
    `adress` String,
 
    `city` String,
 
    `habit` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192;

2.clickhouse环境配置及环境上下文类

spring.datasource.clickhouse.address=jdbc:clickhouse://127.0.0.1:8123
spring.datasource.clickhouse.username=default
spring.datasource.clickhouse.password=
spring.datasource.clickhouse.db=test
spring.datasource.clickhouse.socketTimeout=600000

clickhouse环境类

@Component
public class ClickHouseConfig {
    private static String clickhouseAddress;
 
    private static String clickhouseUsername;
 
    private static String clickhousePassword;
 
    private static String clickhouseDB;
 
    private static Integer clickhouseSocketTimeout;
 
    @Value("${spring.datasource.clickhouse.address}")
    public  void setClickhouseAddress(String address) {
        ClickHouseConfig.clickhouseAddress = address;
    }
    @Value("${spring.datasource.clickhouse.username}")
    public  void setClickhouseUsername(String username) {
        ClickHouseConfig.clickhouseUsername = username;
    }
    @Value("${spring.datasource.clickhouse.password}")
    public  void setClickhousePassword(String password) {
        ClickHouseConfig.clickhousePassword = password;
    }
    @Value("${spring.datasource.clickhouse.db}")
    public  void setClickhouseDB(String db) {
        ClickHouseConfig.clickhouseDB = db;
    }
    @Value("${spring.datasource.clickhouse.socketTimeout}")
    public  void setClickhouseSocketTimeout(Integer socketTimeout) {
        ClickHouseConfig.clickhouseSocketTimeout = socketTimeout;
    }
 
    public static Connection getConn() {
        ClickHouseConnection conn = null;
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(clickhouseUsername);
        properties.setPassword(clickhousePassword);
        properties.setDatabase(clickhouseDB);
        properties.setSocketTimeout(clickhouseSocketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress,properties);
        try {
            conn = clickHouseDataSource.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
        return null;
    }
    public static Connection getConnection() throws SQLException{
        //从数据源中获取数据库连接
        return getConn();
    }
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                //关闭存储查询结果的ResultSet对象
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
            try {
                //关闭负责执行SQL命令的Statement对象
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
 
        if (conn != null) {
            try {
                //将Connection连接对象还给数据库连接池
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
 
}

3.clickhouse查询案例

@Component
public class Clickhouse_Select {
    public static List<Map<String,String>> exeSql(String sql) throws SQLException {
        log.info("cliockhouse 执行sql：" + sql);
        List<Map<String, String>> results = new ArrayList>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = ClickHouseConfig.getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();// 执行查询数据库的SQL语句 .
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, String> map = new HashMap<String, String>();
                for(int r=1;r<colCount+1;r++)
                {
                    map.put(rsmd.getColumnName(r),rs.getString(rsmd.getColumnName(r)));
                }
                results.add(map);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            //释放资源
            ClickHouseConfig.release(conn, stmt, rs);
        }
        return results;
    }

4.插入clickhouse例子

public class Clickhouse_Insert {
    public static String insertsql(List<Map<String, String>> itemEle) {
        String  sql="INSERT INTO test.bigtest" +
                "(id, name, age, adress, city, habit)" +
                "VALUES(?, ?, ?, ?, ?, ?);";
        Connection conn = null;
        PreparedStatement stmt = null;
 
        try {
            conn = ClickHouseConfig.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < itemEle.size(); i++) {
                stmt.setInt(1,Integer.parseInt(itemEle.get(i).get("id")));
                stmt.setString(2,itemEle.get(i).get("name"));
                stmt.setString(3,itemEle.get(i).get("age"));
                stmt.setString(4,itemEle.get(i).get("adress"));
                stmt.setString(5,itemEle.get(i).get("city"));
                stmt.setString(6,itemEle.get(i).get("habit"));
                stmt.addBatch();
            }
            // 执行update
            stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ClickHouseConfig.release(conn, stmt, null);
        }
        return "1";
    }