[SpringBoot / MS SQL]Configuration을 설정하여 데이터베이스 2개 연결하기(with JPA)

데이터베이스 1개 연결

SpringBoot에서 제공하는 형식에 따라 spring 하위에 yaml 파일을 설정하기 때문에 별도 Configuration 설정을 불필요하다.

spring
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://localhost:8080;databaseName={dbName};encrypt=true;trustServerCertificate=true
    username : {ID}
    password: {PW}
  jpa:
    mapping-resources: {native query path}

 

데이터베이스 2개 연결

nativeQuery xml 파일은 spring에 설정해두고 multi라는 property 값을 설정하여 datasource를 나누었다.

위에서 1개 연결 설정시에는 SpringBoot에서 제공하는 형식에 맞추기 때문에 「url(또는 URL) : url 주소」로 설정하지만,

2개의 DB에 연결을 할때는 「jdbc-url : url 주소」 로 설정해주어야 한다고 한다.

이유는 그것이 HikariCP의 Database URL 설정이니까! (끄덕)

그냥 url로 했다가 url 값을 못찾는 오류가 발생했었으니 조심하자.

spring:
  jpa:
    mapping-resources: /query/nativeQuery.xml
multi:
  datasource:
    main:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://localhost:8080;databaseName={dbName1};encrypt=true;trustServerCertificate=true
      username: {ID}
      password: {PW}
      base-package: {repository path ex)com.beige0905.test.repository}
      jpa:
        database-platform: org.hibernate.dialect.SQLServer2012Dialect
        show-sql: true
        format-sql: true
        use-sql-comments: true
        hibernate.ddl-auto: update
        generate-ddl: false
    second:
      driver-class-name:
      jdbc-url: jdbc:sqlserver://localhost:8080;databaseName={dbName2};encrypt=true;trustServerCertificate=true
      username: {ID}
      password: {PW}
      base-package: {repository path ex)com.beige0905.test.repository}
      jpa:
        database-platform: org.hibernate.dialect.SQLServer2012Dialect
        show-sql: true
        format-sql: true
        use-sql-comments: true
        hibernate.ddl-auto: update
        generate-ddl: false

 

MainDBConfiguration

package com.beige0905.test.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableJpaRepositories(basePackages = {"${multi.datasource.main.base-package}"},
        entityManagerFactoryRef = "EntityManagerFactory",
        transactionManagerRef = "TransactionManager")
public class MPMainDatabaseConfig {

    @Value("${spring.jpa.mapping-resources}")
    String namedQuery;
    @Value("${multi.datasource.main.base-package}")
    String basePackage;
    @Value("${multi.datasource.main.jpa.database-platform}")
    String dbPlatform;

    @Primary
    @Bean(name = "RepositoryDataSource")
    @ConfigurationProperties(prefix = "multi.datasource.main")
    public DataSource getDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean masterEntityManager(@Qualifier("RepositoryDataSource") DataSource dataSource) {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(basePackage);
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        em.setMappingResources(namedQuery);

        Properties jpaProperties = new Properties();
        jpaProperties.put("hibernate.dialect", dbPlatform);
        em.setJpaProperties(jpaProperties);

        return em;
    }

    @Primary
    @Bean(name = "TransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("EntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager(entityManagerFactory.getObject());
        transactionManager.setNestedTransactionAllowed(true);
        return transactionManager;
    }
}

 

second Configuration

main Configuration과 동일하지만  Bean name을 다르게 설정해야하고 @Primary annotation은 제외한다

package com.beige0905.test.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableJpaRepositories(basePackages = {"${multi.datasource.second.base-package}"},
        entityManagerFactoryRef = "SubEntityManagerFactory",
        transactionManagerRef = "SubTransactionManager")
public class MPEntryLogConfig {
    @Value("${multi.datasource.second.base-package}")
    String basePackage;
    @Value("${multi.datasource.second.jpa.database-platform}")
    String dbPlatform;

    @Bean(name = "SubRepositoryDataSource")
    @ConfigurationProperties(prefix="multi.datasource.second")
    public DataSource getDataSource() { return DataSourceBuilder.create().build(); }

    @Bean(name = "SubEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean masterEntityManager(@Qualifier("SubRepositoryDataSource") DataSource dataSource) {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(basePackage);
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        Properties jpaProperties = new Properties();
        jpaProperties.put("hibernate.dialect", dbPlatform);
        em.setJpaProperties(jpaProperties);

        return em;
    }

    @Bean(name = "SubTransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("SubEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager(entityManagerFactory.getObject());
        transactionManager.setNestedTransactionAllowed(true);
        return transactionManager;
    }
}

 

MainVo

package com.beige0905.test.repository.vo;

import lombok.*;

import javax.persistence.*;
import java.sql.Timestamp;

@Getter
@Setter
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "product")
public class MainVo {

    public enum STATUS {
        AVAILABLE,
        UNAVAILABLE
    }
    
    @Id
    @Column(name = "product_no")
    private int productNo;
    
    @Column(name = "product_name")
    private String productName;
    
    @UpdateTimestamp
    @Column(name = "update_date")
    private Timestamp updateDate = Timestamp.valueOf(LocalDateTime.now());
    
    @Column(name ="status")
    @Enumerated(EnumType.STRING)
    private STATUS status;
}

 

MainRepository

package com.beige0905.test.repository;

import com.beige0905.test.repository.vo.MainVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface MainRepository extends JpaRepository<MainVo, String> {

    @Transactional
    @Modifying
    @Query("update MainVo v set v.status = ?1 where v.no = ?2")
    int updateStatus(MainVo.STATUS processing, int no);
}

 

SecondVo

package com.beige0905.test.second.repository.vo;

import lombok.*;

import javax.persistence.*;
import java.sql.Timestamp;

@Getter
@Setter
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "user_info")
public class SecondVo {
    @Id
    @Column(name = "user_no")
    private int userNo;
    @Column(name = "user_name")
    private String userName;
    @Column(name = "join_date")
    private Timestamp joinDate;

}

 

SecondRepository

package com.beige0905.test.secondDb.repository;

import com.beige0905.test.secondDb.repository.vo.SecondVo;
import org.springframework.data.jpa.repository.JpaRepository;

public interface SecondRepository extends JpaRepository<SecondVo, String> {
}

 

vo, repository 생성시 Main과 Second 별도 패키지를 사용해야한다.

 

 

참고자료

https://velog.io/@lehdqlsl/SpringBoot-JPA-Multiple-Databases-%EC%84%A4%EC%A0%95

SpringBoot JPA Multiple Databases 설정

https://wiki.webnori.com/pages/viewpage.action?pageId=9798284 

다중DB 환경설정 - WebFrameWork - WebNORI - IT개발문화 연구소

https://jojoldu.tistory.com/296

Spring Boot & HikariCP Datasource 연동하기