在springboot中如何集成clickhouse進行讀寫操作

更新時間：2024年11月18日 09:41:08 作者：oNuoyi

本文介紹了在Spring Boot中集成ClickHouse的步驟,包括引入依賴、配置數(shù)據(jù)源、編寫實體類和Mapper類進行CRUD操作,特別提到批量插入時需要在SQL語句中添加`FORMAT`以避免錯誤,在實際應用中,與MySQL的操作類似,只需將ClickHouse當作MySQL使用

1、引入依賴

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--升級 druid驅動 1.1.10支持ClickHouse-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>

2、編寫數(shù)據(jù)源配置

@Configuration
public class DruidConfig {

    @Bean
    public DataSource dataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:clickhouse://localhost:8123/test");
        dataSource.setInitialSize(10);
        dataSource.setMaxActive(100);
        dataSource.setMinIdle(10);
        dataSource.setMaxWait(-1);
        return dataSource;
    }

}

3、編寫表實體類

和mysql一模一樣寫法，對應的表我通過DBeaver已經(jīng)創(chuàng)建好了

package cn.yufire.sync.sls.getway.logs.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;

import java.io.Serializable;

@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "getway_logs")
@Data
public class GetwayLogs implements Serializable {
    /**
     * id
     */
    @TableField(value = "id")
    private Long id;

    /**
     * 接口名稱：網(wǎng)關上定義的名稱
     */
    @TableField(value = "api_name")
    private String apiName;

    /**
     * 響應體
     */
    @TableField(value = "response_body")
    private String responseBody;

    /**
     * 調用環(huán)境：TEST、RELEASE、PRE
     */
    @TableField(value = "api_stage_name")
    private String apiStageName;

    /**
     * 錯誤碼
     */
    @TableField(value = "error_code")
    private String errorCode;

    /**
     * 請求類型
     */
    @TableField(value = "http_method")
    private String httpMethod;

    /**
     * 接口請求地址
     */
    @TableField(value = "api_path")
    private String apiPath;

    /**
     * 接口請求全地址
     */
    @TableField(value = "api_full_path")
    private String apiFullPath;

    /**
     * 請求時間
     */
    @TableField(value = "request_time")
    private String requestTime;

    /**
     * 請求體
     */
    @TableField(value = "request_body")
    private String requestBody;

    /**
     * 網(wǎng)關請求阿里云創(chuàng)建
     */
    @TableField(value = "getway_request_id")
    private String getwayRequestId;

    /**
     * 阿里云網(wǎng)關應用id
     */
    @TableField(value = "getway_app_id")
    private String getwayAppId;

    /**
     * 請求協(xié)議，HTTP、HTTPS、...
     */
    @TableField(value = "request_protocol")
    private String requestProtocol;

    /**
     * 客戶端調用產(chǎn)生的隨機字符串
     */
    @TableField(value = "client_nonce")
    private String clientNonce;

    /**
     * 網(wǎng)關應用名稱
     */
    @TableField(value = "getway_app_name")
    private String getwayAppName;

    /**
     * 網(wǎng)關分組id
     */
    @TableField(value = "getway_group_id")
    private String getwayGroupId;

    /**
     * 客戶端ip
     */
    @TableField(value = "client_ip")
    private String clientIp;

    /**
     * 網(wǎng)關綁定域名
     */
    @TableField(value = "getway_bind_domain")
    private String getwayBindDomain;

    /**
     * 請求體大小
     */
    @TableField(value = "request_size")
    private Integer requestSize;

    /**
     * 響應體大小
     */
    @TableField(value = "response_size")
    private Integer responseSize;

    /**
     * 后端應用響應HTTP狀態(tài)碼
     */
    @TableField(value = "app_response_code")
    private Integer appResponseCode;

    /**
     * 分組名稱
     * */
    @TableField(value = "apiGroupName")
    private String apiGroupName;
}

4、創(chuàng)建一個mapper類進行增刪改查

因為我使用clickhouse只需要批量插入，所以就寫了一個批量插入的sql，當然mybatis-plus也有自帶的批量插入的方法，但是不是一條sql執(zhí)行的，而是通過批量執(zhí)行多條 SQL 語句來實現(xiàn)的，所以就手寫了一個批次插入的sql,這里有一個坑，批量插入clickhouse會報錯，單條插入沒問題，百度了發(fā)現(xiàn)批量插入各種小問題，需要在 Values前加一個FORMAT ，即 insert into x(xx,xx) FORMAT Values (),處理后就沒有報錯了

package cn.yufire.sync.sls.getway.logs.mapper;

import cn.yufire.sync.sls.getway.logs.pojo.GetwayLogs;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Insert;

import java.util.List;

public interface GetwayLogsMapper extends BaseMapper<GetwayLogs> {

    /**
     * 批量插入網(wǎng)關日志
     *
     * @param list 數(shù)據(jù)
     * @return
     */
    @Insert("<script>insert into getway_logs(" +
            "api_name," +
            "response_body," +
            "api_stage_name," +
            "error_code," +
            "http_method," +
            "api_path," +
            "api_full_path," +
            "request_time," +
            "request_body," +
            "getway_request_id," +
            "getway_app_id," +
            "request_protocol," +
            "client_nonce," +
            "getway_app_name," +
            "getway_group_id," +
            "client_ip," +
            "getway_bind_domain," +
            "request_size," +
            "response_size," +
            "app_response_code" +
            ") FORMAT Values " +
            " <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\">\n" +
            "     (" +
            "#{item.apiName}," +
            "#{item.responseBody}," +
            "#{item.apiStageName}," +
            "#{item.errorCode}," +
            "#{item.httpMethod}," +
            "#{item.apiPath}," +
            "#{item.apiFullPath}," +
            "#{item.requestTime}," +
            "#{item.requestBody}," +
            "#{item.getwayRequestId}," +
            "#{item.getwayAppId}," +
            "#{item.requestProtocol}," +
            "#{item.clientNonce}," +
            "#{item.getwayAppName}," +
            "#{item.getwayGroupId}," +
            "#{item.clientIp}," +
            "#{item.getwayBindDomain}," +
            "#{item.requestSize}," +
            "#{item.responseSize}," +
            "#{item.appResponseCode}" +
            ")\n" +
            "        </foreach>" +

            "</script>")
    Integer batchInsert(List<GetwayLogs> list);


}

在業(yè)務中調用

@Autowired
private GetwayLogsMapper getwayLogsMapper;

log.info("批量插入至數(shù)據(jù)庫中...,是否添加成功:{}", getwayLogsMapper.batchInsert(logs));

可以看到增刪改查操作和操作mysql的一模一樣，也就是說給clickhouse當成mysql用就行

在springboot中集成clickhouse中也遇到了一些報錯

ClickHouse exception, code: 1002

使用了clickhouse的依賴和jpa依賴總是跑不起來,一直報沒有驅動，給了驅動也不對

clickhouse集成springboot報錯Unable to determine Dialect to use [name=ClickHouse, majorVersion=22]; user must register resolver or explicitly set 'hibernate.dialect'

百度了各種最后看到這個，直接給clickhouse的依賴和jpa的依賴去掉就給clickhouse當成mysql結果就正常了