SpringBoot实现动态数据源切换及单库事务控制

引言: 

项目中经常会遇到多数据源的场景,通常的处理是: 操作数据一般都是在DAO层进行处理,使用配置多个DataSource 然后创建多个SessionFactory，在使用Dao层的时候通过不同的SessionFactory进行处理，

但是这样的操作代码入侵性比较明显且配置繁琐难以维护,,,在这里推荐一个Spring提供的AbstractRoutingDataSource抽象类,它实现了DataSource接口的用于获取数据库连接的方法

AbstractRoutingDataSource的内部维护了一个名为targetDataSources的Map，并提供的setter方法用于设置数据源关键字与数据源的关系，实现类被要求实现其determineCurrentLookupKey()方法，由此方法的返回值决定具体从哪个数据源中获取连接

一. 注解方式实现动态切换数据源

原理: AbstractRoutingDataSource提供了程序运行时动态切换数据源的方法，在dao类或方法上标注需要访问数据源的关键字，路由到指定数据源，获取连接

1.pom.xml导入相关坐标

<!--mysql相关-->
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>
<!--oracle相关-->
<dependency>
 <groupId>com.github.noraui</groupId>
 <artifactId>ojdbc7</artifactId>
 <version>${oracle.version}</version>

2.1application.properties配置多数据源

#多数据源配置
db.groups=default,oracle

#默认数据库(mysql库)
db.default.url=jdbc:mysql://127.0.0.1:3306/demo?connectTimeout=2000&allowMultiQueries=true&rewriteBatchedStatements=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
db.default.username=root
db.default.password=root

#oracle库
db.oracle.url=jdbc:oracle:thin:@127.0.0.1:1521/orcl
db.oracle.username=root
db.oracle.password=root

2.2application.yml添加datasource配置

spring:
 datasource:
    group: ${db.groups}

3.1数据源切换方法: 维护一个static变量datasourceContext用于记录每个线程需要使用的数据源关键字。并提供切换、读取、清除数据源配置信息的方法

编写DataSourceContextHolder类

public class DataSourceContextHolder {
 private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

 public static synchronized void setDataSourceKey(String key) {
 contextHolder.set(key);
    }

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

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

3.2实现AbstractRoutingDataSource 

public class DynamicDataSource extends AbstractRoutingDataSource {

 private static DynamicDataSource instance;
 private static Object lock=new Object();
 private static Map<Object,Object> dataSourceMap = Maps.newHashMap();

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

 @Override
 public void setTargetDataSources(Map<Object, Object> targetDataSources) {
 super.setTargetDataSources(targetDataSources);
        dataSourceMap.putAll(targetDataSources);
        super.afterPropertiesSet();
    }

 public static synchronized DynamicDataSource getInstance(){
 if(instance==null){
 synchronized (lock){
 if(instance==null){
 instance=new DynamicDataSource();
                }
 }
 }
 return instance;
    }

 public static boolean isExistDataSource(String key) {
 if (StringUtils.isEmpty(key)) {
 return false;
        }
 return dataSourceMap.containsKey(key);
    }
}

3.3编写数据源配置类MybatisConfig

@Configuration
@MapperScan(basePackages = { "com.**.mapper"} , sqlSessionTemplateRef = "sqlSessionTemplate")

public class MybatisConfig {

    private static Logger LOG = LoggerFactory.getLogger(MybatisConfig.class);

    @Autowired
    private Environment environment;

    private static final String DEFAULT_DATASOURCE_NAME = "default";

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) throws Exception {

        //需要引入mybatis-plus坐标
        //MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dynamicDataSource);

        //bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/wttech/vsm/support/mapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }

    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dynamicDataSource() {
        String groups = environment.getProperty("spring.datasource.group");
        LOG.info("数据源组名称:{}", groups);
        Map<Object,Object> dataSourceMap = Maps.newHashMap();
        Set<String> dbNames = Arrays.asList(groups.split(",")).stream().filter(s -> s.trim().length() > 0).collect(Collectors.toSet());
        dbNames.add(DEFAULT_DATASOURCE_NAME);
        HikariDataSource first = null;
        HikariDataSource def = null;
        for (String dbName:dbNames) {
            String driver = environment.getProperty(String.format("db.%s.driver", dbName));
            String url = environment.getProperty(String.format("db.%s.url", dbName));
            String username = environment.getProperty(String.format("db.%s.username", dbName));
            String password = environment.getProperty(String.format("db.%s.password", dbName));
            LOG.info("数据源{}连接:{}", dbName, url);
            if (StringUtils.isEmpty(url) || StringUtils.isEmpty(username) || StringUtils.isEmpty(password)) {
                continue;
            }
            DataSourceBuilder<HikariDataSource> hikariDataSourceBuilder = DataSourceBuilder.create().type(HikariDataSource.class);
            if (!StringUtils.isEmpty(driver)) {
                hikariDataSourceBuilder.driverClassName(driver);
            }
            HikariDataSource hikariDataSource = hikariDataSourceBuilder.url(url).username(username).password(password).build();
            hikariDataSource.setAutoCommit(true);
            String testQuery = environment.getProperty(String.format("db.%s.connectionTestQuery", dbName));
            if (!StringUtils.isEmpty(testQuery)) {
                hikariDataSource.setConnectionTestQuery(testQuery);
            }
            String timeout = environment.getProperty(String.format("db.%s.connectionTimeout", dbName));
            if (!StringUtils.isEmpty(timeout)) {
                hikariDataSource.setConnectionTimeout(Long.parseLong(timeout));
            }
            String minimumIdle = environment.getProperty(String.format("db.%s.minimumIdle", dbName));
            if (!StringUtils.isEmpty(minimumIdle)) {
                hikariDataSource.setMinimumIdle(Integer.parseInt(minimumIdle));
            }
            String maximumPoolSize = environment.getProperty(String.format("db.%s.maximumPoolSize", dbName));
            if (!StringUtils.isEmpty(maximumPoolSize)) {
                hikariDataSource.setMaximumPoolSize(Integer.parseInt(maximumPoolSize));
            }
            String idleTimeout = environment.getProperty(String.format("db.%s.idleTimeout", dbName));
            if (!StringUtils.isEmpty(idleTimeout)) {
                hikariDataSource.setIdleTimeout(Long.parseLong(idleTimeout));
            }
            String maxLifetime = environment.getProperty(String.format("db.%s.maxLifetime", dbName));
            if (!StringUtils.isEmpty(maxLifetime)) {
                hikariDataSource.setMaxLifetime(Long.parseLong(maxLifetime));
            }
            hikariDataSource.setPoolName(dbName);
            dataSourceMap.put(dbName, hikariDataSource);
            if (first == null) {
                first = hikariDataSource;
            }
            if (DEFAULT_DATASOURCE_NAME.equals(dbName)) {
                def = hikariDataSource;
            }
        }
        DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
        dynamicDataSource.setTargetDataSources(dataSourceMap);
        dynamicDataSource.setDefaultTargetDataSource(def == null ? first : def);
        return dynamicDataSource;
    }

4.1.标记数据源注解

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface SwitchDataSource {
 String value();
}

4.2.编写切入点方法

@Aspect
@Component
public class MethodInterceptor {
@Around("execution(* com.wttech.vsm.support.mapper..*.*(..)))
public Object dao(ProceedingJoinPoint invocation) throws Throwable {
 MethodSignature methodSignature = (MethodSignature) invocation.getSignature();
    Method method = methodSignature.getMethod();

    String dbName = null;
    SwitchDataSource dataSource = method.getAnnotation(SwitchDataSource.class);
    if (dataSource != null) {
 dbName = dataSource.value();
        if (DynamicDataSource.isExistDataSource(dbName)) {
 DataSourceContextHolder.setDataSourceKey(dbName);
        }
 }
 try {
 return invocation.proceed();
    } finally {
 DataSourceContextHolder.clearDataSourceKey();
    }
} 
}

 

二.单库事务控制:

本人涉及到到的业务场景是在service层的一个方法中存在多个dao操作(只涉及单库),,需要维持事务性,,,遇到问题: 数据源是在mapper层通过注解切换的,,@Transactional在services层控制,,导致程序报错找不到数据源,,开始的解决思路是前提@SwitchDataSource注解至service层 ,,但经测试后还是报错,,,

最后的解决思路是必须保证切换数据源是在事务控制开启之前完成...

1.切入点表达式增加service层切入

@Around("execution(* com.**.mapper..*.*(..)) || execution(* com.**.service.DataFixService.*(..))")

2.注释掉需要事务控制的dao操作设计到的mapper层的数据源切换注解

// @SwitchDataSource("toll")
 int updateFixInList(@Param("listId") String listId, @Param("fieldName") String fieldName, @Param("fieldValue") String fieldValue);

3.事务控制是基于数据源的,,必须在数据源切换后在开启事务,,以下是具体实现思路:

控制器->方法1（切换数据源,使用代理方式调用方法2）->方法2（开启事务,执行多个dao操作）

先切换数据源

@SwitchDataSource("toll")
public void updateFixInList(String listId, String fieldName, String fieldValue) {

 //springAOP的用法中，只有代理的类才会被切入，我们在controller层调用service的方法的时候，是可以被切入的，但是如果我们在service层 A方法中，调用B方法，切点切的是B方法，那么这时候是不会切入的
 //通过((Service)AopContext.currentProxy()).B() 来调用B方法，这样一来，就能切入了！
 ((DataFixService) AopContext.currentProxy()).updateFixInListProxy(listId, fieldName, fieldValue);
}

再控制事务

@Transactional(rollbackFor = Exception.class)
public void updateFixInListProxy(String listId, String fieldName, String fieldValue) {

 dataFixMapper.updateFixInList(listId, fieldName, fieldValue);

  //其他dao操作
}

特别注意: 以上这种方法目前仅适用于多数据源下单库的事务操作,,,如果serviece方法dao操作设计多库,,由于目前业务场景暂未涉及到,,所以暂未深入研究.....