【SpringBoot】动态调用双数据源（使用参数实现，解决同名Bean问题）

目前进行公司的一个BOSS项目的开发工作，由于单台Oracle数据库无法满足项目庞大的数据量，数据库开发不想进行去O，也就无法使用MySQL集群解决数据量陡增的问题。数据库开发提出了一个分库的方案，将整个Oracle按数据量分为两个库，同时在一个库的时候各个省份的表也是分离的，如果使用扫描就导致了在Java代码（SpringBoot + Mybatis）里存在同名的Bean，通过目录区分，这样Mapper也会是双份，代码冗余严重，所以根据网上提供的注解实现动态数据源调用的方式实现了根据入参调用不同数据源的方法，Demo（使用MySQL做）如下：

1.创建数据库

两张表结构非常简单，如下图。


2.配置application.yml

#Tomcat配置
server:
  port: 8080

#Actuator监控配置
security:
  user:
    name: asiainfo
    password: "!QAZ4esz"
    role: ACTUATOR
management:
    security:
      enabled: false
      roles: ACTUATOR
endpoints:
    jmx:
        enabled: false

#数据源配置
oracle:
  druid:
      stat_username: asiainfo
      stat_password: "!QAZ4esz"
  wlw_a:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      url:  jdbc:mysql://127.0.0.1:3306/wlw_a?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: root
      password_encrypted: false
      driverClassName: com.mysql.jdbc.Driver
      maxActive: 200
      minIdle: 5
      maxWait: 60000
      poolPreparedStatements : true
      testOnBorrow : false
      testOnReturn : false
      removeAbandoned : false
      maxPoolPreparedStatementPerConnectionSize : 100
      keepAlive : true
      minEvictableIdleTimeMillis : 300000
      validationQuery : "SELECT 'x' FROM DUAL"
      testWhileIdle : false
      filters : "stat"

  wlw_b:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      url:  jdbc:mysql://127.0.0.1:3306/wlw_b?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: root
      password_encrypted: false
      driverClassName: com.mysql.jdbc.Driver
      maxActive: 200
      minIdle: 5
      maxWait: 60000
      poolPreparedStatements : true
      testOnBorrow : false
      testOnReturn : false
      removeAbandoned : false
      maxPoolPreparedStatementPerConnectionSize : 100
      keepAlive : true
      minEvictableIdleTimeMillis : 300000
      validationQuery : "SELECT 'x' FROM DUAL"
      testWhileIdle : false
      filters : "stat"

3.Mapper

People.java

package com.asiainfo.test.entity;
public class People {

    private int id;

    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public People(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public People() {
    }

    @Override
    public String toString() {
        return "People{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

PeopleMapper.java

package com.asiainfo.test.mapper;

import com.asiainfo.test.entity.People;
import org.apache.ibatis.annotations.Mapper;

/**
 * Title PeopleMapper
 * Package com.asiainfo.test.mapper
 * Description people mapper
 * Author 唐磊
 * Date 2018-07-20 10:25
 */
@Mapper
public interface PeopleMapper {

    public People selectPeopleById(int id);

}

PeopleMapper.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.asiainfo.test.mapper.PeopleMapper" >
    <resultMap id="BaseResultMap" type="com.asiainfo.test.entity.People" >
        <id column="ID" property="id" jdbcType="INTEGER" />
        <result column="NAME" property="name" jdbcType="VARCHAR" />
    </resultMap>
    <sql id="Base_Column_List" >
        ID, NAME
    </sql>

    <select id="selectPeopleById" parameterType="int" statementType="PREPARED" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM people WHERE ID = #{id}
    </select>
</mapper>

4.DataSource配置

数据库配置由这四个类实现。

DataSourceHolder ：获取数据源、设置数据源等操作由此类实现。

package com.asiainfo.test.config;

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

public class DataSourceHolder {
    //线程本地环境
    private static final ThreadLocal<String> contextHolders = new ThreadLocal<String>();
    //数据源列表
    public static List<String> dataSourceIds = new ArrayList<>();
    //设置数据源
    public static void setDataSource(String customerType) {
        contextHolders.set(customerType);
    }
    //获取数据源
    public static String getDataSource() {
        return (String) contextHolders.get();
    }
    //清除数据源
    public static void clearDataSource() {
        contextHolders.remove();
    }
    //判断指定DataSrouce当前是否存在
    public static boolean containsDataSource(String dataSourceId){
        return dataSourceIds.contains(dataSourceId);
    }
}

DynamicDataSource ：继承AbstractRoutingDataSource通过DataSourceHolder获取数据源。

package com.asiainfo.test.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceHolder.getDataSource();
    }
}

DynamicDataSourceConfig ：获取数据库配置并配置两个数据库，设置数据库SessionFactory以及事务管理TransactionManager。

package com.asiainfo.test.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.bind.RelaxedPropertyResolver;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan(basePackages = DynamicDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "wlwSqlSessionFactory")
public class DynamicDataSourceConfig implements EnvironmentAware{

    // 默认数据源
    private DataSource defaultDataSource;

    //第二数据源
    private DataSource secondDataSource;

    static final String PACKAGE = "com.asiainfo.test.mapper";

    private static final String MAPPER_LOCATION = "classpath:mapper/*.xml";

    private static final String DEFAULT_ENV_PATH = "oracle.wlw_a.datasource.";

    private static final String SECOND_ENV_PATH = "oracle.wlw_b.datasource.";

    private static final String DEFAULT_DATABASE = "DSA";

    private static final String SECOND_DATABASE = "DSB";

    @Override
    public void setEnvironment(Environment environment) {
        try {
            initDefaultDatasource(environment);
        } catch (Exception e) {
           System.out.println("创建主数据库失败，原因：" + e);
        }

        try {
            initOtherDatasource(environment);
        } catch (Exception e) {
            System.out.println("创建第二数据库失败，原因：" + e);
        }
    }

    private void initDefaultDatasource(Environment environment) throws Exception {
        // 读取主数据源
        defaultDataSource = buildDruidDataSource(environment,DEFAULT_ENV_PATH);
        DataSourceHolder.dataSourceIds.add(DEFAULT_DATABASE);
    }

    private void initOtherDatasource(Environment environment) throws Exception {
        // 读取第二数据源
        secondDataSource = buildDruidDataSource(environment,SECOND_ENV_PATH);
        DataSourceHolder.dataSourceIds.add(SECOND_DATABASE);
    }

    //拼装数据源
    private DruidDataSource buildDruidDataSource(Environment environment, String envPath) throws Exception {

        RelaxedPropertyResolver propertyResolver = new RelaxedPropertyResolver(environment, envPath);

        DruidDataSource dataSource = new DruidDataSource();

        dataSource.setDriverClassName(propertyResolver.getProperty("driverClass"));
        dataSource.setUrl(propertyResolver.getProperty("url"));
        dataSource.setUsername(propertyResolver.getProperty("username"));
        dataSource.setPassword(propertyResolver.getProperty("password"));
        dataSource.setMaxActive(Integer.parseInt(propertyResolver.getProperty("maxActive")));
        dataSource.setMinIdle(Integer.parseInt(propertyResolver.getProperty("minIdle")));
        dataSource.setMaxWait(Long.parseLong(propertyResolver.getProperty("maxWait")));
        dataSource.setPoolPreparedStatements(Boolean.parseBoolean(propertyResolver.getProperty("poolPreparedStatements")));
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(
                Integer.parseInt(propertyResolver.getProperty("maxPoolPreparedStatementPerConnectionSize")));
        dataSource.setKeepAlive(Boolean.parseBoolean(propertyResolver.getProperty("keepAlive")));
        dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(propertyResolver.getProperty("minEvictableIdleTimeMillis")));
        dataSource.setValidationQuery(propertyResolver.getProperty("validationQuery"));
        dataSource.setTestWhileIdle(Boolean.parseBoolean(propertyResolver.getProperty("testWhileIdle")));
        dataSource.setFilters(propertyResolver.getProperty("filters"));

        return dataSource;
    }

    @Bean(name = "dataSource")
    public DynamicDataSource dataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();

        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

        // 配置多数据源
        Map<Object, Object> dbMap = new HashMap<Object, Object>();

        dbMap.put(DEFAULT_DATABASE,defaultDataSource);
        dbMap.put(SECOND_DATABASE,secondDataSource);

        dynamicDataSource.setTargetDataSources(dbMap);

        return dynamicDataSource;
    }

    @Primary
    @Bean(name = "wlwTransactionManager")
    public DataSourceTransactionManager wlwTransactionManager() throws SQLException {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource());
        transactionManager.setNestedTransactionAllowed(true);
        transactionManager.setRollbackOnCommitFailure(true);
        return transactionManager;
    }

    @Primary
    @Bean(name = "wlwSqlSessionFactory")
    public SqlSessionFactory wlwSqlSessionFactory(DataSource dynamicDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dynamicDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setUseGeneratedKeys(true);
        configuration.setUseColumnLabel(true);
        configuration.setJdbcTypeForNull(null);
        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}

DynamicDataSourceAspect ：设置切面在Service层，每当执行service下的方法是获取到所有参数，提前定义两个数据源的常量，根据相应参数切换不同的数据源。
优点：根据传来的参数动态切换数据源，减少代码冗余。
缺点：入参必须带数据库参数，并且要尽量放在前面，减少循环。

package com.asiainfo.test.config;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;


@Aspect
@Order(1)// 保证该AOP在@Transactional之前执行
@Component
public class DynamicDataSourceAspect {

    @Around("execution(* com.asiainfo.test.service..*(..))")
    public Object switchDS(ProceedingJoinPoint point) throws Throwable {
        Class<?> className = point.getTarget().getClass();

        String dataSource = "DSA";//默认数据源

        Object[] args = point.getArgs();

		//遍历参数列表，判断调用的数据源
        for (Object arg : args) {
            if("wlw_b".equals(arg.toString())){
                dataSource = "DSB";
                break;
            }
        }

        // 切换数据源
        DataSourceHolder.setDataSource(dataSource);

        try {
            return point.proceed();
        } finally {
            DataSourceHolder.clearDataSource();
        }
    }
}

5.启动类与Controller

由于该Demo要做展示（有部分与会人员不懂代码），所以没有用单元测试，单独写了一个Controller用作展示，更为直观。

package com.asiainfo.test;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication

@EnableAutoConfiguration
@ComponentScan("com.asiainfo.test.*")
@MapperScan("com.asiainfo.test.mapper")
public class DynmultipleApplication {

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

package com.asiainfo.test.controller;

import com.asiainfo.test.entity.People;
import com.asiainfo.test.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private TestService testService;

    @RequestMapping("people")
    public void test(HttpServletRequest request, HttpServletResponse response) throws Exception {

        String id = request.getParameter("id");
        String library = request.getParameter("library");

        People people = new People();

        people = testService.selectPeopleById(Integer.valueOf(id),library);

        response.setCharacterEncoding("UTF-8");

        PrintWriter out = response.getWriter();

        out.println(people.toString());

        out.flush();
        out.close();
    }
}

6.效果展示

根据参数library判断使用哪个数据源。