[Spring] DataSource

Multiple DataSource

설명

• 한 애플리케이션에서 여러 MySQL 서버에 접근하여 사용해야할 경우에 대하여 설명한다.
• 아래 예제에서는 각 MySQL 서버를 포트로 구분한다.(ex. 13306, 23306)
• 13306 포트의 MySQL 서버는 일반적인 데이터(ex. Student)를 저장하는 DB로 사용.
• 23306 포트의 MySQL 서버는 보조적인 데이터(ex. Logging)를 저장하는 DB로 사용.
• @Transactional 메소드 안에서 데이터소스가 다른 레파지토리들을 사용할 경우 올바르게 트랜잭션 처리가 될 수 있도록 ChainedTransactionManager, JtaTransactionManager와 같은 방법을 사용해야 한다.
• 아래 예제에서는 JPA를 활용하기 때문에 필요하면 아래 링크 참고
  • https://sg-choi.tistory.com/295

테이블 생성

• 13306 포트의 MySQL에 테이블 생성

  CREATE TABLE IF NOT EXISTS Student
  (
      studentSeq BIGINT NOT NULL AUTO_INCREMENT,
      name       VARCHAR(200),
      age        INTEGER,
      PRIMARY KEY (studentSeq)
  );
  

• 23306 포트의 MySQL에 테이블 생성

  CREATE TABLE IF NOT EXISTS Logging
  (
      logSeq  BIGINT NOT NULL AUTO_INCREMENT,
      message VARCHAR(200),
      PRIMARY KEY (logSeq)
  );
  

application.properties 설정

...

# MySQL1
spring.datasource.main.jdbc-url=jdbc:mysql://localhost:13306/test?characterEncoding=UTF-8&serverTimezone=Asia/Seoul&useSSL=false
spring.datasource.main.username=test
spring.datasource.main.password=test
spring.datasource.main.driver-class-name=com.mysql.cj.jdbc.Driver

# MySQL2
spring.datasource.logging.jdbc-url=jdbc:mysql://localhost:23306/test?characterEncoding=UTF-8&serverTimezone=Asia/Seoul&useSSL=false
spring.datasource.logging.username=test
spring.datasource.logging.password=test
spring.datasource.logging.driver-class-name=com.mysql.cj.jdbc.Driver

Student 관련 코드 추가

• Entity 추가

  package com.example.student.entity;
  ...
  
  @AllArgsConstructor
  @NoArgsConstructor
  @Getter
  @Setter
  @Entity
  public class Student {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long studentSeq;
      private String name;
      private Integer age;
  }
  

• Repository 추가

  package com.example.student.repository;
  ...
  
  public interface StudentRepository extends JpaRepository<Student, Long> {
  }
  

• Config 추가

  @EnableJpaRepositories(
      basePackages = {"com.example.student.repository"},
      entityManagerFactoryRef = "mainEntityManagerFactory",
      transactionManagerRef = "mainTransactionManager"
  )
  @Configuration
  public class MainDatabaseConfig {
      @ConfigurationProperties(prefix = "spring.datasource.main")
      @Bean
      public DataSource mainDataSource() {
          return DataSourceBuilder.create().type(HikariDataSource.class).build();
      }
  
      @Bean
      public LocalContainerEntityManagerFactoryBean mainEntityManagerFactory(@Qualifier("mainDataSource") DataSource mainDataSource) {
          LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
          entityManager.setDataSource(mainDataSource);
          entityManager.setPackagesToScan("com.example.student.entity");
          entityManager.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
          return entityManager;
      }
  
      @Bean
      public PlatformTransactionManager mainTransactionManager(@Qualifier("mainEntityManagerFactory") EntityManagerFactory mainEntityManagerFactory) {
          return new JpaTransactionManager(mainEntityManagerFactory);
      }
  }
  

Logging 관련 코드 추가

• Entity 추가

  package com.example.logging.entity;
  ...
  
  @AllArgsConstructor
  @NoArgsConstructor
  @Getter
  @Setter
  @Entity
  public class Logging {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long logSeq;
      private String message;
  }
  

• Repository 추가

  package com.example.logging.repository;
  ...
  
  public interface LoggingRepository extends JpaRepository<Logging, Long> {
  }
  

• Config 추가

  @EnableJpaRepositories(
      basePackages = {"com.example.logging.repository"},
      entityManagerFactoryRef = "loggingEntityManagerFactory",
      transactionManagerRef = "loggingTransactionManager"
  )
  @Configuration
  public class LoggingDatabaseConfig {
      @ConfigurationProperties(prefix = "spring.datasource.logging")
      @Bean
      public DataSource loggingDataSource() {
          return DataSourceBuilder.create().type(HikariDataSource.class).build();
      }
  
      @Bean
      public LocalContainerEntityManagerFactoryBean loggingEntityManagerFactory(@Qualifier("loggingDataSource") DataSource loggingDataSource) {
          LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
          entityManager.setDataSource(loggingDataSource);
          entityManager.setPackagesToScan("com.example.logging.entity");
          entityManager.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
          return entityManager;
      }
  
      @Bean
      public PlatformTransactionManager loggingTransactionManager(@Qualifier("loggingEntityManagerFactory") EntityManagerFactory loggingEntityManagerFactory) {
          return new JpaTransactionManager(loggingEntityManagerFactory);
      }
  }
  

Test

@SpringBootTest
public class MultipleDataSourceTest {
    @Autowired
    private StudentRepository studentRepository;

    @Autowired
    private LoggingRepository loggingRepository;

    @Test
    public void test() {
        studentRepository.save(new Student(null, "john", 25));
        loggingRepository.save(new Logging(null, "Hello World"));

        Student dbStudent = studentRepository.findAll().stream().findFirst().get();
        Logging dbLogging = loggingRepository.findAll().stream().findFirst().get();

        assertEquals("john", dbStudent.getName());
        assertEquals("Hello World", dbLogging.getMessage());
    }
}

참고

• https://www.baeldung.com/spring-data-jpa-multiple-databases