springboot整合mybatis-plus、clickhouse、mysql多数据源

springboot的多数据源有多种方式，本文按照指定不同dao/mapper.xml的方式来实现访问不同的数据源。这样的好处是不用注解去切换数据源。

1、引入驱动

<dependency>
   <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>${druid.version}</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>${mybatisplus.boot.version}</version>
</dependency>

<!-- mysql -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
</dependency>
<!-- clickhouse-->
<dependency>
    <scope>compile</scope>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>${clickhouse-jdbc.version}</version>
</dependency>
<!-- 分页pagehelper-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>${pagehelper.version}/version>
    <scope>compile</scope>
</dependency>

2、配置连接信息

spring:
  datasource:
    first:
      url: jdbc:mysql://192.168.11.89:3308/watch_platform?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password: zdxf123
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.cj.jdbc.Driver
    ch:
      url: jdbc:clickhouse://192.168.11.89:8123/watch_platform
      username: default
      password: zdxf@2022
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.clickhouse.jdbc.ClickHouseDriver
    #druid配置
    druid:
      validation-query: SELECT 1
      initial-size: 10 # 初始化连接:连接池启动时创建的初始化连接数量
      max-active: 1000 # 最大活动连接:连接池在同一时间能够分配的最大活动连接的数量,如果设置为非正数则表示不限制
      min-idle: 10 # 最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,如果设置为0则不创建
      max-wait: 60000 # 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常,如果设置为-1表示无限等待
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
     login-username: druid # sql-stat监控用户名
       login-password: 123456 # sql-stat监控密码
     filter:
       stat:
         log-slow-sql: true
         slow-sql-millis: 1000
         merge-sql: false
       wall:
         config:
           multi-statement-allow: true

3、配置文件

mysql配置文件MysqlConf ：

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.incrementer.OracleKeyGenerator;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

/**
 * mysql数据源配置
 * @author lanbo
 *
 */

@Configuration
@MapperScan(basePackages = "com.xxxx.modules.*.dao", sqlSessionFactoryRef = "firstSqlSessionFactory")
public class MysqlConf {

    @Primary
    @Bean(name = "firstDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.first")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

    @Primary
    @Bean(name = "firstSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource,@Qualifier("mybatisConfiguration")MybatisConfiguration mybatisConfiguration,@Qualifier("globalConfig")GlobalConfig globalConfig) throws Exception {
    	MybatisSqlSessionFactoryBean  factoryBean = new MybatisSqlSessionFactoryBean();
        mybatisConfiguration.addInterceptor(new PaginationInterceptor());
        factoryBean.setConfiguration(mybatisConfiguration);
        GlobalConfig.DbConfig dbConfig = globalConfig.getDbConfig();
        dbConfig.setKeyGenerator(new OracleKeyGenerator());
        factoryBean.setGlobalConfig(globalConfig);
        factoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*Dao.xml"));
        return factoryBean.getObject();
    }

    @Primary
    @Bean(name = "firstTransactionManager")
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "firstSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

配置文件MybatisConfig ：

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 插件配置
 *
 * @author zj
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor pageClick = new PaginationInnerInterceptor(DbType.CLICK_HOUSE);
        PaginationInnerInterceptor pageMysql = new PaginationInnerInterceptor(DbType.MYSQL);
        mybatisPlusInterceptor.addInnerInterceptor(pageClick);
        mybatisPlusInterceptor.addInnerInterceptor(pageMysql);
        return mybatisPlusInterceptor;
    }
}

clickhouse的配置文件ChConfig：

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

/**
 * @Author LeeShaw
 * @Description 连接配置信息
 * @Date 2022/3/31 16:47
 **/
@ConfigurationProperties(prefix = "spring.datasource.ch")
@Data
@Component
public class ChConfig {
    private String url;
    private String username;
    private String password;
    private String type;
    private String driverClassName;

}

clickhouse的配置文件ClickHouseConfig：

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * @Author LeeShaw
 * @Description
 * @Date 2022/3/31 9:45
 **/
@Configuration
@MapperScan(basePackages = "com.xxxx.clickhouse.dao", sqlSessionFactoryRef = "clickhouseSqlSessionFactory")
public class ClickHouseConfig {
    @Autowired
    private ChConfig config;

    @Bean(name = "clickhouseDataSource")
    public DataSource druidDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(config.getUrl());
        druidDataSource.setUsername(config.getUsername());
        druidDataSource.setPassword(config.getPassword());
        druidDataSource.setDbType(config.getType());
        druidDataSource.setDriverClassName(config.getDriverClassName());
        return druidDataSource;
    }

    @Bean(name = "clickhouseSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("clickhouseDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setLogImpl(StdOutImpl.class);
        factoryBean.setConfiguration(configuration);
        factoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        PageInterceptor pageHelper = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "false");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);
        factoryBean.setPlugins(new Interceptor[] { pageHelper });
        factoryBean.setMapperLocations(resolver.getResources("classpath*:clickhouse/*Dao.xml"));
        return factoryBean.getObject();
    }

    @Bean(name = "clickhouseTransactionManager")
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("clickhouseDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "clickhouseSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("clickhouseSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4、目录结构

mysql的dao/mapper文件放在常规的目录下；clickhouse在单独目录下，如果想修改，在ClickHouseConfig里修改 @MapperScan(basePackages = "com.xxxx.yyyy.dao", sqlSessionFactoryRef = "clickhouseSqlSessionFactory")和 factoryBean.setMapperLocations(resolver.getResources("classpath*:clickhouse/*Dao.xml"));