Springboot集成clickhouse jdbc方式-CSDN博客

本文链接：https://blog.csdn.net/m0_47570899/article/details/135289990

本文介绍了如何在SpringBoot应用中通过Druid和ClickHouseJDBC驱动部署ClickHouse数据库，包括添加依赖、配置Bean以及使用JdbcTemplate进行SQL查询的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

部署方式也非常简单,直接上干货,附上用法,如果要在本地安装ck数据库的话看我上一篇文章

一.依赖


<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<!--druid-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
</dependency>
<!--ck-->
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.6</version>
</dependency>

二.配置

1)配置Bean

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
@Data
public class ClickhouseConfig {
    /**
     * enabled: true
     * driverClassName: ru.yandex.clickhouse.ClickHouseDriver
     * url: jdbc:clickhouse://192.168.0.40:8123/xx?max_result_bytes=10000
     * username: xxx
     * paswword: xxx
     * initialSize: 10
     * validationQuery: SELECT 1
     * maxActive: 100
     * minIdle: 10
     * maxWait: 6000
     */
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private Integer initialSize;
    private Integer minIdle;
    private Integer maxActive;
    private Integer maxWait;
    private String validationQuery;

    @Value("${spring.clickhouse.driverClassName}")
    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    @Value("${spring.clickhouse.url}")
    public void setUrl(String url) {
        this.url = url;
    }

    @Value("${spring.clickhouse.username}")
    public void setUsername(String username) {
        this.username = username;
    }

    @Value("${spring.clickhouse.password}")
    public void setPassword(String password) {
        this.password = password;
    }

    @Value("${spring.clickhouse.initialSize}")
    public void setInitialSize(Integer initialSize) {
        this.initialSize = initialSize;
    }

    @Value("${spring.clickhouse.minIdle}")
    public void setMinIdle(Integer minIdle) {
        this.minIdle = minIdle;
    }

    @Value("${spring.clickhouse.maxActive}")
    public void setMaxActive(Integer maxActive) {
        this.maxActive = maxActive;
    }

    @Value("${spring.clickhouse.maxWait}")
    public void setMaxWait(Integer maxWait) {
        this.maxWait = maxWait;
    }

    @Value("${spring.clickhouse.validationQuery}")
    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    @Bean(name = "clickhouseDataSource")
    public DataSource clickhouseDataSource() {
        DruidDataSource ck = new DruidDataSource();
        ck.setDbType(driverClassName);
        ck.setUrl(url);
        ck.setUsername(username);
        ck.setPassword(password);
        ck.setInitialSize(initialSize);
        ck.setMinIdle(minIdle);
        ck.setMaxActive(maxActive);
        ck.setMaxWait(maxWait);
        ck.setValidationQuery(validationQuery);
        return ck;
    }

    @Bean(name = "clickhouseTemplate")
    public JdbcTemplate clickhouseTemplate(@Qualifier("clickhouseDataSource") DataSource clickhouseDataSource) {
        JdbcTemplate ck = new JdbcTemplate(clickhouseDataSource);
        ck.setResultsMapCaseInsensitive(true);
        return ck;
    }

2)配置文件

    driverClassName: ru.yandex.clickhouse.ClickHouseDriver
    url: jdbc:clickhouse://192.168.1.10:8123/default?max_result_bytes=10485760
    username: default
    password: 
    initialSize: 10
    validationQuery: SELECT 1
    maxActive: 100
    minIdle: 10
    maxWait: 6000

三.使用

@Resource(name = "clickhouseTemplate")
private JdbcTemplate clickhouseTemplate;

@Test
public void test1() {
    String sql="SELECT * FROM user";
    List<Map<String,Object>> result= clickhouseTemplate.queryForList(sql);

    //条件转对象写法
    List<User> users = clickhouseTemplate.query(sql, preparedStatement -> {
        preparedStatement.setInt(1, 1); //参数1  sql中的动态参数用 ? 占位符 例如:SELECT * FROM user where platform = ? 
        preparedStatement.setString(2, "2");//参数2
    }, (resultSet, i) -> {
        User user = new User();
        user.setId(resultSet.getString("id"));
        user.setAge(resultSet.getString("age"));
        user.setName(resultSet.getString("name"));
        return user;
    });
    //转对象写法
    List<User> users = clickhouseTemplate.query(sql, (resultSet, i) -> {
        User user = new User();
        user.setId(resultSet.getString("id"));
        user.setAge(resultSet.getString("age"));
        user.setName(resultSet.getString("name"));
        return user;
    });
    System.out.println(result);
}