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

news/2024/7/4 7:19:02

参考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博客


http://www.niftyadmin.cn/n/3497442.html

相关文章

matlab符号函数绘图法_《MATLAB符号运算及其应用》(黄忠霖著).pdf

matlab符号函数绘图法_《MATLAB符号运算及其应用》(黄忠霖著).pdf

《MATLAB符号运算及其应用》(黄忠霖著).pdf[General Information]书名MATLAB符号运算及其应用作者黄忠霖&#xff0c;黄京编著页数425出版社北京市&#xff1a;国防工业出版社出版日期2004SS号DX号000003120011URL/bookDetail.jsp?dxNumber000003120011&dFF2A7DD4D65FE8CB…
阅读更多...
LVS负载均衡群集1（NAT模式）

LVS负载均衡群集1（NAT模式）

一、群集技术概述 1、群集的类型1&#xff09;负载均衡群集&#xff1a;主要的功能将来自客户机的访问请求分流给多台服务器&#xff0c;从而缓单台服务器的负载压力&#xff0c;例如京东淘宝的购物节的时候&#xff0c;当天的并发量是分常大的&#xff0c;单台服务器是无法承载…
阅读更多...
数据库（转）

数据库（转）

iOS开发数据库篇—SQL 一、SQL语句 如果要在程序运行过程中操作数据库中的数据&#xff0c;那得先学会使用SQL语句 1.什么是SQL SQL&#xff08;structured query language&#xff09;&#xff1a;结构化查询语言 SQL是一种对关系型数据库中的数据进行定义和操作的语言 SQL语言…
阅读更多...
Google今天出浏览器了!

Google今天出浏览器了!

Goole今天提供自己开发的浏览Chrome免费下载了!
阅读更多...
python获取当前时区_关于datetime：在Python中获取系统的时区信息？

python获取当前时区_关于datetime：在Python中获取系统的时区信息？

我想从Python获取我的系统的默认时区(PST)。 最好的方法是什么？ 我想避免分支另一个过程。 这应该工作： import time time.tzname time.tzname返回两个字符串的元组：第一个是本地非DST时区的名称，第二个是本地DST时区的名称。 返回示例：(MST, MDT) 要进一步使用此功能，您…
阅读更多...
BZOJ 1565: [NOI2009]植物大战僵尸( 最小割 )

BZOJ 1565: [NOI2009]植物大战僵尸( 最小割 )

先拓扑排序搞出合法的, 然后就是最大权闭合图模型了.... ---------------------------------------------------------------------#include<cstdio>#include<cstring>#include<algorithm>using namespace std;#define Id(x, y) ((x) * c (y))const int max…
阅读更多...
内部类获得外部类字节码对象

内部类获得外部类字节码对象

普通内部类和匿名内部类获得外部类字节码对象方式不一样。获得父类的字节码对象方法一样。 普通内部类&#xff1a;getDeclaringClass() 匿名内部类&#xff1a;getEnclosingClass() WaterRiverBox waterRiverBox new WaterRiverBox() {{setType(1);}};WaterRiverBox.Test t…
阅读更多...
彻底学会使用epoll（三）——ET的读操作实例分析

彻底学会使用epoll（三）——ET的读操作实例分析

首先看程序一&#xff0c;这个程序想要实现的功能是当用户从控制台有任何输入操作时&#xff0c;输出”hello world&#xff01;”。 l 程序一 点击(此处)折叠或打开 #include unistd.h> #include iostream> #include sys/epoll.h> using namespace std; int m…
阅读更多...
最新文章

Copyright @ 2022~2023