spring boot 2.3.2.RELEASE + mysql + clickhouse + mybatis-plus

• java 项目集成 mysql 和 clickhouse 数据库

• 依赖：因为使用阿里源管理没成功过，这里使用的数据源管理是 dynamic-datasource-spring-boot-starter ，如果原来项目有依赖于 druid-spring-boot-starter 要排除掉，

		<!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <version>1.18.24</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>2.0.10</version>
        </dependency>

		<!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!-- clickhouse -->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.2</version>
        </dependency>
        <!-- 多数据源管理 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!--引入mybatisPlus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--mybatisPlus  生成-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- 模板引擎 -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>
        <!--mybatisPlus-->

• 配置文件

# -------------------------------------------------------------------#
spring.datasource.dynamic.primary=mysql
spring.datasource.dynamic.strict=false
spring.datasource.dynamic.datasource.mysql.url=jdbc:mysql://ip:port/db_name?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&autoReconnect=true&autoReconnectForPools=true
spring.datasource.dynamic.datasource.mysql.username=root
spring.datasource.dynamic.datasource.mysql.password=123
spring.datasource.dynamic.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.dynamic.datasource.clickhouse.url=jdbc:clickhouse://ip:port/db_name
spring.datasource.dynamic.datasource.clickhouse.username=admin
spring.datasource.dynamic.datasource.clickhouse.password=password
spring.datasource.dynamic.datasource.clickhouse.driver-class-name=com.clickhouse.jdbc.ClickHouseDriver


spring.datasource.dynamic.druid.initial-size=10
spring.datasource.dynamic.druid.max-active=40
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.validation-query=select 'x'
spring.datasource.dynamic.druid.max-wait=60000
spring.datasource.dynamic.druid.time-between-eviction-runs-millis=60000
spring.datasource.dynamic.druid.min-evictable-idle-time-millis=30000

• 实体类 和正常实体没有任何区别

package com.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * @Author Meng
 * @Date 2022/7/29 9:13  星期五/周五
 * @Description 测试多源clickhouse
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class PhoneRecord implements Serializable {

    private Long field_name_1;
    private String field_name_2;
    private Integer field_name_3;
    private Integer field_name_4;
    private Long field_name_5;
}

• 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.mapper.ClickhouseMapper">
    <resultMap id="BaseResultMap" type="com.entity.PhoneRecord">
        <result property="field_name_1" column="field_name_1" jdbcType="BIGINT"/>
        <result property="field_name_2" column="field_name_2" jdbcType="VARCHAR"/>
        <result property="field_name_3" column="field_name_3" jdbcType="INTEGER"/>
        <result property="field_name_4" column="field_name_4" jdbcType="INTEGER"/>
        <result property="field_name_5" column="field_name_5" jdbcType="BIGINT"/>
    </resultMap>

    <sql id="Base_Column_List">
        <!--Base_Column_List-->
        field_name_1,field_name_2,field_name_3,field_name_4,field_name_5
    </sql>



    <select id="selectByPageCondition" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM `db_name` LIMIT 0,100;
    </select>
</mapper>

• mapper

package com.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.entity.PhoneRecord;

import java.util.List;

/**
 * <p>
 * 基本信息 Mapper 接口
 * </p>
 *
 * @author Meng
 * @since 2022-06-20
 */
@DS("clickhouse") //重点是这里 使用clickhouse 数据库要指定数据源  mysql数据库默认不用配置就可以了，因为在配置文件中配置了默认mysql
public interface ClickhouseMapper extends BaseMapper<PhoneRecord> {

    List<PhoneRecord> selectByPageCondition();
}

• 接口请求

package com.controller;

import com.mapper.ClickhouseMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

/**
 * @Author Meng
 * @Date 2022/7/29 10:53  星期五/周五
 * @Description
 */
@SuppressWarnings("Duplicates")
@RestController
@Slf4j
@RequestMapping("/control")
@Api(tags = "Control")
public class Controller {

    @Resource
    private ClickhouseMapper clickhouseMapper;

    @PostMapping(value = "/control1")
    @ApiOperation(value = "control1",httpMethod = "POST")
    public String control1(){
        System.out.println(clickhouseMapper.selectByPageCondition());
        return "control1";
    }
}

• 启动类

package com;

//import org.mybatis.spring.annotation.MapperScan;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;

/**
 * @author Eng
 * @date 2021/11/25 14:26
 * @Description
 */
//@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@SpringBootApplication(scanBasePackages = {"com"},exclude = {DataSourceAutoConfiguration.class})//这里排除spring自动配置
//@ServletComponentScan("com")
@EnableScheduling
@MapperScan({"com.mapper", "com.mapper"})
@EnableAsync
//@EnableCaching
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

• 因为原来项目使用的是阿里连接源依赖，一直弄不好，干脆直接重新搞个spring demo 全部重新来搞，搞好了直接复制到项目里就可以实际应用了，这里要说明下clickhouse 修改和新增语句应该是和mysql不一样的所以不能使用mybatis-plus 执行要自己写sql。记录下以后减少踩坑。