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@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;@Configurationpublic 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;@ConfigurationProperties(prefix = "spring.datasource.ch")@Data@Componentpublic 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;@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"));

来源地址：https://blog.csdn.net/qq_28392947/article/details/130105133