Java SpringBoot配置多数据源 DS springboot配置多数据源无效

转载

jiecho 2024-02-19 19:24:40

大概思路

在appliaction.yml中配置数据源信息，在数据源配置中读取yml中的值，将配置好的数据源放入sqlSessionFactory中，接下来需要考虑多数据源切换问题，采用如下方式，自定义一个注解，将注解放到类上（放方法上太麻烦），当使用aop拦截时出现以下问题：1、@within当方法执行时将注解放到类上是拦截不到的 2：通过execution可以拦截到但总是在切换数据源之后执行。因此放弃aop拦截的方式，改用通过实现Mybatis拦截器进行数据源切换。

1. 首先在application.yml中加入配置：

spring:
	jdbc:
	  # mysql default
	  default:
	    type: mysql
	    username: root
	    password: root
	    url: jdbc:mysql://localhost:3306/zw_eq_js?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
	    driver-class-name: com.mysql.jdbc.Driver
	
	  # mysql 2
	  mysql2:
	    type: mysql
	    username: root
	    password: Zw55350002@
	    url: jdbc:mysql://localhost:3307/zw_eq_js?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
	    driver-class-name: com.mysql.jdbc.Driver

2. 数据源初始化：读取application.yml中数据源的配置，将所有数据源放到routingDataSource（动态数据源）中:

package com.test.config.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import com.test.common.io.PropertiesUtils;
import com.test.common.lang.StringUtils;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
* 数据源配置（支持多数据源）
*/
@Order(-1)
@Configuration
public class DataSourceConfig {

   public static Map<Object,Object> targetDataSource = new HashMap<>();

//    @Primary // 如没有配置则默认使用
//    @Bean(name = "dataSourceOne")
//    public DataSource dataSourceOne() {
//        String prefix = "spring.jdbc.one.";
//        return getDataSource(prefix,"dataSourceOne");
//    }
//
   @Bean(name = "dataSource")
   public RoutingDataSource dataSource() throws SQLException {
       RoutingDataSource a = new RoutingDataSource();
       getDataSource(null, a);
       return a;
   }

   public static DataSource getDataSource(String dataSourceName, RoutingDataSource routingDataSource){
       PropertiesUtils _env = PropertiesUtils.getInstance();
       List<String> dataSourceNames = new ArrayList<>();
       if(StringUtils.isEmpty(dataSourceName)){
           Properties p = _env.getProperties();
           Enumeration en = p.propertyNames();
           while (en.hasMoreElements()){
               Object el = en.nextElement();
               String elStr = String.valueOf(el);
               if(el != null && StringUtils.startsWith(elStr,"spring.jdbc") ){
                   String[] elArr = elStr.split("\\.");
                   if(!dataSourceNames.contains(elArr[2])){
                       dataSourceNames.add(elArr[2]);
                   }
               }
           }
       }else{
           dataSourceNames.add(dataSourceName);
       }
       String preFix = "spring.jdbc.";
       for(String _dataSourceName : dataSourceNames){
           dataSourceName = _dataSourceName;
           _dataSourceName = preFix + _dataSourceName;
           Properties prop = build(_dataSourceName);
           Object dataSource = null;
           if("true".equalsIgnoreCase(_env.getProperty("spring.jta.enabled"))){ // 开启分布式事务
               AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
               ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
               ds.setUniqueResourceName(dataSourceName);
               ds.setXaProperties(prop);
               dataSource = ds;
           } else{
               DruidDataSource ds = new DruidDataSource();
               ds.setName(dataSourceName);
               ds.setUrl(prop.getProperty("url"));
               ds.setUsername(prop.getProperty("username"));
               ds.setPassword(prop.getProperty("password"));
               ds.setDriverClassName(prop.getProperty("driverClassName"));
               ds.setInitialSize(Integer.valueOf(prop.getProperty("initialSize")));
               ds.setMaxActive(Integer.valueOf(prop.getProperty("maxActive")));
               ds.setMinIdle(Integer.valueOf(prop.getProperty("minIdle")));
               ds.setMaxWait(Integer.valueOf(prop.getProperty("maxWait")));
               ds.setTestOnBorrow(Boolean.valueOf(prop.getProperty("testOnBorrow")));
               ds.setTestOnReturn(Boolean.valueOf(prop.getProperty("testOnReturn")));
               ds.setTimeBetweenEvictionRunsMillis(Long.valueOf(prop.getProperty("timeBetweenEvictionRunsMillis")));
               ds.setMinEvictableIdleTimeMillis(Long.valueOf(prop.getProperty("minEvictableIdleTimeMillis")));
               ds.setRemoveAbandoned(Boolean.valueOf(prop.getProperty("removeAbandoned")));
               ds.setRemoveAbandonedTimeout(Integer.valueOf(prop.getProperty("removeAbandonedTimeout")));
               ds.setPoolPreparedStatements(true);
               ds.setMaxPoolPreparedStatementPerConnectionSize(ds.getMaxActive());
               dataSource = ds;
           }
           // 添加动态数据源上下文
//            DynamicDataSourceContextHolder.dataSourceNames.add(dataSourceName);
           // 设置默认数据源
           if("default".equalsIgnoreCase(dataSourceName)){
               routingDataSource.setDefaultTargetDataSource(dataSource);
//                DynamicDataSourceContextHolder.setDataSourceName(_dataSourceName);
           }else{
               targetDataSource.put(dataSourceName,dataSource);
           }

       }
       // 注册目标数据源
       routingDataSource.setTargetDataSources(targetDataSource);
       routingDataSource.afterPropertiesSet();
       return routingDataSource;
   }

   protected static Properties build(String prefix) {
       Properties prop = new Properties();
       PropertiesUtils _env = PropertiesUtils.getInstance();
       prefix += ".";
       prop.put("url", _env.getProperty(prefix + "url"));
       prop.put("username", _env.getProperty(prefix + "username"));
       prop.put("password", _env.getProperty(prefix + "password"));
       prop.put("driverClassName", _env.getProperty(prefix + "driver-class-name", ""));
       prop.put("initialSize", _env.getProperty("spring.pool.init"));
       prop.put("maxActive", _env.getProperty("spring.pool.maxActive"));
       prop.put("minIdle", _env.getProperty("spring.pool.minIdle"));
       prop.put("maxWait", _env.getProperty("spring.pool.maxWait"));
//        prop.put("poolPreparedStatements", _env.getProperty(prefix + "poolPreparedStatements"));
//        prop.put("maxPoolPreparedStatementPerConnectionSize",
//                _env.getProperty(prefix + "maxPoolPreparedStatementPerConnectionSize"));
//        prop.put("validationQuery", _env.getProperty(prefix + "validationQuery"));
//        prop.put("validationQueryTimeout", _env.getProperty(prefix + "validationQueryTimeout"));
       prop.put("testOnBorrow", _env.getProperty("spring.pool.testOnBorrow"));
       prop.put("testOnReturn", _env.getProperty("spring.pool.testOnReturn"));
//        prop.put("testWhileIdle", _env.getProperty(prefix + "testWhileIdle"));
       prop.put("timeBetweenEvictionRunsMillis", _env.getProperty("spring.pool.timeBetweenEvictionRunsMillis"));
       prop.put("minEvictableIdleTimeMillis", _env.getProperty("spring.pool.minEvictableIdleTimeMillis"));
       prop.put("removeAbandoned", _env.getProperty("spring.pool.removeAbandoned"));
       prop.put("removeAbandonedTimeout", _env.getProperty("spring.pool.removeAbandonedTimeout"));
//        prop.put("useGlobalDataSourceStat",_env.getProperty(prefix + "useGlobalDataSourceStat"));
//        prop.put("filters", _env.getProperty(prefix + "filters"));
       return prop;
   }
}

3. 其中动态数据源的定义如下：

package com.test.config.datasource;

import com.test.common.lang.StringUtils;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

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

/**
 * 多数据源
 */
public class RoutingDataSource extends AbstractRoutingDataSource {
    private Map<Object, Object> targetDataSources;
    private Object defaultTargetDataSource;

    public RoutingDataSource() {
    }

    @Override
    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        super.setDefaultTargetDataSource(this.defaultTargetDataSource = defaultTargetDataSource);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        /*
         * DynamicDataSourceContextHolder代码中使用setDataSourceName
         * 设置当前的数据源，在路由类中使用getDataSourceName进行获取，
         *  交给AbstractRoutingDataSource进行注入使用。
         */

        return DynamicDataSourceContextHolder.getDataSourceName();

    }

    public DataSource getTargetDataSource(String dataSourceName) {
        if (!StringUtils.isBlank(dataSourceName) && !"default".equals(dataSourceName)) {
            Object a;
            return (a = this.targetDataSources.get(dataSourceName)) == null ? (DataSource)this.defaultTargetDataSource : (DataSource)a;
        } else {
            return (DataSource)this.defaultTargetDataSource;
        }
    }

    public void removeTargetDataSource(String dataSourceName) {
        this.targetDataSources.remove(dataSourceName);
        super.afterPropertiesSet();
    }

    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(this.targetDataSources = targetDataSources);
    }

    public void addTargetDataSource(String dataSourceName, DataSource dataSource) {
        this.targetDataSources.put(dataSourceName, dataSource);
        super.afterPropertiesSet();
    }
}
package com.test.config.datasource;

import org.apache.commons.lang3.StringUtils;

/**
 * 动态数据源上下文，在Mybatis拦截器（DataSourceInterceptor）中修改当前数据源
 * @author lyh
 * @version v.0.1
 */

public class DynamicDataSourceContextHolder {
    public static final String DEFAULT = "default";
    private static final ThreadLocal<String> dataSourceNames = new ThreadLocal<>();

    public static String getDataSourceName() {
        return (String)dataSourceNames.get();
    }

    public DynamicDataSourceContextHolder() {
    }

    public static void setDataSourceName(String dataSourceName) {
        if (!StringUtils.isBlank(dataSourceName) && !"default".equals(dataSourceName)) {
            DynamicDataSourceContextHolder.dataSourceNames.set(dataSourceName);
        } else {
            clearDataSourceName();
        }
    }

    public static void clearDataSourceName() {
        dataSourceNames.remove();
    }
}

4. 将数据源配置到sqlSessionFactory中：

package com.test.config.mybatis;

import com.test.annotation.MyBatisDao;
import com.test.base.dao.BaseEntity;
import com.test.common.io.PropertiesUtils;
import com.test.config.datasource.DataSourceInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
public class MyBatisConfig{

    @Bean(name="sqlSessionFactory")
    public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setPlugins(new Interceptor[]{new DataSourceInterceptor()});
        // 因springboot和mybatis整合配置文件不支持TypeAliasesSuperType（只会扫描其类下的子类）及TypeAliasesPackage的正则因此使用此配置文件进行配置
        factory.setTypeAliasesSuperType(BaseEntity.class);
        // 指定entity包路径
        factory.setTypeAliasesPackage(PropertiesUtils.getInstance().getProperty("mybatis.typeAliasesPackage"));
        // 指定xml文件位置
        factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(PropertiesUtils.getInstance().getProperty("mybatis.mapper-locations")));
        factory.setDataSource(dataSource);
        // 配置自定义的事务工厂
        factory.setTransactionFactory(new TransactionFactory());
        factory.setVfs(SpringBootVFS.class);
        return factory;
    }

    @Bean
    public static MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer a = new MapperScannerConfigurer();
        a.setSqlSessionFactoryBeanName("sqlSessionFactory");
        a.setBasePackage("com.test");
        a.setAnnotationClass(MyBatisDao.class);
        return a;
    }
}

5. 动态数据源的事务配置：

package com.test.config.mybatis;

import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.ibatis.transaction.Transaction;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;

import javax.sql.DataSource;

/**
 * 事务工厂，在创建sqlSessionFactory时注入此工厂使用自定义的事务CustomizeTransaction
 */
public class TransactionFactory extends SpringManagedTransactionFactory {

    public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
        return (Transaction)(new CustomizeTransaction(dataSource));
    }

    public TransactionFactory() {
    }
}

package com.test.config.mybatis;

import com.test.common.collect.MapUtils;
import com.test.common.lang.StringUtils;
import com.test.config.datasource.DynamicDataSourceContextHolder;
import com.test.config.datasource.RoutingDataSource;
import org.apache.ibatis.transaction.Transaction;
import org.mybatis.spring.transaction.SpringManagedTransaction;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;

/**
 * 自定义事务，getConnection时根据数据源上下文（当前数据源名称，没有则为默认）生成相应数据源
 *
 */
public class CustomizeTransaction implements Transaction {
    private Map<String, SpringManagedTransaction> A;
    private RoutingDataSource K;

    public void commit() throws SQLException {
        Iterator ite;
        for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
            ((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).commit();
        }
    }

    public void rollback() throws SQLException {
        Iterator ite;
        for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
//            Object obj = ite.next();
            SpringManagedTransaction springManagedTransaction = (SpringManagedTransaction)((Map.Entry)ite.next()).getValue();
            springManagedTransaction.rollback();
//            System.out.println(obj);
//            ((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).rollback();
        }

    }

    public Connection getConnection() throws SQLException {
        String dataSourceName = DynamicDataSourceContextHolder.getDataSourceName();
        SpringManagedTransaction springManagedTransaction;
        if (StringUtils.isEmpty(dataSourceName) || (springManagedTransaction = (SpringManagedTransaction)this.A.get(dataSourceName)) == null) {
            DataSource dataSource = this.K;
            if (!"default".equals(dataSourceName) && !StringUtils.isEmpty(dataSourceName)) {
                dataSource = this.K.getTargetDataSource(dataSourceName);
            }else{
                dataSourceName = "default";
            }

            springManagedTransaction = new SpringManagedTransaction((DataSource)dataSource);
            this.A.put(dataSourceName, springManagedTransaction);
        }
        return springManagedTransaction.getConnection();
    }

    public CustomizeTransaction(DataSource dataSource) {
        this.K = (RoutingDataSource)dataSource;
        this.A = MapUtils.newConcurrentMap();
    }

    public Integer getTimeout() throws SQLException {
        String dataSourceName = DynamicDataSourceContextHolder.getDataSourceName();
        if(StringUtils.isEmpty(dataSourceName)){
            dataSourceName = "default";
        }
        SpringManagedTransaction springManagedTransaction;
        return (springManagedTransaction = (SpringManagedTransaction)this.A.get(dataSourceName)) != null ? springManagedTransaction.getTimeout() : null;
    }

    public void close() throws SQLException {
        Iterator ite;
        for(Iterator ite1 = ite = this.A.entrySet().iterator(); ite1.hasNext(); ite1 = ite) {
            ((SpringManagedTransaction)((Map.Entry)ite.next()).getValue()).close();
        }
        this.A.clear();
    }
}

6. 配置数据源管理器：

package com.test.config.mybatis;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import com.test.config.datasource.RoutingDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.jta.JtaTransactionManager;

import javax.sql.DataSource;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;

/**
 * 配置事务管理器（一版事务管理器（DataSourceTransactionManager）及分布式的事务管理器（atomikosTransactionManager））
 * 配置多数据源后，使用非默认数据源时事务失效
 * 原因：jeesite只配置了一个事务管理器，动态切换数据源后使用的还是之前默认的事务管理器，因此失效。
 * 解决方案：针对每个数据源配置其事务管理器，在@Transactional注解后标注自己要使用的事务管理器，但只针对每个数据源内部事务，跨数据源还是要使用jta分布式事务管理
 */
@Configuration
@EnableTransactionManagement
public class TransactionConfig {
    @Bean(
            initMethod = "init",
            destroyMethod = "close"
    )
    @ConditionalOnProperty(
            name = {"spring.jta.enabled"},
            havingValue = "true",
            matchIfMissing = false
    )
   public TransactionManager atomikosTransactionManager() throws Throwable {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(false);
        return userTransactionManager;
    }

    public TransactionConfig() {
    }

    @Bean({"transactionManager"})
    @DependsOn({"userTransaction", "atomikosTransactionManager"})
    @ConditionalOnProperty(
            name = {"spring.jta.enabled"},
            havingValue = "true",
            matchIfMissing = false
    )
    @Primary
    public PlatformTransactionManager jtaTransactionManager(UserTransaction userTransaction, TransactionManager atomikosTransactionManager) throws Throwable {
        return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
    }

    @ConditionalOnProperty(
            name = {"spring.jta.enabled"},
            havingValue = "false",
            matchIfMissing = true
    )
    @Bean("transactionManager")
    @Qualifier("transactionManager")
    @Primary
    public PlatformTransactionManager transactionManager(RoutingDataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManagernew = new DataSourceTransactionManager(dataSource);
        return dataSourceTransactionManagernew;
    }

    @ConditionalOnProperty(
            name = {"spring.jta.enabled"},
            havingValue = "false",
            matchIfMissing = true
    )
    @Bean("mysql2TransactionManager")
    @Qualifier("mysql2TransactionManager")
    public PlatformTransactionManager mysql2TransactionManager(RoutingDataSource dataSource) {
        DataSource _dataSource = dataSource.getTargetDataSource("mysql2");
        DataSourceTransactionManager dataSourceTransactionManagernew = new DataSourceTransactionManager(_dataSource);
        return dataSourceTransactionManagernew;
    }

    @Bean
    @ConditionalOnProperty(
            name = {"spring.jta.enabled"},
            havingValue = "true",
            matchIfMissing = false
    )
    public UserTransaction userTransaction() throws Throwable {
        UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(180);
        return userTransactionImp;
    }
}

7. 实现Mybatis拦截器，读取注解配置的数据源，进行切换，次切换通过DynamicDataSourceContextHolder完成，DynamicDataSourceContextHolder在配置事务的时候（CustomizeTransaction）通过DynamicDataSourceContextHolder.getDataSourceName();获取当前数据源，因此只需要在此拦截器中DynamicDataSourceContextHolder.setDataSourceName();，最后把此拦截器注入到sqlSessionFatory中才会起作用

package com.test.config.datasource;

import com.test.annotation.MyBatisDao;
import com.test.common.lang.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.util.Properties;

/**
 * Mybatis拦截器，因使用了自定义的sqlSessionFatory，因此需要在sqlSessionFatory中注入拦截器，否则将不生效
 * 使用拦截器根据MyBatisDao注解的dataSourceName设置数据源上下文（当前数据源名称）
 */
@Intercepts({@Signature(
        type = Executor.class,
        method = "update",
        args = {MappedStatement.class, Object.class}
), @Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
), @Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
)})
public class DataSourceInterceptor implements Interceptor{

        public Object plugin(Object target) {
            return Plugin.wrap(target, this);
        }

        public Object intercept(Invocation invocation) throws Throwable {
            MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
            String className = StringUtils.substringBeforeLast(mappedStatement.getId(), ".");
            //获取当前的指定的数据源;
            String dsId = ((MyBatisDao) Class.forName(className).getAnnotation(MyBatisDao.class)).dataSourceName();
            //如果不在我们注入的所有的数据源范围之内，那么输出警告信息，系统自动使用默认的数据源。
            if(StringUtils.isEmpty(dsId)){
                dsId = "default";
            }
            boolean flag = false;
            if (!dsId.equalsIgnoreCase(DynamicDataSourceContextHolder.getDataSourceName())) {
                DynamicDataSourceContextHolder.setDataSourceName(dsId);
                flag = true;
            } else {
                //找到的话，那么设置到动态数据源上下文中。
                DynamicDataSourceContextHolder.setDataSourceName(dsId);
            }
            Object obj = invocation.proceed();
            if(flag){
                DynamicDataSourceContextHolder.setDataSourceName(dsId);
            }
            return obj;
        }

        public void setProperties(Properties properties) {
        }
}

【注】因多数据源中每个数据源都有自己的管理器，所以在配置管理时不要忘了给每个数据源配置管理器，这样事务才会起效（参考TransactionConfig），并且通过@Transactional(“mysql2TransactionManager”)选用管理器，这种处理方式只适合在同一事务下只有一个数据源的情况，如果在同一个事务下使用多个数据源的话，得使用分布式事务进行处理，如在DataSourceConfig中的AtomikosDataSourceBean使用以及TransactionConfig中的JtaTransactionManager配置，但是目前代码有部分报错还没调好，如后续如解决会放上来。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。