Spring Boot多数据源配置/读写分离（Druid+MyBatisPlus）

SpringBoot配置多数据源/读写分离整体步骤

• Jar包引入 spring boot + druid + mybatis plus（多数据源 + 分页）
• application.yml配置多数据源及mybatis plus mapper配置
• 新建动态数据源DynamicDataSource（继承AbstractRoutingDataSource），ThreadLocal中获取当前使用哪个数据源
• 自定义多个Datasource，枚举类对应各个DataSource，及事务集成
• 编写AOP，增强Mapper方法的调用处，切换具体数据源
• 测试，数据源切换、插入、分页查询等

项目源码

https://github.com/zc-zangchao/multiple-data-source

POM配置

版本

        <springboot.version>2.2.0.RELEASE</springboot.version>
        <druid.version>1.1.9</druid.version>
        <oracle.version>12.1.0.1.0</oracle.version>
        <mysql-connector.version>8.0.18</mysql-connector.version>
        <mybatis-plus.version>2.1.9</mybatis-plus.version>
        <log4jdbc.version>1.2</log4jdbc.version>

具体jar

			<!--springboot-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>${springboot.version}</version>
            </dependency>
            
            <!--druid-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.version}</version>
            </dependency>
            <!-- 多数据源时使用mybatis-plus已经包含了mybatis+pageHelper，所以不需要再引用 mybatis + pageHelper -->
            <!-- 多数据源配置支撑 -->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>${mybatis-plus.version}</version>
            </dependency>

            <!-- log4jdbc jdbc日志增强 -->
            <dependency>
                <groupId>com.googlecode.log4jdbc</groupId>
                <artifactId>log4jdbc</artifactId>
                <version>${log4jdbc.version}</version>
            </dependency>

            <!-- db connect -->
            <!-- oracle -->
            <dependency>
                <groupId>com.oracle</groupId>
                <artifactId>ojdbc7</artifactId>
                <version>${oracle.version}</version>
            </dependency>

            <!-- mysql -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql-connector.version}</version>
            </dependency>

yml配置

# mybatis mapper locations
mybatis-plus:
  mapper-locations: classpath:mapper/*Mapper.xml

spring:
  datasource:
    druid:
      oracle:
        url: jdbc:oracle:thin:@127.0.0.1:1521/db
        username: root
        password: root
        driver-class-name: oracle.jdbc.driver.OracleDriver
        max-active: 10
        max-wait: 10000
      mysql:
        # tcpRcvBuf/tcpSndBuf 缓冲区参数 rewriteBatchedStatements batchUpdate参数
        url: jdbc:log4jdbc:mysql://127.0.0.1:3306/mydb?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&tcpRcvBuf=1048576&tcpSndBuf=1048576&socketTimeout=180000&rewriteBatchedStatements=true&autoReconnect=true
        username: root
        password: root
        # 开源 SQL 日志框架，在大多数情况下极大改善了可读性及调试工作
        driver-class-name: net.sf.log4jdbc.DriverSpy
        max-active: 10
        max-wait: 600000
        # sql监控
        filters: stat
        # 检测池里连接的可用性 开启影响性能 默认false
        test-on-borrow: false
        # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除 默认true
        test-while-idle: true
        # 每30秒运行一次空闲连接回收器
        time-between-eviction-runs-millis: 30000
        # 检测语句
        validation-query: "select 1"
      mysql-backup:
        # tcpRcvBuf/tcpSndBuf 缓冲区参数 rewriteBatchedStatements batchUpdate参数
        url: jdbc:mysql://127.0.0.1:3306/mydb?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        max-active: 10
        max-wait: 600000
        # sql监控
        filters: stat
        # 检测池里连接的可用性 开启影响性能 默认false
        test-on-borrow: false
        # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除 默认true
        test-while-idle: true
        # 每30秒运行一次空闲连接回收器
        time-between-eviction-runs-millis: 30000
        # 检测语句
        validation-query: "select 1"

动态数据源配置

package com.springboot.demo.service.datasource;

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

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

package com.springboot.demo.service.datasource;

public class DataSourceContextHolder {
    private DataSourceContextHolder(){}

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDataSource(String dbType){
        contextHolder.set(dbType);
    }

    public static String getDataSource(){
        return contextHolder.get();
    }

    public static void clearDataSource(){
        contextHolder.remove();
    }
}

DataSource及事务配置

package com.springboot.demo.service.datasource.type;

public enum EnumDataSourceType {
    ORACLE,

    MYSQL,

    MYSQL_BACKUP;
}

package com.springboot.demo.service.datasource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.boot.starter.MybatisPlusProperties;
import com.baomidou.mybatisplus.spring.boot.starter.SpringBootVFS;
import com.springboot.demo.service.datasource.type.EnumDataSourceType;
import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.SqlSessionFactoryBean;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Resource
    private MybatisPlusProperties properties;

    @Bean(name = "dataSourceOracle")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.oracle")
    public DataSource oracleDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dataSourceMysql")
    @ConfigurationProperties(prefix = "spring.datasource.druid.mysql")
    public DataSource mysqlDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dataSourceMysqlBackup")
    @ConfigurationProperties(prefix = "spring.datasource.druid.mysql-backup")
    public DataSource mysqlBackupDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 配置默认数据源
        dynamicDataSource.setDefaultTargetDataSource(oracleDataSource());
        // 配置多数据源
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(EnumDataSourceType.ORACLE.name(), oracleDataSource());
        dataSourceMap.put(EnumDataSourceType.MYSQL.name(), mysqlDataSource());
        dataSourceMap.put(EnumDataSourceType.MYSQL_BACKUP.name(), mysqlBackupDataSource());
        dynamicDataSource.setTargetDataSources(dataSourceMap);
        return dynamicDataSource;
    }

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 开启 PageHelper 的支持
        paginationInterceptor.setLocalPage(true);
        return paginationInterceptor;
    }

    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean() {

        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dynamicDataSource());
        // mybatis本身的核心库在springboot打包成jar后有个bug，无法完成别名的扫描
        factory.setVfs(SpringBootVFS.class);

        org.apache.ibatis.session.Configuration configuration = this.properties.getConfiguration();
        if (configuration == null && !StringUtils.hasText(this.properties.getConfigLocation())) {
            configuration = new MybatisConfiguration();
        }

        factory.setConfiguration(configuration);

        // 分页功能
        factory.setPlugins(new Interceptor[]{ paginationInterceptor()});

        if (this.properties.getConfigurationProperties() != null) {
            factory.setConfigurationProperties(this.properties.getConfigurationProperties());
        }

        if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
            factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
        }

        if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {
            factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
        }

        if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
            factory.setMapperLocations(this.properties.resolveMapperLocations());
        }

        return factory;
    }

//    @Bean
//    public SqlSessionFactory sqlSessionFactory() throws Exception {
//        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
//        sqlSessionFactory.setDataSource(dynamicDataSource());
//        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*Mapper.xml"));
//
//        MybatisConfiguration configuration = new MybatisConfiguration();
//        configuration.setJdbcTypeForNull(JdbcType.NULL);
//        configuration.setMapUnderscoreToCamelCase(true);
//        configuration.setCacheEnabled(false);
//        sqlSessionFactory.setConfiguration(configuration);
//        sqlSessionFactory.setPlugins(new Interceptor[]{
//                paginationInterceptor() //添加分页功能
//        });
//
//        return sqlSessionFactory.getObject();
//    }

    /**
     * 配置@Transactional注解事务
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

}

AOP增强 切换数据源

package com.springboot.demo.service.datasource.annotation;

import com.springboot.demo.service.datasource.type.EnumDataSourceType;

import java.lang.annotation.*;

@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TargetDataSource {

    EnumDataSourceType value() default EnumDataSourceType.ORACLE;

}

package com.springboot.demo.service.datasource.aspect;

import com.springboot.demo.service.datasource.DataSourceContextHolder;
import com.springboot.demo.service.datasource.annotation.TargetDataSource;
import com.springboot.demo.service.datasource.type.EnumDataSourceType;
import lombok.extern.slf4j.Slf4j;
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.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Component
@Aspect
@Slf4j
// 默认优先级 最后执行 可调整Order
public class DynamicDataSourceAspect {


    @Before("@annotation(targetDataSource)")
    public void before(JoinPoint point, TargetDataSource targetDataSource) {
        try {
            TargetDataSource annotationOfClass = point.getTarget().getClass().getAnnotation(TargetDataSource.class);
            String methodName = point.getSignature().getName();
            Class[] parameterTypes = ((MethodSignature) point.getSignature()).getParameterTypes();
            Method method = point.getTarget().getClass().getMethod(methodName, parameterTypes);
            TargetDataSource methodAnnotation = method.getAnnotation(TargetDataSource.class);
            methodAnnotation = methodAnnotation == null ? annotationOfClass : methodAnnotation;
            EnumDataSourceType dataSourceType = methodAnnotation != null && methodAnnotation.value() != null ? methodAnnotation.value() : EnumDataSourceType.ORACLE;
            DataSourceContextHolder.setDataSource(dataSourceType.name());
        } catch (NoSuchMethodException e) {
            log.warn("Aspect targetDataSource inspect exception.", e);
        }
    }

    @After("@annotation(targetDataSource)")
    public void after(JoinPoint point, TargetDataSource targetDataSource) {
        DataSourceContextHolder.clearDataSource();
    }
}

测试验证

验证数据源切换、事务、分页

package com.springboot.demo.web.service;

import com.baomidou.mybatisplus.plugins.pagination.PageHelper;
import com.springboot.demo.service.dao.domain.User;
import com.springboot.demo.service.dao.mapper.UserMapper;
import com.springboot.demo.service.datasource.annotation.TargetDataSource;
import com.springboot.demo.service.datasource.type.EnumDataSourceType;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserService {
    @Resource
    private UserMapper userMapper;

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public boolean addUser(User user) {
        int result = userMapper.insert(user);
        if (result <= 0) {
            throw new RuntimeException("exception");
        }
        return result > 0;
    }

    @TargetDataSource(EnumDataSourceType.MYSQL_BACKUP)
    public User queryUserById(String userId) {
        return userMapper.selectByPrimaryKey(userId);
    }

    @TargetDataSource(EnumDataSourceType.MYSQL)
    public List<User> selectAll(int pageNum, int pageSize) {
        // 启动分页
        PageHelper.startPage(pageNum, pageSize);
        return userMapper.selectAll();
    }
}

package com.springboot.demo.web.controller;

import com.springboot.demo.service.dao.domain.User;
import com.springboot.demo.web.model.ResultInfo;
import com.springboot.demo.web.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.websocket.server.PathParam;
import java.util.List;

@RestController
@Slf4j
public class UserController {
    @Resource
    private UserService userService;

    @GetMapping("/user/query")
    public List<User> queryUsers(@PathParam("pageNum") int pageNum, @PathParam("pageSize") int pageSize) {
        log.info("Query users.");
        return userService.selectAll(pageNum, pageSize);
    }

    @GetMapping("/user/queryUserById/{userId}")
    public User queryUserById(@PathVariable String userId) {
        log.info("Query user by Id.");
        return userService.queryUserById(userId);
    }

    @PostMapping("/user/add")
    public ResultInfo addUser(@RequestBody User user) {
        log.info("Add user.");
        userService.addUser(user);
        return new ResultInfo();
    }
}

参考：
Spring Boot 整合 Durid数据库连接池
Spring Boot2.0配置Druid数据库连接池（单数据源、多数据源、数据监控）
使用springboot + druid + mybatisplus完成多数据源配置
数据连接池默认配置带来的坑testOnBorrow=false，cloes_wait 终于解决了
关于连接池参数testWhileIdle，testOnBorrow，testOnReturn的疑问
使用druid连接池带来的坑testOnBorrow=false
MySQL之rewriteBatchedStatements
使用log4jdbc更有效的记录java sql日志