JPA - JPA 다중 Database 사용법

서비스를 구현하는 중 각 서비스들이 DB를 access하는 편차가 커서 DB를 나누게 되었다.

그런 와중에  성능을 더욱 향상 시키기 위해 하나의 서비스에서 "쓰기를 위한 DB" 와 "읽기를 위한 DB" 로 나누어 설계하였다

이 때 하나의 서비스에서 여러 DB를 접근하기 위한 방법이 필요하게 되었다

DB

두개의 DB를 구성하기 위해 2개의 가상머신으로 구성했다

1. Master DB

IP 

 - 1.0.0.11

DB

  

Table

  

2. Stanby DB

IP

 - 1.0.0.12

DB

 

Table

 

Spring Boot

Settings

 1. Diretory Tree

 2. build.gradle

buildscript {
    ext {
        springBootVersion = '2.1.2.RELEASE'
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}
 
apply plugin: 'java'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'
 
group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'
 
repositories {
    mavenCentral()
}
 
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    // https://mvnrepository.com/artifact/mysql/mysql-connector-java
    compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'
    // https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-data-jpa', version: '2.1.2.RELEASE'
    // spring-boot-starter-jdbc
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-jdbc', version: '2.1.0.RELEASE'
}

 3. application.properties

## Mater DB Setting ##
spring.master.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.master.datasource.username=root
spring.master.datasource.password=It1
spring.master.datasource.url=jdbc:mysql://1.0.0.11:3306/master?characterEncoding=UTF-8&serverTimezone=UTC
 
## Stanby DB Setting ##
spring.stanby.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.stanby.datasource.username=root
spring.stanby.datasource.password=It1
spring.stanby.datasource.url=jdbc:mysql://1.0.0.12:3306/stanby?characterEncoding=UTF-8&serverTimezone=UTC

CODE

 1. DB Config

- Spring Boot에서 사용할 DB설정을 위한 코드

*mysqlDataSource()

 - Datasource를 반환하는 메소드로 stanbyDBConfig에도 당연히 같은 메소드가 포함된다

   Bean(name ="") 을 지정하여 Master와 Stanby가 서로 다른 Bean을 생성하도록 해야한다 

   아니라면 메소드 이름을 통해 자동으로 Bean이 생성되므로 메소드의 이름을 서로 다르게 해야한다.

*@Primay

 - SpringBoot에서 기본으로 사용될 DB의 설정을 알려주기 위한 어노테이션이다 

   MasterDBConfig에만 지정하도록 해야한다

  1) MasterDBConfig

package com.example.multi.config;
 
import com.example.multi.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;
 
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "masterEntityManager",
        transactionManagerRef = "masterTransactionManager",
        basePackages = "com.example.multi.dao.master"
)
public class MasterDBConfig {
    @Autowired
    private Environment env;
 
    @Primary
    @Bean
    public DataSource mysqlDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.master.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.master.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.master.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.master.datasource.password"));
        return dataSource;
    }
 
    @Primary
    @Bean(name = "masterEntityManager")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(mysqlDataSource())
                .properties(hibernateProperties())
                .packages(User.class)
                .persistenceUnit("userPU")
                .build();
    }
 
    @Primary
    @Bean(name = "masterTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("masterEntityManager") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
 
    private Map hibernateProperties() {
        Resource resource = new ClassPathResource("hibernate.properties");
 
        try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
 
            return properties.entrySet().stream()
                    .collect(Collectors.toMap(
                            e -> e.getKey().toString(),
                            e -> e.getValue())
                    );
        } catch (IOException e) {
            return new HashMap();
        }
    }
}

  2) StanbyDBConfig

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "stanbyEntityManager",
        transactionManagerRef = "stanbyTransactionManager",
        basePackages = "com.example.multi.dao.stanby"
)
public class StanbyDBConfig {
    @Autowired
    private Environment env;
 
    @Bean
    public DataSource stanbyDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.stanby.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.stanby.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.stanby.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.stanby.datasource.password"));
        return dataSource;
    }
 
    @Bean(name = "stanbyEntityManager")
    public LocalContainerEntityManagerFactoryBean stanbyEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(stanbyDataSource())
                .properties(hibernateProperties())
                .packages(User.class)
                .persistenceUnit("userPU")
                .build();
    }
 
    @Bean(name = "stanbyTransactionManager")
    public PlatformTransactionManager stanbyTransactionManager(@Qualifier("stanbyEntityManager") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
 
    private Map hibernateProperties() {
        Resource resource = new ClassPathResource("hibernate.properties");
 
        try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
 
            return properties.entrySet().stream()
                    .collect(Collectors.toMap(
                            e -> e.getKey().toString(),
                            e -> e.getValue())
                    );
        } catch (IOException e) {
            return new HashMap();
        }
    }
}

 2. JPA Repository

  1) UserMasterDao  

package com.example.multi.dao.master;
 
import com.example.multi.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
 
public interface UserMasterDao extends JpaRepository<User, Integer> {
}

  2) UserStanbyDao

package com.example.multi.dao.stanby;
 
import com.example.multi.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
 
public interface UserStanbyDao extends JpaRepository<User, Integer> {
}

3. Model

 1. User  

package com.example.multi.model;
 
import javax.persistence.*;
 
@Entity
public class User {
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    @Column
    private String name;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
}

4. Controller 

package com.example.multi.controller;
 
import com.example.multi.dao.master.UserMasterDao;
import com.example.multi.dao.stanby.UserStanbyDao;
import com.example.multi.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
 
@RestController
public class Controller {
 
    @Autowired
    private UserMasterDao masterDao;
 
    @GetMapping("/master")
    @ResponseBody
    public String getMasterUser(){
        String data = "";
 
        List<User> users = masterDao.findAll();
        for(User user : users) {
            data += user.getName();
            data += "\n";
        }
 
        return data;
    }
 
    @Autowired
    private UserStanbyDao stanbyDao;
 
    @GetMapping("/stanby")
    @ResponseBody
    public String getStanbyUser(){
        String data = "";
 
        List<User> users = stanbyDao.findAll();
        for(User user : users) {
            data += user.getName();
            data += "\n";
        }
 
        return data;
    }
 
}