이전 포스팅에서 두개의 데이터베이스를 연결하고 설정을 해 보았습니다..
이 글은 아래의 유튜브를 보고 실습한 것으로, 궁금하신분들은 아래 유튜브를 참고해주세요!
Youtube - J108. Spring JPA Multiple Databases(Seungchul Park)
- @Bean으로 등록 된 DataSource
- Spring은 트랜젝션에 집입하면 Database Connection여부를 물어본 후 설정된 DataSource의 커넥션을 가져오는데, LazyConnectDataSourceProxy를 리턴한다면 커넥션을 점유하지 않은 상태로 필요시에 커넥션을 점유하게 된다.
- EntityManagerFactory 등록하기
- LocalContainerEntityManagerFactoryBean 등록하기
- PlatformTransactionManager 등록하기
import com.google.common.collect.ImmutableMap;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
@Configuration
@ConfigurationProperties(prefix = "spring.db2.datasource")
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactory2",
transactionManagerRef = "transactionManager2",
basePackages = {"com.database.multidatabase.db2.repository"}
)
public class DbConfig2 extends HikariConfig {
@Bean
public DataSource dataSource2() {
return new LazyConnectionDataSourceProxy(new HikariDataSource(this));
}
@Bean
public EntityManagerFactory entityManagerFactory2() {
JpaVendorAdapter vendorAdapter =new HibernateJpaVendorAdapter();
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
factory.setDataSource(this.dataSource2());
factory.setJpaVendorAdapter(vendorAdapter);
factory.setJpaPropertyMap(ImmutableMap.of(
"hibernate.hbm2ddl.auto", "update",
"hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect",
"hibernate.show_sql", "true"
));
factory.setPackagesToScan("com.database.multidatabase.db2.domain");
factory.setPersistenceUnitName("db2");
factory.afterPropertiesSet();
return factory.getObject();
}
@Bean
public PlatformTransactionManager transactionManager2() {
JpaTransactionManager tm = new JpaTransactionManager();
tm.setEntityManagerFactory(entityManagerFactory2());
return tm;
}
}위와 같이 두번째 데이터베이스도 설정을 완료하였다.
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
@Configuration
public class ChainTxConfig {
@Bean
@Primary
public PlatformTransactionManager transactionManager(PlatformTransactionManager transactionManager1, PlatformTransactionManager transactionManager2) {
return new ChainedTransactionManager(transactionManager1, transactionManager2);
}
}- PlatformTransactionManager는 트랜젝션 처리의 중심이 되는 인터페이스이다. 트렌젝션을 관리해야 하는 메서드들을 ChainedTransactionManager에 넣어준다.
Entity
@Getter
@Setter
@ToString(exclude = "empList")
@Entity
@Table(name = "dept")
@NoArgsConstructor
public class Dept1 {
public Dept1(Long deptno, String dname, String loc) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
@Id
private Long deptno;
private String dname;
private String loc;
@OneToMany(mappedBy = "dept", cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
private List<Emp1> empList;
}import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import javax.persistence.*;
import java.sql.Timestamp;
@Getter
@Setter
@ToString(exclude = "empList")
@Entity
@Table(name = "emp")
@NoArgsConstructor
public class Emp1 {
public Emp1(Long empno, String ename, String job, Integer mgr, Timestamp hiredate, Integer sal, Integer comm, Long deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
Dept1 dept = new Dept1();
dept.setDeptno(deptno);
this.dept = dept;
}
@Id
private Long empno;
private String ename;
private String job;
private Integer mgr;
private Timestamp hiredate;
private Integer sal;
private Integer comm;
@ManyToOne
@JoinColumn(name="deptno", nullable=false)
private Dept1 dept;
}Repository
import com.database.multidatabase.db1.domain.Dept1;
import org.springframework.data.repository.CrudRepository;
public interface DeptRepository1 extends CrudRepository<Dept1, Long> {
}import com.database.multidatabase.db1.domain.Emp1;
import org.springframework.data.repository.CrudRepository;
public interface EmpRepository1 extends CrudRepository<Emp1, Long> {
}Service
public interface DeptService {
public void tranXATest();
public void tranNonXATest();
}import com.database.multidatabase.db1.domain.Dept1;
import com.database.multidatabase.db1.repository.DeptRepository1;
import com.database.multidatabase.db2.repository.DeptRepository2;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
@RequiredArgsConstructor
public class DeptServiceImpl implements DeptService {
private final DeptRepository1 deptRepository1;
private final DeptRepository2 deptRepository2;
@Override
@Transactional
public void tranXATest() {
Dept1 dept50 = new Dept1(50L, "CCCC", "cccc");
deptRepository1.save(dept50); // Transaction으로 묶여서 세이브 안됨
deptRepository2.deleteAll(); // 테스트환경에서
}
@Override
public void tranNonXATest() {
Dept1 dept50 = new Dept1(50L, "CCCC", "cccc");
deptRepository1.save(dept50); // Transaction으로 안묶여서 세이브됨
deptRepository2.deleteAll();
}
}-
tranXATest와 tranNonXATest를 deleteAll로 일부러 실패시키는 매소드를 만들어서 확인해보기로 했다.
Dept와 Emp가 OneToMany ManyToOne으로 묶여있어 에러가 발생함 -
tranXATest는 @Transactional를 선언하여 트랜젝션 환경을 만들어주어 실패시 Rollback이 수행되도록 생성하였고, tranNonXATest는 실패해도 save가 되는 메소드이다.
설정이 끝났으니 테스트를 시작해보자.
import com.database.multidatabase.db1.domain.Dept1;
import com.database.multidatabase.db1.domain.Emp1;
import com.database.multidatabase.db2.domain.Dept2;
import com.database.multidatabase.db2.domain.Emp2;
import com.database.multidatabase.db2.repository.DeptRepository2;
import com.database.multidatabase.db2.repository.EmpRepository2;
import com.database.multidatabase.service.DeptService;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Commit;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
@Commit
@SpringBootTest
class TestDb {
@Autowired
EmpRepository1 empRepository1;
@Autowired
DeptRepository1 deptRepository1;
@Autowired
EmpRepository2 empRepository2;
@Autowired
DeptRepository2 deptRepository2;
@Autowired
DeptService deptService;
@BeforeEach
@Transactional
public void pre() {
empRepository1.deleteAll();
empRepository2.deleteAll();
deptRepository1.deleteAll();
deptRepository2.deleteAll();
insert_test();
insert_db2();
}
@Test
void trTest() {
try {
deptService.tranXATest();
} catch (Exception e) {
}
Assertions.assertThat(deptRepository1.findById(50L)).isNotPresent();
}
@Test
void trNonTest() {
try {
deptService.tranNonXATest();
} catch (Exception e) {
}
Assertions.assertThat(deptRepository1.findById(50L)).isPresent();
}
@Test
void insert_test() {
List<Dept1> list = new ArrayList<>();
Dept1 dept10 = new Dept1(10L, "ACCOUNTING", "NEW YORK");
list.add(dept10);
Dept1 dept15 = new Dept1(15L, "TEST", "TEST");
list.add(dept15);
Dept1 dept20 = new Dept1(20L, "RESEARCH", "DALLAS");
list.add(dept20);
Dept1 dept25 = new Dept1(25L, "SALES", "CHICAGO");
list.add(dept25);
Dept1 dept30 = new Dept1(30L, "OPERATIONS", "BOSTON");
list.add(dept30);
deptRepository1.saveAll(list);
List<Emp1> empList = new ArrayList<Emp1>();
Emp1 emp;
emp = new Emp1(7839L, "KING" , "PRESIDENT", null, getTimestamp("1981-11-17 00:00:00.000"), 5000, null, 10L); empList.add(emp);
emp = new Emp1(7698L, "BLAKE" , "MANAGER" , 7839, getTimestamp("1981-01-05 00:00:00.000"), 2850, null, 30L); empList.add(emp);
emp = new Emp1(7782L, "CLARK" , "MANAGER" , 7839, getTimestamp("1981-09-06 00:00:00.000"), 2450, null, 10L); empList.add(emp);
emp = new Emp1(7566L, "JONES" , "MANAGER" , 7839, getTimestamp("1981-02-04 00:00:00.000"), 2975, null, 20L); empList.add(emp);
emp = new Emp1(7788L, "SCOTT" , "ANALYST" , 7566, getTimestamp("1987-07-13 00:00:00.000"), 3000, null, 20L); empList.add(emp);
emp = new Emp1(7902L, "FORD" , "ANALYST" , 7566, getTimestamp("1981-03-12 00:00:00.000"), 3000, null, 20L); empList.add(emp);
emp = new Emp1(7369L, "SMITH" , "CLERK" , 7566, getTimestamp("1980-12-17 00:00:00.000"), 800, null, 20L); empList.add(emp);
emp = new Emp1(7499L, "ALLEN" , "SALESMAN" , 7698, getTimestamp("1981-02-20 00:00:00.000"), 1600, 300, 30L); empList.add(emp);
emp = new Emp1(7521L, "WARD" , "SALESMAN" , 7698, getTimestamp("1981-02-22 00:00:00.000"), 1250, 500, 30L); empList.add(emp);
emp = new Emp1(7654L, "MARTIN", "SALESMAN" , 7698, getTimestamp("1981-09-28 00:00:00.000"), 1250, 1400, 30L); empList.add(emp);
emp = new Emp1(7844L, "TURNER", "SALESMAN" , 7698, getTimestamp("1981-09-08 00:00:00.000"), 1500, 0, 30L); empList.add(emp);
emp = new Emp1(7876L, "ADAMS" , "CLERK" , 7698, getTimestamp("1971-07-13 00:00:00.000"), 1100, null, 20L); empList.add(emp);
emp = new Emp1(7900L, "JAMES" , "CLERK" , 7698, getTimestamp("1981-03-12 00:00:00.000"), 950, null, 30L); empList.add(emp);
emp = new Emp1(7934L, "MILLER", "CLERK" , 7782, getTimestamp("1982-01-23 00:00:00.000"), 1300, null, 10L); empList.add(emp);
empRepository1.saveAll(empList);
}
@Test
void insert_db2() {
List<Dept2> list2 = new ArrayList<>();
Dept2 dept210 = new Dept2(10L, "ACCOUNTING", "NEW YORK");list2.add(dept210);
Dept2 dept215 = new Dept2(15L, "TEST", "TEST");list2.add(dept215);
Dept2 dept220 = new Dept2(20L, "RESEARCH", "DALLAS");list2.add(dept220);
Dept2 dept225 = new Dept2(25L, "SALES", "CHICAGO");list2.add(dept225);
Dept2 dept230 = new Dept2(30L, "OPERATIONS", "BOSTON");list2.add(dept230);
deptRepository2.saveAll(list2);
List<Emp2> empList = new ArrayList<Emp2>();
Emp2 emp;
emp = new Emp2(7839L, "KING" , "PRESIDENT", null, getTimestamp("1981-11-17 00:00:00.000"), 5000, null, 10L); empList.add(emp);
emp = new Emp2(7698L, "BLAKE" , "MANAGER" , 7839, getTimestamp("1981-01-05 00:00:00.000"), 2850, null, 30L); empList.add(emp);
emp = new Emp2(7782L, "CLARK" , "MANAGER" , 7839, getTimestamp("1981-09-06 00:00:00.000"), 2450, null, 10L); empList.add(emp);
emp = new Emp2(7566L, "JONES" , "MANAGER" , 7839, getTimestamp("1981-02-04 00:00:00.000"), 2975, null, 20L); empList.add(emp);
emp = new Emp2(7788L, "SCOTT" , "ANALYST" , 7566, getTimestamp("1987-07-13 00:00:00.000"), 3000, null, 20L); empList.add(emp);
emp = new Emp2(7902L, "FORD" , "ANALYST" , 7566, getTimestamp("1981-03-12 00:00:00.000"), 3000, null, 20L); empList.add(emp);
emp = new Emp2(7369L, "SMITH" , "CLERK" , 7566, getTimestamp("1980-12-17 00:00:00.000"), 800, null, 20L); empList.add(emp);
emp = new Emp2(7499L, "ALLEN" , "SALESMAN" , 7698, getTimestamp("1981-02-20 00:00:00.000"), 1600, 300, 30L); empList.add(emp);
emp = new Emp2(7521L, "WARD" , "SALESMAN" , 7698, getTimestamp("1981-02-22 00:00:00.000"), 1250, 500, 30L); empList.add(emp);
emp = new Emp2(7654L, "MARTIN", "SALESMAN" , 7698, getTimestamp("1981-09-28 00:00:00.000"), 1250, 1400, 30L); empList.add(emp);
emp = new Emp2(7844L, "TURNER", "SALESMAN" , 7698, getTimestamp("1981-09-08 00:00:00.000"), 1500, 0, 30L); empList.add(emp);
emp = new Emp2(7876L, "ADAMS" , "CLERK" , 7698, getTimestamp("1971-07-13 00:00:00.000"), 1100, null, 20L); empList.add(emp);
emp = new Emp2(7900L, "JAMES" , "CLERK" , 7698, getTimestamp("1981-03-12 00:00:00.000"), 950, null, 30L); empList.add(emp);
emp = new Emp2(7934L, "MILLER", "CLERK" , 7782, getTimestamp("1982-01-23 00:00:00.000"), 1300, null, 10L); empList.add(emp);
empRepository2.saveAll(empList);
}
public Timestamp getTimestamp(String timeStamp){
LocalDateTime localDateTime = LocalDateTime.parse(timeStamp, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"));
return Timestamp.valueOf(localDateTime);
}
}2022-10-18 08:06:56.385 WARN 12148 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1451, SQLState: 23000
2022-10-18 08:06:56.386 ERROR 12148 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Cannot delete or update a parent row: a foreign key constraint fails (`testdb2`.`emp`, CONSTRAINT `FKfqt0j25nlvjwt7qt1t3x7v6qf` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
2022-10-18 08:06:56.387 INFO 12148 --- [ main] o.h.e.j.b.internal.AbstractBatchImpl : HHH000010: On release of batch it still contained JDBC statements- 두가지 테스트 모두 의도했던대로 예외가 발생하였다.
- @Transactional 어노테이션이 선언된 tranXATest() 메소드는 아래와 같은 결과가 나왔다.
- @Transactional 어노테이션이 선언되지 않은 tranNonXATest() 메소드는 아래와 같은 결과가 나왔다.
이렇게 두개의 데이터베이스를 하나의 프로젝트에 연결하고, 트렌젝션이 묶일 수 있는지 테스트 해보았다.
틀리거나 잘못된 부분이 있다면 bht9011@gmail.com 로 제보 부탁드립니다.
읽어주셔서 감사합니다.
백엔드 프로그래머