springboot+JTA+atomikos多数据源分布式事务管理

一、项目需求

        1、同时操作两个数据库，一个在本地服务器，一个在云服务器。

        2、数据库数据是同步的，两个数据库同时做update、insert等操作时，无论哪个数据库操作失败，要求两个数据库数据同时回滚。

        3、两个数据库均为SQL server数据库。

二、特殊要求

        1、数据库支持XA事务。网上其他教程都很复杂，但是这个博主写的很不错，不会配的可以参考一下：无法创建 XA 控制连接。错误:“找不到存储过程 'master..xp_sqljdbc_xa_init_ex'_com.microsoft.sqlserver.jdbc.sqlserverexception: 无-CSDN博客

        2、设置允许远程操作数据库。

        3、云服务器在控制台配置入站或出站规则，tcp协议，开放数据库端口。本地数据库在防火墙配置入站出站规则。

三、简单认识XA、JTA和Atomikos

XA

        XA是定义于数据库的分布式事务处理规范，XA事务支持不同数据库之间实现分布式事务。

JTA

        JTA（Java Transaction API）：是Java平台上一个标准API，用于管理和控制分布式事务的执行流程。它是数据库XA事务在Java的一个映射。

        核心类：
javax.transaction.UserTransaction：暴露给应用使用，用来启动、提交、回滚事务。
javax.transaction.TransactionManager：提供给事务管理器的接口，用于协调和控制分布式事务的执行过程。
javax.transaction.XAResource：表示一个资源管理器，用于管理和操作资源。
javax.transaction.Xid：用于唯一标识一个分布式事务。

Atomikos

        Atomikos是一个开源的事务管理器，用于管理和控制分布式事务的执行过程。Atomikos可以解决，在同一个应用下，连接多个数据库，实现分布式事务。

四、项目案例

项目目录：

0、pom配置：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.13</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<groupId>com.gloyel</groupId>
	<artifactId>twodbgn</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>twodbgn</name>
	<description>twodbgn</description>

	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.2</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.32</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter-test</artifactId>
			<version>3.0.3</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jta-atomikos</artifactId>
			<version>2.6.13</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
			<version>3.6.0</version>
		</dependency>
		<dependency>
			<groupId>jakarta.transaction</groupId>
			<artifactId>jakarta.transaction-api</artifactId>
			<version>1.3.3</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>5.3.23</version>
		</dependency>
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<version>11.2.0.jre17</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

1、bean

        User是实体类，与数据库表对应可自行配置

2、config

        JTA 事务配置，固定配置，通过JTATransactionManager实现分布式事务

import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;
import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;

/**
 * JTA 事务配置
 */
@Configuration
public class AtomikosConfig {
    @Bean(name = "userTransaction")
    public UserTransaction userTransaction() throws Throwable {
        UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(10000);
        return userTransactionImp;
    }

    @Bean(name = "atomikosTransactionManager")
    public TransactionManager atomikosTransactionManager() throws Throwable {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(false);
        return userTransactionManager;
    }

    @Bean(name = "transactionManager")
    @DependsOn({ "userTransaction", "atomikosTransactionManager" })
    public PlatformTransactionManager transactionManager() throws Throwable {
        UserTransaction userTransaction = userTransaction();
        TransactionManager atomikosTransactionManager = atomikosTransactionManager();
        return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
    }
}

3、controller

        控制层

import com.gloyel.twodbgn.Service.UserServiceImpl;
import com.gloyel.twodbgn.Service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
@RequestMapping("/user")
public class UserController {

    @Resource
    private UserService userService=new UserServiceImpl();

    @RequestMapping(value = "/userList")
    public String addPre() {
        String msg = "";
        try {
            userService.updateUser("and FID = 7");
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return msg;
    }
}

4、ioc

        配置数据库

import java.sql.SQLException;

import javax.sql.DataSource;

import com.atomikos.icatch.jta.UserTransactionManager;
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.context.annotation.*;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.microsoft.sqlserver.jdbc.SQLServerXADataSource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;


@Configuration
@MapperScan(basePackages="com.gloyel.twodbgn.mapper.local",sqlSessionTemplateRef="localSqlSessionTemplate")
public class TestMybatisConfig1 {

    @Primary
    @Bean(name="localDataSource")
    @DependsOn("transactionManager")
    public DataSource testDataSource() throws SQLException {
        SQLServerXADataSource sqlServerXADataSource = new SQLServerXADataSource();
        sqlServerXADataSource.setURL("jdbc:sqlserver://localhost:1433;databaseName=mytest;encrypt=true;trustServerCertificate=true");
        sqlServerXADataSource.setUser("username");
        sqlServerXADataSource.setPassword("password");

        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(sqlServerXADataSource);
        atomikosDataSourceBean.setUniqueResourceName("localDataSource");

        atomikosDataSourceBean.setMinPoolSize(3);
        atomikosDataSourceBean.setMaxPoolSize(25);

        return atomikosDataSourceBean;
    }
    @Primary
    @Bean(name="localSqlSessionFactory")
    @DependsOn("localDataSource")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("localDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
    @Primary
    @Bean(name="localSqlSessionTemplate")
    @DependsOn("localSqlSessionFactory")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("localSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

/***************************************************************************************/

import com.atomikos.icatch.jta.UserTransactionManager;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.microsoft.sqlserver.jdbc.SQLServerXADataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Lazy;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import javax.transaction.NotSupportedException;
import javax.transaction.SystemException;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages="com.gloyel.twodbgn.mapper.cloud",sqlSessionTemplateRef="cloudSqlSessionTemplate")
public class TestMybatisConfig2 {

    @Bean(name="cloudDataSource")
    @DependsOn("transactionManager")
    public DataSource testDataSource() throws SQLException {
        SQLServerXADataSource sqlServerXADataSource = new SQLServerXADataSource();
        sqlServerXADataSource.setURL("jdbc:sqlserver://ip:端口;databaseName=mytest;encrypt=true;trustServerCertificate=true");
        sqlServerXADataSource.setUser("username");
        sqlServerXADataSource.setPassword("password");

        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(sqlServerXADataSource);
        atomikosDataSourceBean.setUniqueResourceName("cloudDataSource");

        atomikosDataSourceBean.setMinPoolSize(3);
        atomikosDataSourceBean.setMaxPoolSize(25);

        return atomikosDataSourceBean;
    }
    @Bean(name="cloudSqlSessionFactory")
    @DependsOn("cloudDataSource")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("cloudDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
    @Bean(name="cloudSqlSessionTemplate")
    @DependsOn("cloudSqlSessionFactory")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("cloudSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5、mapper

@Mapper
public interface UserMapper_C {
    @Select({"select * from User_loginData where 1=1 ${where}"})
    List<User> selectUser(@Param("where")String where);

    @Update({"update Dc_User set FPassWord = 'ccc' where 1=1 ${where}"})
    int updateUser(@Param("where")String where);

}


@Mapper
public interface UserMapper_L {
    @Select({"select * from User_loginData where 1=1 ${where}"})
    List<User> selectUser(@Param("where")String where);

    @Update({"update Dc_User set FPassWord = 'lll' where 1=1 ${where}"})
    int updateUser(@Param("where")String where);

}

6、service

public interface UserService {
    List<User> selectUser(String where);
    int updateUser(String where);
}

@Service("userService")
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper_L userMapper_l;
    @Resource
    private UserMapper_C userMapper_c;

    @Override
    public List<User> selectUser(String where) {
        return userMapper_c.selectUser(where);
    }

    @Override
    @Transactional
    public int updateUser(String where) {
        int a = userMapper_c.updateUser(where);
//        int c = 1/0;
        int b = userMapper_l.updateUser(where);
        System.out.println("a="+a+" -- b="+b);
        return a+b;
    }
}

五、调试运行

1、运行正常时：

//UserMapper_C 
@Update({"update Dc_User set FPassWord = 'ccc' where 1=1 ${where}"})
int updateUser(@Param("where")String where);

//UserMapper_L
@Update({"update Dc_User set FPassWord = 'lll' where 1=1 ${where}"})
int updateUser(@Param("where")String where);


@Override
    @Transactional
    public int updateUser(String where) {
        int a = userMapper_c.updateUser(where);
//        int c = 1/0;
        int b = userMapper_l.updateUser(where);
        System.out.println("a="+a+" -- b="+b);
        return a+b;
    }

控制台：

云数据库：

本地数据库：

2、运行异常时：

        观察出现异常时，更新语句是否回滚。

//UserMapper_C 
@Update({"update Dc_User set FPassWord = 'ccc000' where 1=1 ${where}"})
int updateUser(@Param("where")String where);

//UserMapper_L
@Update({"update Dc_User set FPassWord = 'lll000' where 1=1 ${where}"})
int updateUser(@Param("where")String where);


@Override
    @Transactional
    public int updateUser(String where) {
        int a = userMapper_c.updateUser(where);
        int c = 1/0;
        int b = userMapper_l.updateUser(where);
        System.out.println("a="+a+" -- b="+b);
        return a+b;
    }

控制台：

云数据库：

本地数据库：

至此，多数据源分布式事务管理项目测试成功！