[Spring Boot] 다중 DB 트랜잭션

1. dependency check

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-configuration-processor</artifactId>
	<optional>true</optional>
</dependency>

2. application.properties

spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1Name?useUniCode=yes&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
spring.datasource.db1.username=user1
spring.datasource.db1.password=password1

spring.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2Name?useUniCode=yes&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
spring.datasource.db2.username=user2
spring.datasource.db2.password=password2

3. DB#1 configuration

package com.iut.mes.config;


import javax.sql.DataSource;

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.ApplicationContext;
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 org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(basePackages="com.iut.mes.mapper.db1",sqlSessionFactoryRef="db1SqlSessionFactory")
@EnableTransactionManagement
public class Db1Config {
	
	@Bean(name="db1DataSource")
	@Primary
	@ConfigurationProperties(prefix="spring.datasource.db1")
	public DataSource db1DataSource() {
		return DataSourceBuilder.create().build();
		
	}
	
	@Bean(name="db1SqlSessionFactory")
	@Primary
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource db1DataSource, ApplicationContext applicationContext) throws Exception{
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(db1DataSource);
		sessionFactory.setMapperLocations(applicationContext.getResources("classpath:/mapper/db1/*.xml"));

sessionFactory.setConfigLocation(applicationContext.getResource("classpath:/mapper/config/mybatis-config.xml"));
		return sessionFactory.getObject();
	}
	
	@Bean(name="db1SqlSessionTemplate")
	@Primary
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db1sqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(db1sqlSessionFactory);
	}
	
    @Bean(name = "db1transactionManager")
	@Primary
    public PlatformTransactionManager transactionManager(@Qualifier("db1DataSource") DataSource db1DataSource) {
        return new DataSourceTransactionManager(db1DataSource);
    }
}

4. DB#2 configuration

package com.iut.mes.config;

import javax.sql.DataSource;

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.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(basePackages="com.iut.mes.mapper",sqlSessionFactoryRef="db2SqlSessionFactory") /*멀티DB사용시 mapper클래스파일 스켄용 basePackages를 DB별로 따로설정*/
@EnableTransactionManagement
public class Db2Config {
	
	@Bean(name="db2DataSource")
	@ConfigurationProperties(prefix="spring.datasource.db2")
	public DataSource db1DataSource() {
		return DataSourceBuilder.create().build();
		
	}
	
	@Bean(name="db2SqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource db1DataSource, ApplicationContext applicationContext) throws Exception{
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(db1DataSource);
		sessionFactory.setMapperLocations(applicationContext.getResources("classpath:/mapper/db2/*.xml")); //쿼리작성용 mapper.xml위치 설정.
		sessionFactory.setConfigLocation(applicationContext.getResource("classpath:/mapper/config/mybatis-config.xml"));
		return sessionFactory.getObject();
	}
	
	@Bean(name="db2SqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db1sqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(db1sqlSessionFactory);
	}
	
    @Bean(name = "db2transactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("db2DataSource") DataSource db1DataSource) {
        return new DataSourceTransactionManager(db1DataSource);
    }
}

5. DB#1 Mapper 설정

package com.iut.mes.mapper.db1;

import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.iut.mes.domain.User;

@Mapper
public interface Db1Mapper {
	public void insertAuth();
}

6. DB#2 Mapper 설정

package com.iut.mes.mapper.db2;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface Db2Mapper {
	public void insertEmp();
}

7. 서비스에서의 Mapper 사용

@Autowired
Db1Mapper db1Mapper;

@Autowired
Db2Mapper db2Mapper;

@Transactional
public void transactionTest() {
	db1Mapper.insertAuth();
	db2Mapper.insertEmp();
}

8. resources/mapper/config/mybatis-config.xml 생성

본래는 db1, db2 따로 설정파일을 만들어야 하나 귀찮아서 그냥 했다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "HTTP://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<settings>
		<setting name="cacheEnabled" value="true"/>
		<setting name="callSettersOnNulls" value="true"/>
		<setting name="lazyLoadingEnabled" value="false"/>
		<setting name="aggressiveLazyLoading" value="true"/>
		<setting name="multipleResultSetsEnabled" value="true"/>
		<setting name="useColumnLabel" value="true"/>
		<setting name="useGeneratedKeys" value="false"/>
		<setting name="autoMappingBehavior" value="PARTIAL"/>
		<setting name="defaultExecutorType" value="REUSE" />
		<setting name="defaultStatementTimeout" value="25000" />
		<setting name="jdbcTypeForNull" value="NULL"/>  
	</settings>
	
	<typeAliases>
		<typeAlias alias="hashMap" type="java.util.HashMap" />
		<typeAlias alias="String" type="java.lang.String"/>
		<typeAlias alias="uisRsMap" type="com.iut.mes.util.UISResultMap" />
		<typeAlias alias="User" type="com.iut.mes.domain.User" />
	</typeAliases>
	
	<mappers>
	</mappers>

</configuration>

9. resources/mapper/db1/*.xml 생성

<?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.iut.mes.mapper.db1.Db1Mapper">
	
	<insert id="insertAuth" parameterType="hashMap">
		insert into authority
		(username,
		authority_name)	
		values ('aaa', 'wwwwwwww2')
	</insert>
			
</mapper>

10. resources/mapper/db2/*.xml 생성

<?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.iut.mes.mapper.db2.Db2Mapper">

	<!-- empq 라는 테이블은 존재하지 않는다. -->
	<insert id="insertEmp" parameterType="hashMap">
		insert into empq (id) values ('aaa')
	</insert>

</mapper>

11. 트랜잭션 테스트

DB#2 에서 오류가 나서 DB#1의 insert 문이 롤백 되었음을 확인 하였다.

### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: Table 'exoluse1.empq' doesn't exist
### The error may exist in file [D:\dev\java\eclipse\workspace\mes\target\classes\mapper\db2\emp.xml]
### The error may involve com.iut.mes.mapper.db2.Db2Mapper.insertEmp-Inline
### The error occurred while setting parameters
### SQL: insert into empq (id) values ('aaa')
### Cause: java.sql.SQLSyntaxErrorException: Table 'exoluse1.empq' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'exoluse1.empq' doesn't exist] with root cause