springboot+durid动态数据源切换

一、数据库配置

spring:
  datasource:
    name: soMysql
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    #基本属性
    url: jdbc:mysql://192.168.113.128:3306/security?characterEncoding=utf8&useSSL=true
    username: root
    password: root
    druid:
      # 相关配置
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat
      #配置初始化大小/最小/最大
      initial-size: 1
      min-idle: 1
      max-active: 20
      #获取连接等待超时时间
      max-wait: 60000
      #间隔多久进行一次检测，检测需要关闭的空闲连接
      time-between-eviction-runs-millis: 60000
      #一个连接在池中最小生存的时间
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      #connection-properties: druid.stat.mergeSql: true;druid.stat.slowSqlMillis: 5000
      # 合并多个DruidDataSource的监控数据
      #use-global-datasource-stat: true
  slave1:
    datasource:
      name: soMysql
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      #基本属性
      url: jdbc:mysql://192.168.113.128:3306/security?characterEncoding=utf8&useSSL=true
      username: root
      password: root
      druid:
        # 相关配置
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat
        #配置初始化大小/最小/最大
        initial-size: 1
        min-idle: 1
        max-active: 20
        #获取连接等待超时时间
        max-wait: 60000
        #间隔多久进行一次检测，检测需要关闭的空闲连接
        time-between-eviction-runs-millis: 60000
        #一个连接在池中最小生存的时间
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 'x'
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        #connection-properties: druid.stat.mergeSql: true;druid.stat.slowSqlMillis: 5000
        # 合并多个DruidDataSource的监控数据
        #use-global-datasource-stat: true

二、数据源配置

package com.security.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.security.core.druid.DynamicDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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 tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Configuration
@MapperScan("com.security.persisence.mapper")//必须加这个，不加报错，如果不加，也可以在每个mapper上添加@Mapper注释
public class DruidDataSourceConfig {

    @Primary
    @Bean("master")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource masterDataSource() {
        return new DruidDataSource();
    }

    @Bean("slave1")
    @ConfigurationProperties(prefix = "spring.slave1.datasource")
    public DataSource slaveDataSource() {
        return new DruidDataSource();
    }

    /**
     * 构建动态数据源
     * @param masterDataSource
     * @param slaveDataSource
     * @return
     */
    @Bean
    public DynamicDataSource dynamicDataSource(@Qualifier("master") DataSource masterDataSource,
                                               @Qualifier("slave-1") DataSource slaveDataSource) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();

        targetDataSources.put("master", masterDataSource);
        targetDataSources.put("slave1", slaveDataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);

        List<Object> slaveDataSources = new ArrayList<Object>();
        slaveDataSources.add("slave1");

        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        dynamicDataSource.setSlaveDataSources(slaveDataSources);

        return dynamicDataSource;

    }
}

三、动态数据源切换

package com.security.core.druid;

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

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 动态数据源路由，读写使用主库，只读使用从库
 * <pre>
 *      轮训算法详解: https://github.com/wtstengshen/blog-page/wiki/Round-Robin
 *
 *      Netflix ribbon: https://www.oschina.net/p/ribbon
 * </pre>
 *
 * Created by rain.wen on 2017/7/31.
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    public final static Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);

    /**
     * 从数据源
     */
    private List<Object> slaveDataSources = new ArrayList<Object>();

    /**
     * 轮询计数
     */
    private AtomicInteger squence = new AtomicInteger(0);

    @Override
    public void afterPropertiesSet() {
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        Object key = DynamicDataSourceHolder.MASTER;
        //主库
        if (DynamicDataSourceHolder.isMaster()) {
            key = DynamicDataSourceHolder.MASTER;
        } else {
            //从库
            key = getSlaveKey();
        }
        logger.debug("==> select datasource key [{}]", key);
        return key;
    }

    public void setSlaveDataSources(List<Object> slaveDataSources) {
        this.slaveDataSources = slaveDataSources;
    }

    /**
     * 轮询获取从库
     *
     * @return
     */
    public Object getSlaveKey() {
        if (squence.intValue() == Integer.MAX_VALUE) {
            synchronized (squence) {
                if (squence.intValue() == Integer.MAX_VALUE) {
                    squence = new AtomicInteger(0);
                }
            }
        }
        int idx = squence.getAndIncrement() % slaveDataSources.size();
        return slaveDataSources.get(idx);
    }
}

四、记录当前线程操作的是需要操作主库还是从库

package com.security.core.druid;
/**
 * 动态数据源操作
 * Created by rain.wen on 2017/7/31.
 */
public class DynamicDataSourceHolder {

    /**
     * 主数据库标识
     */
    public static final String MASTER = "master";

    /**
     * 从数据库标识
     */
    public static final String SLAVE = "slave";

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

    private DynamicDataSourceHolder() {
        //
    }

    public static void putDataSource(String key) {
        holder.set(key);
    }

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

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

    public static boolean isMaster(){
        return holder.get().equals(MASTER);
    }

}

 五、动态数据源事物管理

　　 在开启事务的时候，根据只读事务、读写事务 选择操作是主库还是从库

package com.security.core.druid;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;

/**
 * 动态数据源事务管理
 * Created by rain.wen on 2017/7/31.
 */
public class DynamicDataSourceTransactionManager extends DataSourceTransactionManager {

    /**
     * 只读事务到读库，读写事务到写库
     * @param transaction
     * @param definition
     */
    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {
        //设置数据源
        boolean readOnly = definition.isReadOnly();
        if(readOnly) {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceHolder.SLAVE);
        } else {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceHolder.MASTER);
        }
        super.doBegin(transaction, definition);
    }

    /**
     * 清理本地线程的数据源
     * @param transaction
     */
    @Override
    protected void doCleanupAfterCompletion(Object transaction) {
        super.doCleanupAfterCompletion(transaction);
        DynamicDataSourceHolder.clearDataSource();
    }
}

六、容器启动配置

　　动态DataSource 注入到MyBatis、以及事务配置

package com.security.config;

import org.apache.ibatis.session.Configuration;
import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "mybatis")
@org.springframework.context.annotation.Configuration
public class MybatisConfig {
    private String mapperLocations;
    private String typeAliasesPackage;
    private Configuration configuration;

    public String getMapperLocations() {
        return mapperLocations;
    }

    public void setMapperLocations(String mapperLocations) {
        this.mapperLocations = mapperLocations;
    }

    public String getTypeAliasesPackage() {
        return typeAliasesPackage;
    }

    public void setTypeAliasesPackage(String typeAliasesPackage) {
        this.typeAliasesPackage = typeAliasesPackage;
    }

    public Configuration getConfiguration() {
        return configuration;
    }

    public void setConfiguration(Configuration configuration) {
        this.configuration = configuration;
    }
}

package com.security.config;

import com.security.core.druid.DynamicDataSource;
import com.security.core.druid.DynamicDataSourceTransactionManager;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.io.IOException;
import java.sql.SQLException;

/**
 * 动态数据源注入MyBatis、事务配置
 *
 */
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
public class PlatformDynamicDataSourceConfig {

    @Bean
    @ConditionalOnBean({MybatisConfig.class,DynamicDataSource.class})
    public SqlSessionFactoryBean sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dataSource,MybatisConfig mybatisConfig) throws SQLException, IOException {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mybatisConfig.getMapperLocations()));
        sqlSessionFactoryBean.setTypeAliasesPackage(mybatisConfig.getTypeAliasesPackage());
        sqlSessionFactoryBean.setConfiguration(mybatisConfig.getConfiguration());
        return sqlSessionFactoryBean;
    }

    @Bean
    public DynamicDataSourceTransactionManager transactionManager(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) {
        DynamicDataSourceTransactionManager dynamicDataSourceTransactionManager = new DynamicDataSourceTransactionManager();
        dynamicDataSourceTransactionManager.setDataSource(dataSource);
        return dynamicDataSourceTransactionManager;
    }


}

　　MybatisConfig主要是因为定义了 SqlSessionFactory，配置会失效，不会自动注入，因此需要自定义加载配置信息

七、通过AOP 自定义注解实现主库到备库的切换

自定义注解

package com.security.core.annotation;

import java.lang.annotation.*;

/**
 *
 *用于动态数据源的切换，默认是查询主库
 *
 * @create-time 2019-8-12   下午02:22:28
 * @revision
 */

//该注解用于方法声明
@Target({ElementType.METHOD,ElementType.TYPE})
//VM将在运行期也保留注释，因此可以通过反射机制读取注解的信息
@Retention(RetentionPolicy.RUNTIME)
//将此注解包含在javadoc中
@Documented
//允许子类继承父类中的注解
@Inherited
public @interface DataSource {
    public String name();


}

注解切面实现

package com.security.core.druid;


import com.security.core.annotation.DataSource;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * 根据注解 来拦截切换哪个动态数据源
 */
@Aspect
@Component
public class DynamicDataSourceAspect {

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

    @Pointcut("@within(com.security.core.annotation.DataSource) || @annotation(com.security.core.annotation.DataSource)")
    private void dataSourcePointout() {
    }

    @Around("dataSourcePointout()")
    public Object aroundMethod(ProceedingJoinPoint pjp) throws Throwable {
        MethodSignature methodSignature = (MethodSignature)pjp.getSignature();
        Method method = methodSignature.getMethod();
        DataSource dynamicDataSource = null;
        if(method.isAnnotationPresent(DataSource.class)){
            dynamicDataSource = method.getAnnotation(DataSource.class);
        }
        else{ //查找类的注解
            dynamicDataSource = AnnotationUtils.findAnnotation(method.getDeclaringClass(),DataSource.class);
        }

        if (dynamicDataSource != null){
            DynamicDataSourceHolder.putDataSource(dynamicDataSource.name());
        }
        return pjp.proceed();
    }

    @AfterReturning(returning = "result", pointcut = "dataSourcePointout()")
    public void afterMethod(Object result) {
        DynamicDataSourceHolder.clearDataSource();
    }
}

八、mybatis.configuration 的所有配置无效 原因分析

　　因为tk.mybatis 当SqlSessionFactory不存在定义的时候定义SqlSessionFactory，所以用tk.mybatis的时候,就不要手动注册 SqlSessionFactory 对象了

　　根据springboot的自动注入配置类,找到 MapperAutoConfiguration, 这里定义了 SqlSessionFactory　　

    private final MybatisProperties properties;

    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    // 版面原因,省略了无关的属性设置
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        Configuration configuration = this.properties.getConfiguration();
        if (configuration != null && !CollectionUtils.isEmpty(this.configurationCustomizers)) {
            for (ConfigurationCustomizer customizer : this.configurationCustomizers) {
                customizer.customize(configuration);
            }
        }
        factory.setConfiguration(configuration);
        return factory;
     }

　　通过 SqlSessionFactory 设置configuration的地方发现, 是通过 MybatisProperties.getConfiguration() 获得的 

@ConfigurationProperties(prefix = MybatisProperties.MYBATIS_PREFIX)
public class MybatisProperties {

 // 版面原因,省略了无关的属性设置
  public static final String MYBATIS_PREFIX = "mybatis";

  @NestedConfigurationProperty
  private Configuration configuration;
}

　　tk.mybatis做了很多属性的映射,详细的设置请自己查看 MapperAutoConfiguration 的源码。

　　我们自定义 SqlSessionFactory 的时候,只要注入 MybatisProperties . 就可以解决mybatis配置信息失效问题，因此SqlSessionFactoryBean自定义调整为如下代码

PlatformDynamicDataSourceConfig   

MybatisConfig 去掉，用MybatisProperties自动注入属性类代替

package com.security.config;

import com.security.core.druid.DynamicDataSource;
import com.security.core.druid.DynamicDataSourceTransactionManager;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import tk.mybatis.mapper.autoconfigure.MybatisProperties;

import java.io.IOException;

/**
 * 动态数据源注入MyBatis、事务配置
 *
 */
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
public class PlatformDynamicDataSourceConfig {

    @Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dataSource, MybatisProperties mybatisProperties) throws Exception, {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(mybatisProperties.resolveMapperLocations());
        sqlSessionFactoryBean.setTypeAliasesPackage(mybatisProperties.getTypeAliasesPackage());
        sqlSessionFactoryBean.setTypeHandlersPackage(mybatisProperties.getTypeHandlersPackage());
        sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration());
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public DynamicDataSourceTransactionManager transactionManager(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) {
        DynamicDataSourceTransactionManager dynamicDataSourceTransactionManager = new DynamicDataSourceTransactionManager();
        dynamicDataSourceTransactionManager.setDataSource(dataSource);
        return dynamicDataSourceTransactionManager;
    }


}

 

源码参考链接：https://gitee.com/rainwen/spring-boot-showcase