使用idea springboot操作clickhouse数据库

基于大数据分析，本案例为测试案例，真实工程基于此原型设计，下述文章是安装过程

https://blog.csdn.net/m0_37942145/article/details/118194835

1.pom配置核心配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ckhouse</groupId>
    <artifactId>springclickhouse</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springclickhouse</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <!-- clickHouse数据库 -->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.53</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.层级设计

3.application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
clickhouse:
  driverClassName: ru.yandex.clickhouse.ClickHouseDriver
  url: jdbc:clickhouse://192.168.2.243:8123/default
  password: 123456
  initialSize: 10
  maxActive: 100
  minIdle: 10
  maxWait: 6000
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.ckhouse.springclickhouse.pojo
server:
  port: 8090

4.ChParam

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Data
@Component
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;
    }
}

5.DruidConfig

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;
    }
}

6.UserMapper

<?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.ckhouse.springclickhouse.Dao.UserMapper">

    <select id="queryUser" resultType="com.ckhouse.springclickhouse.pojo.User">
        select userId, appId, version, regTime from `default`.`user`
    </select>

    <insert id="insertUser" parameterType="com.ckhouse.springclickhouse.pojo.User">
         INSERT INTO `default`.`user` (`userId`, `appId`, `version`, `regTime`)
         VALUES (#{userId}, #{appId}, #{version}, #{regTime})
    </insert>
</mapper>

7.UserController

import com.ckhouse.springclickhouse.Dao.UserMapper;
import com.ckhouse.springclickhouse.pojo.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;
import java.util.List;

@Slf4j
@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/queryUser")
    public Object query(){
        List userList = userMapper.queryUser();
        log.info(userList.toString());
        return userList.toString();
    }
}

}

8.UserMapper

import com.ckhouse.springclickhouse.pojo.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    List queryUser();

    Integer insertUser(User user);

}

9.POJO

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer userId;

    private String appId;

    private String version;

    private Date regTime;
}

10运行DBeaver

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 (123457, 'RR', '3.6', '2020-01-07');
INSERT INTO default.user (userId, appId, version, regTime) VALUES (43234, 'HH', '2.5', '2020-06-06');
INSERT INTO default.user (userId, appId, version, regTime) VALUES (1234, 'TT', '2.4', '2020-07-24');
INSERT INTO default.user (userId, appId, version, regTime) VALUES (12345, 'RR', '2.5', '2020-07-29');
INSERT INTO default.user (userId, appId, version, regTime) VALUES (123456, 'TT', '2.1', '2020-07-09');
INSERT INTO default.user (userId, appId, version, regTime) VALUES (234561, 'GG', '3.0', '2020-07-31');

11.请求后台

本文档参考下述文档，在下述描述进一步细化，基本复制就行

下期tidb测试版本

https://blog.csdn.net/flyfeifei66/article/details/87983762

https://blog.csdn.net/weixin_39025362/article/details/107715706