多数据源事务（非分布式）

SomeOtherTime

已于 2022-12-02 11:38:28 修改

阅读量3.7k

点赞数 1

分类专栏：典型demo 文章标签： spring boot java 数据库

于 2021-09-10 14:20:37 首次发布

本文链接：https://blog.csdn.net/yaoct/article/details/120221068

版权

典型demo 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

参考SpringBoot+Mybatis配置多数据源并且实现事务一致性_周先生丶的博客-CSDN博客_多数据源事务一致性

springboot在多数据源时默认只能开启一个主数据库的事务，如果要同时开启多个数据源的事务，并回滚，需要在切面中手动开启所有数据源事务，并同时回滚。

yml：

#开发环境
spring:
  # 数据源配置
  datasource:
    one:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.5.11:3306/test202199?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=PRC&&allowPublicKeyRetrieval=true
      username: root
      password: root
      max-idle: 10
      max-wait: 10000
      min-idle: 5
      initial-size: 5
    two:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.5.11:3306/tuying?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=PRC&&allowPublicKeyRetrieval=true
      username: root
      password: root
      max-idle: 10
      max-wait: 10000
      min-idle: 5
      initial-size: 5
    three:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=PRC&&allowPublicKeyRetrieval=true
      username: root
      password: root
      max-idle: 10
      max-wait: 10000
      min-idle: 5
      initial-size: 5
  # 统一时区
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  type-aliases-package: com.sssr.assets.entity

logging:
  level:
    root: info

各个数据库配置文件：

package com.example.demo202199.config;

/**
 * @version 2.0
 * @description
 * @Author yaoct
 * @create 2021/9/10 9:03
 */

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo202199.dao.mapper1",sqlSessionFactoryRef = "test1SqlSessionFactory",sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class MyBatisConfigOne {

    @Bean(name = "test1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.one")
    @Primary
    public DataSource test1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "test1SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Primary
    @Bean(name = "test1TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "test1SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.example.demo202199.config;

/**
 * @version 2.0
 * @description
 * @Author yaoct
 * @create 2021/9/10 9:03
 */

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo202199.dao.mapper2",sqlSessionFactoryRef = "test2SqlSessionFactory",sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class MyBatisConfigTwo {

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

    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "test2TransactionManager")
    public DataSourceTransactionManager test2TransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

package com.example.demo202199.config;

/**
 * @version 2.0
 * @description
 * @Author yaoct
 * @create 2021/9/10 9:03
 */

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo202199.dao.mapper3",sqlSessionFactoryRef = "test3SqlSessionFactory",sqlSessionTemplateRef = "test3SqlSessionTemplate")
public class MyBatisConfigThree {

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

    @Bean(name = "test3SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test3DataSource") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "test3TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test3DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

自定义注解：

package com.example.demo202199.config;

import java.lang.annotation.*;

/**
 * @version 2.0
 * @description
 * @Author yaoct
 * @create 2021/9/10 11:56
 */
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface MyTransactional {

    String[] transactionManagers();
}

切面开启和回滚事务：

package com.example.demo202199.config;

import javafx.util.Pair;
import org.aspectj.lang.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.util.Stack;

/**
 * 多数据源事务切面
 * ※采用Around似乎不行※
 *
 * @author Zhou Huanghua
 * @date 2019/10/26 1:16
 */
@Component
@Aspect
public class MultiDataSourceTransactionAspect {

    /**
     * 线程本地变量：为什么使用栈？※为了达到后进先出的效果※
     */
    private static final ThreadLocal<Stack<Pair<DataSourceTransactionManager, TransactionStatus>>> THREAD_LOCAL = new ThreadLocal<>();

    /**
     * 用于获取事务管理器
     */
    @Autowired
    private ApplicationContext applicationContext;

    /**
     * 切面
     */
    @Pointcut("@annotation(com.example.demo202199.config.MyTransactional)")
    public void pointcut() {
    }

    /**
     * 声明事务
     *
     * @param transactional 注解
     */
    @Before("pointcut() && @annotation(transactional)")
    public void before(MyTransactional transactional) {
        // 根据设置的事务名称按顺序声明，并放到ThreadLocal里
        String[] transactionManagerNames = transactional.transactionManagers();
        Stack<Pair<DataSourceTransactionManager, TransactionStatus>> pairStack = new Stack<>();
        for (String transactionManagerName : transactionManagerNames) {
            DataSourceTransactionManager transactionManager = applicationContext.getBean(transactionManagerName, DataSourceTransactionManager.class);

            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
            // 非只读模式
            def.setReadOnly(false);
            // 事务隔离级别：采用数据库的
            def.setIsolationLevel(TransactionDefinition.ISOLATION_DEFAULT);
            // 事务传播行为
            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);


            TransactionStatus transactionStatus = transactionManager.getTransaction(def);
            pairStack.push(new Pair(transactionManager, transactionStatus));
        }
        THREAD_LOCAL.set(pairStack);
    }

    /**
     * 提交事务
     */
    @AfterReturning("pointcut()")
    public void afterReturning() {
        // ※栈顶弹出（后进先出）
        Stack<Pair<DataSourceTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get();
        while (!pairStack.empty()) {
            Pair<DataSourceTransactionManager, TransactionStatus> pair = pairStack.pop();
            pair.getKey().commit(pair.getValue());
        }
        THREAD_LOCAL.remove();
    }

    /**
     * 回滚事务
     */
    @AfterThrowing(value = "pointcut()")
    public void afterThrowing() {
        // ※栈顶弹出（后进先出）
        Stack<Pair<DataSourceTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get();
        while (!pairStack.empty()) {
            Pair<DataSourceTransactionManager, TransactionStatus> pair = pairStack.pop();
            pair.getKey().rollback(pair.getValue());
        }
        THREAD_LOCAL.remove();
    }
}

测试：

package com.example.demo202199;

import com.example.demo202199.config.MyTransactional;
import com.example.demo202199.dao.mapper1.Test202199Mapper;
import com.example.demo202199.entity.Test1;
import com.example.demo202199.entity.Testtable;
import com.example.demo202199.service.DhHostIPServiceBase;
import com.example.demo202199.service.Test1ServiceBase;
import com.example.demo202199.service.Test202199ServiceBase;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Transactional;

@SpringBootApplication
@Controller
@MapperScan("com.example.demo202199.dao.*")
//@EnableTransactionManagement
public class Demo202199Application implements ApplicationRunner {

    public static void main(String[] args) {
        SpringApplication.run(Demo202199Application.class, args);
    }

    @Autowired
    Test202199Mapper mapper;

    @Autowired
    Test202199ServiceBase test202199ServiceBase;

    @Autowired
    DhHostIPServiceBase dhHostIPServiceBase;

    @Autowired
    Test1ServiceBase test1ServiceBase;

    @Override
//    @Transactional
//    @Transactional("test2TransactionManager")
    @MyTransactional(transactionManagers={"test1TransactionManager","test3TransactionManager"})
    public void run(ApplicationArguments args) throws Exception {
        System.out.println(test202199ServiceBase.list());
        System.out.println(dhHostIPServiceBase.list());
        System.out.println(test1ServiceBase.list());
        test202199ServiceBase.save(new Testtable(42,"ceshi1","ceshi1"));
        test1ServiceBase.save(new Test1(42L,1L,1L,1L));
        int a=1/0;
    }
}

参考：

springboot在集成多数据源+mybatis-plus无法进行分页的BUG_sunrj_go的博客-CSDN博客

SomeOtherTime

关注

1
点赞
踩
29

收藏

觉得还不错? 一键收藏
1
评论
多数据源事务（非分布式）

参考SpringBoot+Mybatis配置多数据源并且实现事务一致性_周先生丶的博客-CSDN博客_多数据源事务一致性springboot在多数据源时默认只能开启一个主数据库的事务，如果要同时开启多个数据源的事务，并回滚，需要在切面中手动开启所有数据源事务，并同时回滚。yml：#开发环境spring: # 数据源配置 datasource: one: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-u
复制链接

扫一扫