前言

实际开发时有些业务需要进行多数据库的操作，如果进行了多数据库操作就需要考虑多数据库的事务
目录结构
在这里插入图片描述

1. 配置多数据源

ps：其实就是配置多个 datasource、sqlSessionFactory 和 transactionManager

1.1 导入坐标

	<dependencies>
<!--    mybaits    -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
<!--        druid连接池   也可以不用-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
<!--        mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
<!--        springboot单元测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
    </dependencies>

1.2 application.yml

如果没有使用 druid连接池，那么将 url 改为 jdbc-url

spring:
  datasource:
    test1:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: root
    test2:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: root

1.3 实体类

数据库：test1中存在表 school1、test2中存在表 school2 ，两张表字段及类型都相同

  1. school1
    public class School1 {
    private int id;
    private String name;
    private int age;
}
  2. school2 和 1相同，只是名字不同
    …省略

1.4 配置类（重要）仔细检查

  1. DBConfig1：连接 test1 数据库
    	
	@Configuration
	@MapperScan(basePackages = "com.hbsi.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
	public class DBConfig1 {
	
	    @Bean(name = "test1DataSource")
	    @ConfigurationProperties(prefix = "spring.datasource.test1")
	    public DataSource test1DataSource() {
	        
	//     return DataSourceBuilder.create().build();    // 没有用到 druid ：
	        return new DruidDataSource();
	    }
	
	    @Bean(name = "test1SqlSessionFactory")
	    public SqlSessionFactory test1SqlSessionFactory() throws Exception {
	        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
	        bean.setDataSource(test1DataSource());
	        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test1/*.xml"));
	        return bean.getObject();
	    }
	
	    @Bean(name = "test1TransactionManager")
	    public DataSourceTransactionManager test1TransactionManager() {
	        return new DataSourceTransactionManager(test1DataSource());
	    }
	
	    @Bean(name = "test1SqlSessionTemplate")
	    public SqlSessionTemplate testSqlSessionTemplate() throws Exception {
	        return new SqlSessionTemplate(test1SqlSessionFactory());
	    }
	}
  2. DBConfig2：连接 test2 数据库
    
@Configuration
@MapperScan(basePackages = "com.hbsi.mapper.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DBConfig2 {

    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource test2DataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(test2DataSource());
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test2/*.xml"));
        return bean.getObject();
    }

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

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

1.5 mapper

  1. 两个 mapper 类

    public interface School1Mapper {
    void insert(School1 school1);
    List<School1>  selectAll();
}


public interface School2Mapper {
    void insert(School1 school1);
    List<School1>  selectAll();
}

  2. 配置文件中的 mapper 文件

    1. school1
      <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hbsi.mapper.test1.School1Mapper">
    <resultMap id="BaseResultMap" type="com.hbsi.entity.School1">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
    </resultMap>
    
    <insert id="insert" parameterType="com.hbsi.entity.School1">
        insert into school1 (id, `name`, age)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})
    </insert>
    
    <select id="selectAll" resultMap="BaseResultMap">
        select id, `name`, age
        from school1
    </select>
</mapper>
    2. school2：
      		<?xml version="1.0" encoding="UTF-8"?>
		<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
		<mapper namespace="com.hbsi.mapper.test1.School1Mapper">
		    <resultMap id="BaseResultMap" type="com.hbsi.entity.School1">
		        <id column="id" jdbcType="INTEGER" property="id" />
		        <result column="name" jdbcType="VARCHAR" property="name" />
		        <result column="age" jdbcType="INTEGER" property="age" />
		    </resultMap>
		    
		    <insert id="insert" parameterType="com.hbsi.entity.School1">
		        insert into school1 (id, `name`, age)
		        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})
		    </insert>
		    
		    <select id="selectAll" resultMap="BaseResultMap">
		        select id, `name`, age
		        from school1
		    </select>
		</mapper>

1.6 service4

@Service
public class SchoolService {
    @Autowired
    private School1Mapper school1Mapper;
    @Autowired
    private School2Mapper school2Mapper;

    public void save(){
        System.out.println("插入数据");
        school1Mapper.insert(new School1(2,"school张三",18));

        System.out.println("业务处理。。。");
        school2Mapper.insert(new School2(2,"school2张三",18));
        System.out.println("处理完毕。。。");
    }
}

1.7 测试

@SpringBootTest
@RunWith(SpringRunner.class)
public class MapperTest {

    @Autowired
    private SchoolService service;

    @Test
    public void test1(){
        service.save();
    }
}

成功
在这里插入图片描述

2. 多数据源事务管理

这里采用spring的编程式事务控制，即 使用TransactionTemplate进行事务管理，当然也可以用分布式事务解决方案 seata，这里就不做演示了
seata官网

2.1 方式一：编程式事务控制

2.1.1 配置类

在配置类中注册指定事务管理器的 TransactionTemplate

  1. DBConfig1
    	@Bean(name = "test1TransactionTemplate")  
    public TransactionTemplate test1TransactionTemplate(){
        return new TransactionTemplate(test1TransactionManager());
    }
  2. DBConfig2
    @Bean(name = "test2TransactionTemplate")  
    public TransactionTemplate test2TransactionTemplate(){
        return new TransactionTemplate(test2TransactionManager());
    }

2.1.2 service

@Service
public class SchoolService {
    @Autowired
    private School1Mapper school1Mapper;
    @Autowired
    private School2Mapper school2Mapper;

    @Autowired
    @Qualifier("test1TransactionTemplate")
    TransactionTemplate test1TransactionTemplate;

    @Autowired
    @Qualifier("test2TransactionTemplate")
    TransactionTemplate test2TransactionTemplate;
	
    public void save2(){
        // execute 方法需要一个 TransactionCallBack接口，这里用 lambda的方式
        test1TransactionTemplate.execute((status1) ->{
            test2TransactionTemplate.execute((status2)->{
                try {

                    school1Mapper.insert(new School1(3,"李四",18));
                    school2Mapper.insert(new School2(3,"李四",18));
                    System.out.println("模拟异常");
                    int i = 1/0;
                    
                }catch (Exception e){
                    e.printStackTrace();
                    status1.setRollbackOnly();  // 事务1回滚
                    status2.setRollbackOnly();  // 事务2回滚
                }
                return true; // 事务2提交
            });
            return true;    // 事务1提交
        });
    }

    public void save(){
        System.out.println("插入数据");
        school1Mapper.insert(new School1(2,"school张三",18));

        System.out.println("业务处理。。。");
        school2Mapper.insert(new School2(2,"school2张三",18));
        System.out.println("处理完毕。。。");
    }
}

测试save2() 为了结果的明显，我先清空了表中的数据

在这里插入图片描述

成功控制住了事务

在这里插入图片描述

注释掉异常再次测试：成功

在这里插入图片描述

如果想要多数据元事务重用性更高的话，可以利用 AOP + 注解方式实现

2.2 方式二：声明式事务控制

场景：插入 test1库中的school1 时，插入 test2 中的 school2

2.2.1 导入aop的依赖

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

2.2.2 主启动类加注解启用AspectJ自动代理

	@SpringBootApplication
	@EnableTransactionManagement
	@EnableAspectJAutoProxy(exposeProxy = true) // 启用AspectJ自动代理
	public class MainApplication {
	    public static void main(String[] args) {
	        SpringApplication.run(MainApplication.class, args);
	    }
	
	}

2.2.2 service中使用当前的代理去调用

@Service
public class SchoolService {
    @Autowired
    private School1Mapper school1Mapper;
    @Autowired
    private School2Mapper school2Mapper;
    
	@Transactional(transactionManager = "test1TransactionManager")
    public void save3(){
        school1Mapper.insert(new School1(3,"王五1",18));
        // 获取当前的代理对象
        SchoolService schoolService = (SchoolService) AopContext.currentProxy();
        schoolService.saveSchool2();
    }
    
    @Transactional(transactionManager = "test2TransactionManager")
    public void saveSchool2(){
        school2Mapper.insert(new School2(3,"王五2",18));
        // 模拟异常
        int i = 1/0;
    }
}

测试结果：当 saveSchool2() 方法出现异常时，两个事务都进行了回滚
在这里插入图片描述
注释掉异常，成功插入数据
在这里插入图片描述

方式三（推荐）：DynamicDataSource多数据源框架

该框架属于苞米豆生态圈，也就是可以在MybatisPlus官网中查找，MyBatisPlus官网
在这里插入图片描述
官网才是最好的教程，看官网吧。。。。

# mysql # java # 数据库 # spring boot
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

Redis开源社区持续壮大，华为云为Valkey项目注入新的活力

avatar 华为云开发者联盟
cover

CCE云原生混部场景下的测试案例

avatar 华为云开发者联盟
cover

详解数仓的3A安全能力

avatar 华为云开发者联盟