springboot 配置多数据源

所需pom依赖

		<!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <!-- oracle jdbc 此依赖手动下载install-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
        </dependency>
        <!--druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--springBoot的aop-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

properties配置文件

# mysql数据源
spring.datasource.druid.mysql.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.mysql.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
spring.datasource.druid.mysql.username=root
spring.datasource.druid.mysql.password=123456

# oracle数据源
spring.datasource.druid.oracle.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.oracle.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.druid.oracle.url=jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.druid.oracle.username=cyc
spring.datasource.druid.oracle.password=123456


# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

数据源配置类

package com.cyc.boot.datasource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author CYC
 * @date 2020/3/20 - 17:40
 * 数据源配置类
 * 在该类中生成多个数据源实例并将其注入到 ApplicationContext 中
 */
@Configuration
public class DataSourceConfigurer {

    private static final Logger logger = LoggerFactory.getLogger(DynamicRoutingDataSource.class);

    @Value("${mybatis.mapper-locations}")
    private String mapperLocations;

    /**
     * 配置数据源
     */
    @Bean(name = "mysql")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.mysql")
    public DataSource mysql(){
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置数据源
     */
    @Bean(name = "oracle")
    @ConfigurationProperties(prefix = "spring.datasource.druid.oracle")
    public DataSource oracle(){

        return DruidDataSourceBuilder.create().build();

    }

    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource(){
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put(DataSourceKey.MYSQL.getName(),mysql());
        dataSourceMap.put(DataSourceKey.ORACLE.getName(),oracle());

        dynamicRoutingDataSource.setDefaultTargetDataSource(mysql());
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

        DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());

        return dynamicRoutingDataSource;
    }

    @Bean(name = "sqlSessionFactoryBean")
    //@ConfigurationProperties(prefix = "mybatis") //这种方式注入失败
    public SqlSessionFactoryBean sqlSessionFactoryBean(){
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        try {
            // 加载MyBatis配置文件
            PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
            // 能加载多个，所以可以配置通配符(如：classpath*:mapper/**/*.xml)
            sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources(mapperLocations));

            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setMapUnderscoreToCamelCase(true);//下划线转驼峰

            sqlSessionFactoryBean.setDataSource(dynamicDataSource());
            sqlSessionFactoryBean.setConfiguration(configuration);
        }catch (Exception e){
            logger.error("创建SqlSession连接工厂错误：{}", e);
        }

        return sqlSessionFactoryBean;
    }

    /**
     * 事务
     */
    @Bean
    public PlatformTransactionManager transactionManager(){
        return new DataSourceTransactionManager(dynamicDataSource());
    }

}

枚举类(定义数据库实例列表)

package com.cyc.boot.datasource;

/**
 * @author CYC
 * @date 2020/3/20 - 17:24
 * 枚举类 数据库实例列表
 */
public enum DataSourceKey {
    MYSQL("mysql"),ORACLE("oracle");
    private String name;

    DataSourceKey(String name){
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

ThreadLocle 存储当前数据库实例的key

package com.cyc.boot.datasource;

import java.util.ArrayList;
import java.util.List;

/**
 * @author CYC
 * @date 2020/3/20 - 17:29
 * 使用ThreadLocal存储当前使用数据源实例的key。
 * ThreadLocal实例化的时候给一个MYSQL的默认值，也就是默认数据源是mysql数据源。
 */
public class DynamicDataSourceContextHolder {

    private static ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(() -> DataSourceKey.MYSQL.getName());

    public static List<Object> dataSourceKeys = new ArrayList<Object>();

    public static void setDataSourceKey(String key){
        CONTEXT_HOLDER.set(key);
    }

    public static Object getDataSourceKey(){
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSourceKey(){
        CONTEXT_HOLDER.remove();
    }

    public static Boolean containDataSourceKey(String key){
        return dataSourceKeys.contains(key);
    }
}

调用determineCurrentLookupKey() 设置数据源

package com.cyc.boot.datasource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @author CYC
 * @date 2020/3/20 - 17:38
 * 该类继承自 AbstractRoutingDataSource 类，
 * 在访问数据库时会调用该类的 determineCurrentLookupKey() 方法获取数据库实例的 key
 */
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private static final Logger logger = LoggerFactory.getLogger(DynamicRoutingDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        logger.info("当前 数据库 is : {}",DynamicDataSourceContextHolder.getDataSourceKey());
        return DynamicDataSourceContextHolder.getDataSourceKey();
    }
}

自定义注解

package com.cyc.boot.datasource;

import java.lang.annotation.*;

/**
 * @author CYC
 * @date 2020/3/21 - 8:24
 */

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    //默认使用 mysql
    DataSourceKey value() default DataSourceKey.MYSQL;
}

植入切面逻辑

package com.cyc.boot.datasource;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * @author CYC
 * @date 2020/3/21 - 8:29
 */
@Aspect
@Component
public class DynamicDataSourceAspect {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    @Before("@annotation(targetDataSource))")
    public void switchDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        if (!DynamicDataSourceContextHolder.containDataSourceKey(targetDataSource.value().getName())) {
            logger.error("数据源 [{}] 不存在, use default DataSource [{}]", targetDataSource.value());
        } else {
            DynamicDataSourceContextHolder.setDataSourceKey(targetDataSource.value().getName());
            logger.info("切换数据源 to [{}] in Method [{}]",
                    DynamicDataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
        }
    }

    @After("@annotation(targetDataSource))")
    public void restoreDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        DynamicDataSourceContextHolder.clearDataSourceKey();
        logger.info("恢复 数据源 to [{}] in Method [{}]",
                DynamicDataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
    }

}

Controller 测试

package com.cyc.boot.controller;

import com.cyc.boot.datasource.DataSourceKey;
import com.cyc.boot.datasource.TargetDataSource;
import com.cyc.boot.dpapi.DynamicinterfaceRes;
import com.cyc.boot.entity.CycUser;
import com.cyc.boot.entity.OracleUser;
import com.cyc.boot.service.CycUserService;
import com.cyc.boot.service.OracleUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


/**
 * @author CYC
 * @date 2020/3/20 - 8:38
 */
@RestController
public class DatabaseController {

    @Autowired
    private CycUserService cycUserService;

    @Autowired
    private OracleUserService oracleUserService;


    @RequestMapping("getMysqlUser")
    @TargetDataSource(DataSourceKey.MYSQL)
    public DynamicinterfaceRes getMysqlUser(){
        CycUser cycUser = new CycUser();
        CycUser cycUsers = cycUserService.queryById(1);
        return DynamicinterfaceRes.buildSuccess(cycUsers);
    }

    @RequestMapping("getOracleUser")
    @TargetDataSource(DataSourceKey.ORACLE)
    public DynamicinterfaceRes getOracleUser(){
        OracleUser oracleUser = new OracleUser();
        OracleUser resultUser = oracleUserService.queryById(1);
        return DynamicinterfaceRes.buildSuccess(resultUser);
    }
}