SpringBoot多数据源配置

依赖

说明：引入对应数据库的连接工具以及orm框架依赖，注意，orm不一定非要选择mybatis，同理，数据库连接池也不一定是druid，这里以此为示例

		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
        </dependency>
        
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>

多数据源配置文件

多数据源配置文件自定义，配置好对应数据源的连接信息，这里type对应数据源类型，因为前面引入druid连接池，所以可以直接声明druid数据源，不用配置此信息也可以，详见下方数据源配置

#mysql数据源
spring.datasource.first.url=jdbc:mysql://ip:3306/库?useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
spring.datasource.first.username=用户
spring.datasource.first.password=密码
spring.datasource.first.driverClassName=com.mysql.jdbc.Driver
spring.datasource.first.type=com.alibaba.druid.pool.DruidDataSource
#mycat数据源
spring.datasource.second.url=jdbc:mysql://ip:8066/库?useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
spring.datasource.second.username=用户
spring.datasource.second.password=密码
spring.datasource.second.driverClassName=com.mysql.jdbc.Driver
spring.datasource.second.type=com.alibaba.druid.pool.DruidDataSource
#clickhouse数据源
spring.datasource.clickhouse.read.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.clickhouse.read.url = jdbc:clickhouse://ip:28123/库?useSSL=false
spring.datasource.clickhouse.read.driver-class-name = ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.clickhouse.read.username = 用户
spring.datasource.clickhouse.read.password =密码

多数据源配置

注意，这里省略了sqlSessionTemplate模板的配置，因此需要指定一个唯一的主数据源，使用@Primary注解标识，这样，Spring容器在尝试注入SqlSessionFactory时，才会找到唯一的bean，如果配置了sqlSessionTemplate，可以不用此配置或都标为主数据源

package org.example.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;

@Configuration
@MapperScan(basePackages = MysqlSourceConfig.MAPPER_INTERFACE_PACKAGE,sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlSourceConfig {
	//mapper文件要分类型存储，方便数据源隔离，具体如下图所示
    static final String MAPPER_INTERFACE_PACKAGE = "org.example.mapper.mysql";
    //namespace命名空间指定了对应的mapper文件，因此可以统一放置，注意一定要隔离mapper文件数据源信息，否则不生效
    static final String MAPPER_XML_LOCATION = "classpath:/mapper/*Mapper.xml";

    @Value("${spring.datasource.first.url}")
    private String url;

    @Value("${spring.datasource.first.username}")
    private String user;

    @Value("${spring.datasource.first.password}")
    private String password;

    @Value("${spring.datasource.first.driverClassName}")
    private String driverClass;

    @Value("${spring.datasource.first.type}")
    private Class<? extends DataSource> type;

    /**
     * 构建dataSource连接池对象，注册bean命名用于区分
     * primary注解用于标记主数据源，一般情况下，只有一个标记即可
     * @return
     */
    @Bean(name = "MysqlDataSource")
    @Primary
    public DataSource mysqlDataSource(){
        return DataSourceBuilder.create()
                .type(type)
                .url(url)
                .driverClassName(driverClass)
                .username(user)
                .password(password)
                .build();
        //还可以直接声明为DruidDataSource来创建，如下，这样，type类型可以不用配置，但是必须使用druid连接池
//        DruidDataSource dataSource = new DruidDataSource();
//        dataSource.setDriverClassName(driverClass);
//        dataSource.setUrl(url);
//        dataSource.setUsername(user);
//        dataSource.setPassword(password);
//        return dataSource;
    }

    /**
     * 声明事务管理器
     * @return
     */
    @Bean(name = "MysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(mysqlDataSource());
    }

    /**
     * 创建MyBatis的sql会话工厂，注入名为MysqlDataSource的数据源，设置MapperXML文件的位置，方便MyBatis知道如何找到SQL映射文件
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("MysqlDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MysqlSourceConfig.MAPPER_XML_LOCATION));
        return sessionFactory.getObject();
    }
}

package org.example.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;

@Configuration
@MapperScan(basePackages = MycatSourceConfig.MAPPER_INTERFACE_PACKAGE,sqlSessionFactoryRef = "mycatSqlSessionFactory")
public class MycatSourceConfig {
    static final String MAPPER_INTERFACE_PACKAGE = "org.example.mapper.mycat";
    static final String MAPPER_XML_LOCATION = "classpath:/mapper/*Mapper.xml";

    @Value("${spring.datasource.second.url}")
    private String url;

    @Value("${spring.datasource.second.username}")
    private String user;

    @Value("${spring.datasource.second.password}")
    private String password;

    @Value("${spring.datasource.second.driverClassName}")
    private String driverClass;

    @Value("${spring.datasource.second.type}")
    private Class<? extends DataSource> type;

    @Bean(name = "MycatDataSource")
    public DataSource mycatDataSource(){
        return DataSourceBuilder.create()
                .type(type)
                .url(url)
                .driverClassName(driverClass)
                .username(user)
                .password(password)
                .build();
    }

    @Bean(name = "MycatTransactionManager")
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(mycatDataSource());
    }

    @Bean(name = "mycatSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("MycatDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MysqlSourceConfig.MAPPER_XML_LOCATION));
        return sessionFactory.getObject();
    }
}

package org.example.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;

@Configuration
@MapperScan(basePackages = ClickHouseSourceConfig.MAPPER_INTERFACE_PACKAGE,sqlSessionFactoryRef = "ckSqlSessionFactory")
public class ClickHouseSourceConfig {
    static final String MAPPER_INTERFACE_PACKAGE = "org.example.mapper.clickhouse";
    static final String MAPPER_XML_LOCATION = "classpath:/mapper/*Mapper.xml";
    @Value("${spring.datasource.clickhouse.read.url}")
    private String url;

    @Value("${spring.datasource.clickhouse.read.username}")
    private String user;

    @Value("${spring.datasource.clickhouse.read.password}")
    private String password;

    @Value("${spring.datasource.clickhouse.read.driver-class-name}")
    private String driverClass;

    @Value("${spring.datasource.clickhouse.read.type}")
    private Class<? extends DataSource> type;

    @Bean(name = "ckDataSource")
    public DataSource ckDataSource(){
        return DataSourceBuilder.create()
                .type(type)
                .url(url)
                .driverClassName(driverClass)
                .username(user)
                .password(password)
                .build();
    }

    @Bean(name = "ckTransactionManager")
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(ckDataSource());
    }

    @Bean(name = "ckSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("ckDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MysqlSourceConfig.MAPPER_XML_LOCATION));
        return sessionFactory.getObject();
    }
}

配置完成后，还需要在启动类排除数据源自动加载类，用于我们自定义的数据配置生效@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

测试

创建测试方法，引入三个Mapper文件，查对应的数据库表的数据总和

public void test1(){
        int clickhouseNum = clickHouseMapper.count();
        logger.info("clickhouse表中数据为"+clickhouseNum);
        int mycatNum = mycatMapper.count();
        logger.info("mycat表中数据为"+mycatNum);
        int mysqlNum = mysqlMapper.count();
        logger.info("mysql表中数据为"+mysqlNum);
    }