一、基础前提

已经安装好了ClickHouse数据库（不懂得如何安装可以看我上一篇文章《单机版ClickHouse部署快速入门》），然后在default数据库中，创建一张表：

CREATE TABLE user_info
(
    `id` UInt64,
    `user_name` String,
    `user_phone` String,
    `create_time` DateTime DEFAULT CAST(now(),'DateTime')
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id;

二、工程搭建及使用

新建一个名称为 springboot_clickhouse 的 SpringBoot 工程，结构如下：

首先看一下 pom.xml 文件中引入的依赖包：

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
 
    <groupId>com.jobs</groupId>
    <artifactId>springboot_clickhouse</artifactId>
    <version>1.0</version>
 
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.5</version>
        <relativePath/>
    </parent>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <scope>compile</scope>
        </dependency>
        <!--引入官网提供的clickhouse的jdbc依赖包-->
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.6.5</version>
        </dependency>
        <!--clickhouse的jdbc依赖，需要引入lz4依赖-->
        <dependency>
            <groupId>org.lz4</groupId>
            <artifactId>lz4-java</artifactId>
            <version>1.8.0</version>
        </dependency>
        <!--clickhouse的jdbc依赖，需要引入httpclient5依赖-->
        <dependency>
            <groupId>org.apache.httpcomponents.client5</groupId>
            <artifactId>httpclient5</artifactId>
            <version>5.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!--解决 IDEA 编写与配置项对应的实体类时，文件上方总是出现红色提示的问题-->
        <!--导入该依赖后，在编写配置文件时，如果用到了实体类的属性，会有智能提示-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.4.5</version>
            </plugin>
        </plugins>
    </build>
</project>

然后再看一下 application.yml 中有关 clickhouse 的访问链接配置，这里使用了阿里的 druid 连接池：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: com.clickhouse.jdbc.ClickHouseDriver
      url: jdbc:clickhouse://192.168.136.128:8123/default
      username: default
      password: 123456
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000
 
mybatis-plus:
  configuration:
    # 开启 sql 打印日志，输出的控制台，方便开发过程中查看 sql 执行细节
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

其中 click 是我们自己编写的自定义配置，因此我们需要建立这些配置参数与 druid 之间的关联，首先建立实体类与配置进行关联：

package com.jobs.config;
 
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
 
@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private Integer initialSize;
    private Integer maxActive;
    private Integer minIdle;
    private Integer maxWait;
}

然后使用实体类的属性值（SpringBoot 启动后会使用配置文件的内容给实体类初始化值），配置 durid 连接池参数：

package com.jobs.config;
 
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 JdbcParamConfig jdbcParamConfig ;
 
    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(jdbcParamConfig.getUrl());
        datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());
        datasource.setInitialSize(jdbcParamConfig.getInitialSize());
        datasource.setMinIdle(jdbcParamConfig.getMinIdle());
        datasource.setMaxActive(jdbcParamConfig.getMaxActive());
        datasource.setMaxWait(jdbcParamConfig.getMaxWait());
        datasource.setUsername(jdbcParamConfig.getUsername());
        datasource.setPassword(jdbcParamConfig.getPassword());
        return datasource;
    }
}

剩下的就是 mybatis 相关的代码了，首先根据数据库表字段，编写实体类：

package com.jobs.entity;
 
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserInfo {
    @TableId
    @TableField("id")
    private Long id;
 
    @TableField("user_name")
    private String userName;
 
    @TableField("user_phone")
    private String userPhone;
 
    @TableField("create_time")
    private String createTime;
}

根据实体类编写 mapper 数据库访问代码：

package com.jobs.mapper;
 
import com.jobs.entity.UserInfo;
import org.apache.ibatis.annotations.*;
import java.util.List;
 
@Mapper
public interface UserInfoMapper {
 
    //添加用户
    @Insert("insert into user_info(id,user_name,user_phone) values(#{id},#{userName},#{userPhone})")
    void addUser(UserInfo userInfo);
 
    //根据 id 获取用户（clickhouse 的主键允许重复，所以这里获取查询到的第一条数据）
    @Select("select id,user_name,user_phone,create_time from user_info where id=#{id} limit 1")
    UserInfo selectById(@Param("id") Long id);
 
    //查询所有用户（这里就不展示分页查询了，自己可以使用 mybatis plus 进行实现）
    @Select("select id,user_name,user_phone,create_time from user_info order by id")
    List<UserInfo> selectList();
 
    //修改用户（对于 clickhouse 来说，修改操作是比较重的操作，最好是大批量的修改，不要逐条修改）
    @Update("ALTER TABLE user_info update user_name=#{userName},user_phone=#{userPhone} where id=#{id}")
    void updateUser(UserInfo userInfo);
 
    //删除用户（对于 clickhouse 来说，删除操作是比较重的操作，最好是大批量的删，不要逐条删除）
    @Delete("ALTER TABLE user_info delete where id=#{id}")
    void deleteUser(@Param("id") Long id);
}

根据 mapper 编写 service 代码，这里图省事，没有写接口，直接写 service 类的实现了：

package com.jobs.service;
 
import com.jobs.entity.UserInfo;
import com.jobs.mapper.UserInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
//mybatis plus 要求给方法上增加 @Transactional 注解，否则日志中总是会出现以下内容：
//SqlSession was not registered for synchronization because synchronization is not active
@Transactional
@Service
public class UserInfoService {
 
    @Autowired
    UserInfoMapper userInfoMapper;
 
    public void addUser(UserInfo userInfo) {
        userInfoMapper.addUser(userInfo);
    }
 
    public UserInfo selectById(Long id) {
        return userInfoMapper.selectById(id);
    }
 
    //@Transactional(rollbackFor = Exception.class)
    public List<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
 
    public void updateUser(UserInfo userInfo) {
        userInfoMapper.updateUser(userInfo);
    }
 
    public void deleteUser(Long id) {
        userInfoMapper.deleteUser(id);
    }
}

由于本 demo 只是一个示例，不想写接口了，就直接编写测试代码，测试 service 中的方法了：

package com.jobs;
 
import com.jobs.entity.UserInfo;
import com.jobs.service.UserInfoService;
import com.sun.xml.internal.ws.policy.privateutil.PolicyUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.CollectionUtils;
 
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.List;
import java.util.Objects;
 
@SpringBootTest
public class UserInfoTest {
 
    @Autowired
    private UserInfoService userInfoService;
 
    @Test
    void test1() {
        UserInfo userInfo1 = new UserInfo(1L, "候胖胖", "1234567", "2024-09-22 19:00:00");
        UserInfo userInfo2 = new UserInfo(2L, "任肥肥", "2345678", "2024-09-22 19:01:12");
        UserInfo userInfo3 = new UserInfo(3L, "李墩墩", "3456789", "2024-09-22 19:02:23");
        UserInfo userInfo4 = new UserInfo(4L, "杨棒棒", "4567890", "2024-09-22 19:03:35");
        UserInfo userInfo5 = new UserInfo(5L, "乔豆豆", "5678901", "2024-09-22 19:05:26");
 
        userInfoService.addUser(userInfo1);
        userInfoService.addUser(userInfo2);
        userInfoService.addUser(userInfo3);
        userInfoService.addUser(userInfo4);
        userInfoService.addUser(userInfo5);
 
        System.out.println("添加成功");
    }
 
    @Test
    void test2() {
        Long id = 2L;
        UserInfo userInfo = userInfoService.selectById(id);
        if (!Objects.isNull(userInfo)) {
            System.out.println(userInfo);
        } else {
            System.out.println("未查询到数据");
        }
    }
 
    @Test
    void test3() {
        List<UserInfo> userInfos = userInfoService.selectList();
        if (!CollectionUtils.isEmpty(userInfos)) {
            for (UserInfo userInfo : userInfos) {
                System.out.println(userInfo);
            }
        } else {
            System.out.println("未查询到数据");
        }
    }
 
    @Test
    void test4() {
        Long id = 2L;
        UserInfo userInfo = userInfoService.selectById(id);
        if (!Objects.isNull(userInfo)) {
            userInfo.setUserName("马壮壮");
            userInfo.setUserPhone("6666777");
            userInfoService.updateUser(userInfo);
            System.out.println("修改成功");
        } else {
            System.out.println("未查询到数据，无法修改");
        }
    }
 
    @Test
    void test5() {
        Long id = 2L;
        userInfoService.deleteUser(id);
        System.out.println("删除成功");
    }
}