[Java知识库] SpringBoot多数据源AbstractRoutingDataSource

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> SpringBoot多数据源AbstractRoutingDataSource -> 正文阅读

[Java知识库]SpringBoot多数据源AbstractRoutingDataSource

1.自定义一个DataSource，继承AbstractRoutingDataSource，这里命名为DynamicDataSource

import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    //默认
    public static String defaultDataSource="mysqlDatasource";
    private static final ThreadLocal<String> contextHolder=new ThreadLocal<>();

    public static DynamicDataSource getInstance(DataSource defaultDataSource,Map<Object,Object> map){
        DynamicDataSource instance = new DynamicDataSource(defaultDataSource,map);
        instance.setDefaultTargetDataSource(defaultDataSource);
        instance.setTargetDataSources(map);
        instance.afterPropertiesSet();
        return instance;
    }

    private DynamicDataSource(DataSource defaultDataSource,Map<Object,Object> map){
        super.setDefaultTargetDataSource(defaultDataSource);
        super.setTargetDataSources(map);
        super.afterPropertiesSet();
    }

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


    public static void setDataSource(String dataSource,Map<Object,Object> map){
        contextHolder.set(dataSource);
        DataSource targetDataSource=(DataSource) map.get((Object)dataSource);
        DynamicDataSource.getInstance(targetDataSource,map);
    }

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

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


}

2.在配置文件里配置相关的数据源信息，这里以一个sqlserver库，一个mysql库作为示例

spring:
  application:
    name: ${app.name}
  profiles:
    active: LOCAL
  datasource:
    dynamic:
      primary: mysql
      datasource:
        mysql:
          url: jdbc:mysql://xxx:3306/demo_plus
          username: xxx
          password: xxx
        sqlserver:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          url: jdbc:sqlserver://xxx:14481;DatabaseName=demo_plus
          username: xxx
          password: xxx
#          druid:
          filters: stat
          # 初始化连接大小
          initial-size: 5
          # 最小空闲连接数
          min-idle: 5
          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的大小
          pool-prepared-statements: true
          max-open-prepared-statements: 20
          max-pool-prepared-statement-per-connection-size: 20

mysql数据源：

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
@MapperScan(basePackages = {"com.walmart.vendor.app.datasource.mysql.repository.mapper"},
        sqlSessionFactoryRef = "mysqlSqlSessionFactory",
        sqlSessionTemplateRef ="mysqlSqlSessionTemplate")
public class MysqlDatasourceConfig {

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

    @Bean
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDatasource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*.xml"));
        return bean.getObject();
    }

    @Bean
    public SqlSessionTemplate mysqlSqlSessionTemplate(
            @Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

sqlserver数据源：

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
@MapperScan(basePackages = "com.walmart.vendor.app.datasource.sqlserver.repository.mapper",
        sqlSessionFactoryRef = "sqlserverSqlSessionFactory",
        sqlSessionTemplateRef ="sqlserverSqlSessionTemplate")
public class SqlserverDatasourceConfig {

    @Bean(name = "sqlserverDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.sqlserver")
    public DataSource sqlserver() {
        return DruidDataSourceBuilder.create().build();
    }


    @Bean
    public SqlSessionFactory sqlserverSqlSessionFactory(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/sqlserver/*.xml"));
        return bean.getObject();
    }


    @Bean
    public SqlSessionTemplate sqlserverSqlSessionTemplate(
            @Qualifier("sqlserverSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

dynamic数据源：

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionManager;

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.dynamic")
public class DynamicDataSourceProperties  {

    @Primary
    @Bean("dynamicDataSource")
    public DynamicDataSource dataSource(@Qualifier("mysqlDatasource") DataSource mysqlDatasource,@Qualifier("sqlserverDataSource")DataSource sqlserverDatasource){
        Map<Object,Object> map=new HashMap<>();
        map.put("mysqlDatasource",mysqlDatasource);
        map.put("sqlserverDataSource",sqlserverDatasource);
        DynamicDataSource dataSource=DynamicDataSource.getInstance(mysqlDatasource,map);
        return  dataSource;
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "mybatis")
    public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dataSource
//            , @Qualifier("MybatisPlusInterceptor") MybatisPlusInterceptor mybatisPlusInterceptor
    ) {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        //分页插件
//        Interceptor[] plugins = new Interceptor[1];
//        plugins[0] = (Interceptor)mybatisPlusInterceptor;
//        sqlSessionFactoryBean.setPlugins(plugins);
        return sqlSessionFactoryBean;
    }
    @Bean("txManager")
    @Primary
    public TransactionManager txManager(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

3定义一个注解DataSourceAnno

package com.walmart.vendor.app.anno;

import static java.lang.annotation.ElementType.ANNOTATION_TYPE;
import static java.lang.annotation.ElementType.CONSTRUCTOR;
import static java.lang.annotation.ElementType.FIELD;
import static java.lang.annotation.ElementType.METHOD;
import static java.lang.annotation.ElementType.PARAMETER;
import static java.lang.annotation.ElementType.TYPE;
import static java.lang.annotation.RetentionPolicy.CLASS;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.Target;

@Target({METHOD, FIELD, ANNOTATION_TYPE, CONSTRUCTOR, PARAMETER,TYPE})
@Retention(RUNTIME)
@Documented
public @interface DataSourceAnno {

    String name() default "mysqlDatasource";


}

4编写相关切面类

import com.walmart.vendor.app.anno.DataSourceAnno;
import com.walmart.vendor.app.config.datasource.DynamicDataSource;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;

@Component
@Aspect
@Slf4j
public class DataSourceAspect {
    @Resource(name = "mysqlDatasource")
    private DataSource mysqlDatasource;

    @Resource(name="sqlserverDataSource")
    private DataSource sqlserverDataSource;



    @Pointcut("execution(* com.xxx.vendor.app.service.*Service.*(..))")
    public void pointCut(){
    }

    @Before("pointCut()")
    public void begin(JoinPoint joinPoint){

    }

    @Around("pointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable{
        MethodSignature signature =(MethodSignature)point.getSignature();
        Method method = signature.getMethod();
        DataSourceAnno ds = method.getAnnotation(DataSourceAnno.class);

        //根据參数获取数据源
        String dataSourceType = new String ();
        Object [] paramList;
        paramList = point.getArgs();
        String str=paramList.toString();
        if(paramList !=null && paramList.length!=0) {
            Map<String, String> param = (Map<String, String>) paramList[0];

            log.info(str);
            dataSourceType = param.get("DATASOURCE");
        }
        Map<Object,Object> map=new HashMap<>();
        map.put("mysqlDatasource",mysqlDatasource);
        map.put("sqlserverDataSource",sqlserverDataSource);

        //根据参数获取数据源
        if (ds==null){
            DynamicDataSource.setDataSource(DynamicDataSource.defaultDataSource,map);

        } else if (dataSourceType !=null&&!dataSourceType.equals ("")){
            //根据参数获取数据源
            DynamicDataSource.setDataSource(dataSourceType,map);

        } else{
            DynamicDataSource.setDataSource(ds.name(),map);
        }

            try{
                return point.proceed();
            }
             finally{
                DynamicDataSource.removeDataSource ();
            }

    }

    @After("pointCut()")
    public void close(){

    }
}

5在要切换数据源的位置加上注解

import com.walmart.alohaframework.core.log.annotation.IgnoreLog;
import com.walmart.vendor.app.anno.DataSourceAnno;
import com.walmart.vendor.app.datasource.mysql.convert.MysqlUserConverter;
import com.walmart.vendor.app.datasource.mysql.model.vo.MysqlUserVO;
import com.walmart.vendor.app.datasource.mysql.repository.MysqlUserRepository;
import java.util.List;
import javax.annotation.Resource;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@Service
@DataSourceAnno(name = "mysqlDatasource")
public class MysqlUserService {
    @Autowired
    MysqlUserRepository mysqlUserRepository;

    @IgnoreLog
    @Transactional(transactionManager = "txManager")
    @DataSourceAnno(name = "mysqlDatasource")
    public List<MysqlUserVO> listUser() {
        return MysqlUserConverter.INSTANCE.toVO(mysqlUserRepository.list());
    }
}