springBoot集成ClickHouse

在上一节中Linux中安装clickhouse

我们安装好了Clickhouse，接下来我们就要集成到项目中。

一：在Ck中新建一个表

create table user
(
    userId  Int32,
    appId   String,
    version String,
    regTime Date
)
    engine = MergeTree PARTITION BY toYYYYMM(regTime) ORDER BY userId SETTINGS index_granularity = 8192;
    
INSERT INTO default.user (userId, appId, version, regTime) VALUES (1, 'RR', '3.6', '2022-08-26');

这样后面就可以新增数据。由于其不擅长按行删除数据虽然支持，不支持数据修改操作，也不支持事务（想要快就要舍弃事务处理）。适合做OLAP数据分析。所以我们主要用于查询和新增。接下来就是整个过程。

二：引入jar包

  <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.53</version>
        </dependency>

三：准备配置文件

单独写一个配置文件，不过需要在application.properties中添加引用。

spring.profiles.include=clickhouse

准备一个application-clickhouse.properties的配置文件

//连接池
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

//ck连接
clickhouse.driverClassName=ru.yandex.clickhouse.ClickHouseDriver
clickhouse.url=jdbc:clickhouse://ip:8123
clickhouse.username=default
clickhouse.password=password


clickhouse.db=system
clickhouse.socketTimeout=600000
clickhouse.initialSize=10
clickhouse.maxActive=100
clickhouse.minIdle=10
clickhouse.maxWait=6000

//mapper

mybatis.mapper-locations=classpath:mapping/mysql/*Mapper.xml
mybatis.type-aliases-package=com.zhm.entity

四：数据源配置

先写一个配置po

package com.zhm.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;

/**
 * @create 2022/8/26 14:43
 */
@Component
@PropertySource("classpath:application-clickhouse.properties")
public class ChParam {
    private String driverClassName;
    private String url;
    private String password;
    private Integer initialSize;
    private Integer maxActive;
    private Integer minIdle;
    private Integer maxWait;

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

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

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

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

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

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

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


    public String getDriverClassName() {
        return driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public String getPassword() {
        return password;
    }

    public Integer getInitialSize() {
        return initialSize;
    }

    public Integer getMaxActive() {
        return maxActive;
    }

    public Integer getMinIdle() {
        return minIdle;
    }

    public Integer getMaxWait() {
        return maxWait;
    }
}

在写一个DruidConfig

package com.zhm.config;

/**
 * @create 2022/8/26 14:46
 */
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {

    @Autowired
    private ChParam chParam;

    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(chParam.getUrl());
        datasource.setDriverClassName(chParam.getDriverClassName());
        datasource.setInitialSize(chParam.getInitialSize());
        datasource.setMinIdle(chParam.getMinIdle());
        datasource.setMaxActive(chParam.getMaxActive());
        datasource.setMaxWait(chParam.getMaxWait());
        datasource.setPassword(chParam.getPassword());
        return datasource;
    }
}

五：准备POJO

由于一中已经确定了表名和列名所以pojo就可以确定。而且三：准备配置文件 中也确定了其位置

mybatis.type-aliases-package=com.zhm.entity
所以需要在com.zhm.entity下创建一个pojo

package com.zhm.entity;

import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;

/**
 * @create 2022/8/26 14:52
 */
public class ClickUser {
    private Integer userId;

    private String appId;

    private String version;
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date regTime;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getAppId() {
        return appId;
    }

    public void setAppId(String appId) {
        this.appId = appId;
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }

    public Date getRegTime() {
        return regTime;
    }

    public void setRegTime(Date regTime) {
        this.regTime = regTime;
    }
}

六：准备Mapper

由于在三的配置文件中已经确定了引用位置

mybatis.mapper-locations=classpath:mapping/mysql/*Mapper.xml

所以 需要把Mapper.xml放到resource下的mapping/mysql/目录下。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhm.mapper.ClickUserMapper">

    <select id="queryUser"  resultType="com.zhm.entity.ClickUser">
        select userId, appId, version, regTime from `default`.`user`

    </select>
    <insert id="insertUser" parameterType="com.zhm.entity.ClickUser">
        INSERT INTO `default`.`user` (`userId`, `appId`, `version`, `regTime`)
        VALUES (#{userId}, #{appId}, #{version}, #{regTime})
    </insert>

</mapper>

有了mapper.xml必然有mapper

import com.zhm.entity.ClickUser;

import java.util.List;

/**
 * @create 2022/8/26 14:47
 */
public interface ClickUserMapper {
    List<ClickUser> queryUser();

    void insertUser(ClickUser clickUser);

}

七：使用

先准备一个service

/**
 * @create 2022/8/5 9:35
 */
@Service
public class ChickHouserService {

    private static final Logger logger =LoggerFactory.getLogger(ChickHouserService.class);

    @Resource
    private ClickUserMapper clickUserMapper;



    public Result getMapperData(ClickUser clickUser) {
        logger.info("进入操作");
        try {
            logger.info("===========先查询============");
            List<ClickUser> list= clickUserMapper.queryUser();
            logger.info("clickhouse查询结果为：{}",JSONObject.toJSON(list));
            logger.info("===========在新增============");
            clickUserMapper.insertUser(clickUser);
            logger.info("===========再次查询============");
            List<ClickUser> rlist= clickUserMapper.queryUser();
            logger.info("clickhouse查询结果为：{}",JSONObject.toJSON(rlist));
            return Result.sendSuccess("操作结束");
        } catch (Exception e) {
            logger.error("操作异常，{}", e);
            return Result.sendFailure("操作异常");
        }
    }

}

最后写一个通用的controller

  @Resource
    private ChickHouserService chickHouserService ;

    @ApiOperation(value = "处理表里的数据，标记去年的数据今年有没有买")
    @RequestMapping(value = "/checkLastData", method = RequestMethod.POST)
    public Result index(@RequestBody ClickUser clickUser) {
        return chickHouserService .getMapperData(clickUser);
    }

这样整个就完成了springBoot集成clickhouse了