SpringBoot+ClickHouse集成

前面已经完成ClickHouse的搭建，创建账号，创建数据库，保存数据库等，接下来就是在SpringBoot项目中集成ClickHouse。

一，引入依赖

<!-- SpringBoot集成ClickHouse -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
	<version>3.5.5</version>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.38</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>

二，添加数据源配置

1，在applicaiton.yml中添加数据源配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://x.x.x.x:8123/account
      username: clickhouse
      password: clickhouse
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

2，在applicaiton.yml中添加mybatis-plus配置

mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: true
    multiple-result-sets-enabled: true
    use-generated-keys: true
    default-statement-timeout: 60
    default-fetch-size: 100
  type-aliases-package: com.xxx.springboot.repository.entity

三，添加自定义数据源

1，创建配置类

package com.xxx.springboot.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @author wangrong03
 * @date 2024/11/5 下午3:59
 */
@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class ClickHouseCommonConfig {
    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
    private String username;
    private String password;
}

2，新增数据源

package com.xxx.springboot.config;

import com.alibaba.druid.pool.DruidDataSource;
import jakarta.annotation.Resource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;


@Configuration
@EnableConfigurationProperties(ClickHouseCommonConfig.class)
@MapperScan(basePackages = {"com.xxx.springboot.repository.mapper"},
    sqlSessionFactoryRef = "SqlSessionFactory")
public class DruidConfig {
    @Resource
    private ClickHouseCommonConfig clickHouseCommonConfig;

    /**
     * 重写 DataSource
     * @return
     */
    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(clickHouseCommonConfig.getUrl());
        datasource.setDriverClassName(clickHouseCommonConfig.getDriverClassName());
        datasource.setInitialSize(clickHouseCommonConfig.getInitialSize());
        datasource.setMinIdle(clickHouseCommonConfig.getMinIdle());
        datasource.setMaxActive(clickHouseCommonConfig.getMaxActive());
        datasource.setMaxWait(clickHouseCommonConfig.getMaxWait());
        datasource.setUsername(clickHouseCommonConfig.getUsername());
        datasource.setPassword(clickHouseCommonConfig.getPassword());
        return datasource;
    }

    @Bean("SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultFetchSize(100);
        configuration.setDefaultStatementTimeout(60);
        sqlSessionFactoryBean.setMapperLocations(
            new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }
}

3，在SpringBootApplicaiton的注解下加上MapperScan注解

四，实现新增和删除

1，mapper.xml

<?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.xxx.springboot.repository.mapper.StudentMapper">
    <resultMap id="BaseResultMap" type="com.xxx.springboot.repository.entity.Student">
        <id column="StudentID" property="studentId" jdbcType="INTEGER" />
        <result column="StudentName" property="studentName" jdbcType="VARCHAR" />
        <result column="Address" property="address" jdbcType="VARCHAR" />
        <result column="Grade" property="grade" jdbcType="INTEGER" />
        <result column="Age" property="age" jdbcType="INTEGER" />
        <result column="Gender" property="gender" jdbcType="INTEGER" />
        <result column="StartTime" property="startTime" jdbcType="VARCHAR" />
    </resultMap>
    <insert id="saveData" parameterType="com.xxx.springboot.repository.entity.Student" >
        INSERT INTO student
        (StudentID,StudentName,Address,Grade,Age,Gender,StartTime)
        VALUES
        (#{studentId,jdbcType=INTEGER},#{studentName,jdbcType=VARCHAR},#{address,jdbcType=VARCHAR},
        #{grade,jdbcType=INTEGER},#{age,jdbcType=INTEGER},#{gender,jdbcType=INTEGER},#{startTime,jdbcType=VARCHAR})
    </insert>
    <select id="queryAll" resultMap="BaseResultMap">
        SELECT StudentID,StudentName,Address,Grade,Age,Gender,StartTime FROM student
    </select>
</mapper>

2, mapper

后面就是在service里调用这个save和queryAll方法就可以，不再赘述了。