SpringBoot-整合Mysql多数据源

1、数据源配置

# 主数据源配置
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.master.driverClassName=com.mysql.jdbc.Driver
spring.datasource.master.url=jdbc:mysql://XXX/invoo2e_data?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true 
spring.datasource.master.username=invoo_dba
spring.datasource.master.password=invoo_db_2015
spring.datasource.master.name=invoo2e_data
spring.datasource.master.host=3306
#dataSource Pool configuration
## 初始化大小，最小，最大
spring.datasource.master.initialSize=5
spring.datasource.master.minIdle=5
spring.datasource.master.maxActive=20
## 配置获取连接等待超时的时间
spring.datasource.master.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.master.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.master.minEvictableIdleTimeMillis=300000
spring.datasource.master.validationQuery=SELECT 1 FROM DUAL
spring.datasource.master.testWhileIdle=true
spring.datasource.master.testOnBorrow=false
spring.datasource.master.testOnReturn=false
spring.datasource.master.poolPreparedStatements=true
spring.datasource.master.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.master.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.master.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# 定时任务数据源
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.secondary.url=jdbc:mysql://XXX/invoo2e_server?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false
spring.datasource.secondary.username=invoo_dba
spring.datasource.secondary.password=invoo_db_2015
spring.datasource.secondary.name=invoo2e_server
spring.datasource.secondary.host=3306


mybatis.typeAliasesPackage=com.invoo.entity.*

2、新建DataSourceContextHolder类获取动态数据源

package com.invoo.config.dataSource;

import lombok.extern.slf4j.Slf4j;

/**
 * 动态数据源
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:27
 **/
@Slf4j
public class DataSourceContextHolder {
    /**
     * 默认数据源
     */
    public static final String DEFAULT_DS = "master";

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

    // 设置数据源名
    public static void setDB(String dbType) {
        log.info("切换到{}数据源", dbType);
        contextHolder.set(dbType);
    }

    // 获取数据源名
    public static String getDB() {
        return (contextHolder.get());
    }
    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }
}

3、动态数据源设置

package com.invoo.config.dataSource;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:27
 **/
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        log.info("数据源为{}", DataSourceContextHolder.getDB());
        return DataSourceContextHolder.getDB();
    }
}

4、新建数据源配置DataSourceConfig类

/**
 * 多数据源配置
 * @author: HongHaoYuan
 * @create: 2018-07-23 12:59
 **/
@Configuration
@Slf4j
public class DataSourceConfig {
    /**
     * @return
     * @Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。
     * 「多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean」
     */
    @Bean(name = "masterDataSourcre")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DruidDataSource initDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSourcre")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     *
     * @return
     */
    @Bean(name = "dynamicDS1")
    public DataSource dataSource(@Qualifier("masterDataSourcre") DruidDataSource masterDataSourcre,
                                 @Qualifier("secondaryDataSourcre") DataSource secondaryDataSourcre) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(masterDataSourcre);
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap(2);
        dsMap.put("master", masterDataSourcre);
        dsMap.put("secondary", secondaryDataSourcre);
        dynamicDataSource.setTargetDataSources(dsMap);
//        log.info("【数据库】name1={},name2={}",masterDataSourcre.getName(),secondaryDataSourcre.getClass().getName());
        return dynamicDataSource;
    }
}

5、主数据源配置

package com.invoo.config.dataSource;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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;

/**
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:18
 **/
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = {MybatisMasterConfig.PACKAGE},sqlSessionFactoryRef = "masterSqlSessionFactory")
@Slf4j
public class MybatisMasterConfig  {

    static final String PACKAGE = "com.invoo.repository.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

    @Autowired
    @Qualifier("masterDataSourcre")
    private DataSource dataSource;

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactoryMaster() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 使用master数据源
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources(MybatisMasterConfig.MAPPER_LOCATION));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplateMaster() throws Exception {
        // 使用上面配置的Factory
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactoryMaster());
        return template;
    }

    
}

6、从数据源配置

package com.invoo.config.dataSource;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:24
 **/
@Configuration
@Slf4j
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = {MybatisSecondaryConfig.PACKAGE},sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class MybatisSecondaryConfig {

    static final String PACKAGE = "com.invoo.repository.secondary";
    static final String MAPPER_LOCATION = "classpath:mapper/secondary/*.xml";

    @Autowired
    @Qualifier("secondaryDataSourcre")
    private DataSource dataSource;

   /* @Bean(name = "secondaryDataSourcre")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }*/
    /*@Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager master2TransactionManager() {
        return new DataSourceTransactionManager(secondaryDataSource());
    }*/
    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactorySecondary() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MybatisSecondaryConfig.MAPPER_LOCATION));
        return factoryBean.getObject();

    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplateSecondary() throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactorySecondary());
        return template;
    }
}

7、新建数据源注解类

package com.invoo.util.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:38
 **/
/*注解存在的范围*/
@Retention(RetentionPolicy.RUNTIME)
/*注解作用的目标*/
@Target({ElementType.METHOD})
public @interface DS {
    String value() default "master";
}

8、利用springAOP切面切换数据源

package com.invoo.aspect;

import com.invoo.util.annotation.DS;
import com.invoo.config.dataSource.DataSourceContextHolder;
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;

/**
 * @author: HongHaoYuan
 * @create: 2018-07-23 16:39
 **/
@Aspect
@Component
@Slf4j
public class DynamicDataSourceAspect {

    @Before("@annotation(com.invoo.util.annotation.DS)")
    public void beforeSwitchDS(JoinPoint point) {
        Class<?> className = point.getTarget().getClass();
//        log.info("【获得当前访问的class】className={}",className);
        //获得访问的方法名
        String methodName = point.getSignature().getName();
        //得到方法的参数的类型
        Class[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes();
        String dataSource = DataSourceContextHolder.DEFAULT_DS;
        try {
            // 得到访问的方法对象
            Method method = className.getMethod(methodName, argClass);
            log.info("【判断是否存在@DS注解】boolean={}",method.isAnnotationPresent(DS.class));
            if (method.isAnnotationPresent(DS.class)) {
                DS annotation = method.getAnnotation(DS.class);
                // 取出注解中的数据源名
                dataSource = annotation.value();
                log.info("【取出注解中的数据源名】dataSource={}",dataSource);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 切换数据源
        DataSourceContextHolder.setDB(dataSource);
    }

    @After("@annotation(com.invoo.util.annotation.DS)")
    public void afterSwitchDS(JoinPoint point) {
        DataSourceContextHolder.clearDB();
    }
}

9、使用

package com.invoo.repository.secondary;

import com.github.pagehelper.Page;
import com.invoo.entity.secondary.WxAttentionUser;
import com.invoo.util.annotation.DS;

/**
 * @author: HongHaoYuan
 * @create: 2018-08-08 10:23
 **/
public abstract interface AbstractRepository<T>{

    /**
     * 每周日同步全量数据
     * @return
     */
    @DS("secondary")
    Page<T> pageQueryBySum();

    /**
     * 每周一至周五同步增量数据
     * @return
     */
    @DS("secondary")
    Page<T> pageQueryByDays();
}