멀티 DataSource 접속 방법 정리

한 개의 프로젝트에서 하나의 Database에만 접속하는 경우가 대부분이지만, 2개 이상의 데이터 베이스에 접속하는 경우도 발생하게 된다.

이럴 경우 어떻게 셋팅을 해야 하는지 정리한다.

 

application.yml 설정파일

spring:
    profiles:
        active: local
    application:
        name: usercms

application-local.yml

server:
    port: 9090
spring:
    profiles: local
    domain: localhost
    datasource-a-write:
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://a-db-url:3306/a?autoReconnect=true&useSSL=false
        username: id
        password: password
    datasource-a-read:
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://a-db-url:3306/a?autoReconnect=true&useSSL=false
        username: id
        password: password
    datasource-b-write:
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://b-db-url:3306/b?autoReconnect=true&useSSL=false
        username: id
        password: password
    datasource-b-read:
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://b-db-url:3306/b?autoReconnect=true&useSSL=false
        username: id
        password: password

 

Database의 read, write 구분을 위한 설정

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

@Slf4j
public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceType = TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "read" : "write";
        return dataSourceType;
    }
}

 

a 서버에 접속하기 위한 DataSource

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
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.jdbc.datasource.LazyConnectionDataSourceProxy;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Configuration
public class ADataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource-a-read")
    public DataSource aReadDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource-a-write")
    public DataSource aWriteDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource aRoutingDataSource(@Qualifier("aWriteDataSource") DataSource writeDataSource, @Qualifier("aReadDataSource") DataSource readDataSource) {
        ReplicationRoutingDataSource aRoutingDataSource = new ReplicationRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<Object, Object>();
        dataSourceMap.put("write", writeDataSource);
        dataSourceMap.put("read", readDataSource);
        aRoutingDataSource.setTargetDataSources(dataSourceMap);
        aRoutingDataSource.setDefaultTargetDataSource(aReadDataSource());

        return aRoutingDataSource;
    }

    @Primary
    @Bean
    public DataSource aDataSource(@Qualifier("aRoutingDataSource") DataSource routingDataSource) {
        log.debug("#### DATA SOURCE");
        return new LazyConnectionDataSourceProxy(routingDataSource);
    }
}

 

a entity에 a DataSource를 맵핑하기 위한 소스. a를 메인으로 사용하기 위해 @Primary 어노테이션 사용

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
//@ComponentScan
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = {
                "a DataSource를 이용하는 도메인의 entity가 위치한 패키지"
        },
        entityManagerFactoryRef = "aEntityManagerFactory",
        transactionManagerRef = "aTransactionManager"
)
public class ADataManagerConfig {
    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean aEntityManagerFactory(@Qualifier("aDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean emfb = new LocalContainerEntityManagerFactoryBean();
        emfb.setDataSource(dataSource);
        emfb.setPersistenceProvider(new HibernatePersistenceProvider());
        emfb.setPersistenceUnitName("aEntityManager");
        emfb.setPackagesToScan(
                "a DataSource를 이용하는 도메인의 entity가 위치한 패키지"
        );
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setShowSql(true);
        jpaVendorAdapter.setGenerateDdl(false);
//        //properties.setProperty(“hibernate.hbm2ddl.auto”, “none”);
//        Properties properties = new Properties();
//        properties.setProperty("show_sql", "true");
//        emfb.setJpaProperties(properties);
        emfb.setJpaVendorAdapter(jpaVendorAdapter);

        return emfb;
    }

    @Primary
    @Bean
    public PlatformTransactionManager aTransactionManager(EntityManagerFactory entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor aExceptionTranslationPostProcessor() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

 

b 서버에 접속하기 위한 DataSource

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
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.jdbc.datasource.LazyConnectionDataSourceProxy;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Configuration
public class BDataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource-b-read")
    public DataSource bReadDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource-b-write")
    public DataSource bWriteDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource bRoutingDataSource(@Qualifier("bWriteDataSource") DataSource writeDataSource, @Qualifier("bReadDataSource") DataSource readDataSource) {
        ReplicationRoutingDataSource bRoutingDataSource = new ReplicationRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<Object, Object>();
        dataSourceMap.put("write", writeDataSource);
        dataSourceMap.put("read", readDataSource);
        bRoutingDataSource.setTargetDataSources(dataSourceMap);
        bRoutingDataSource.setDefaultTargetDataSource(bReadDataSource());

        return bRoutingDataSource;
    }

    @Primary
    @Bean
    public DataSource bDataSource(@Qualifier("bRoutingDataSource") DataSource routingDataSource) {
        log.debug("#### DATA SOURCE");
        return new LazyConnectionDataSourceProxy(routingDataSource);
    }
}

 

b entity에 b DataSource를 맵핑하기 위한 소스

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
//@ComponentScan
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = {
                "b DataSource를 이용하는 도메인의 entity가 위치한 패키지"
        },
        entityManagerFactoryRef = "bEntityManagerFactory",
        transactionManagerRef = "bTransactionManager"
)
public class BDataManagerConfig {
    @Bean
    public LocalContainerEntityManagerFactoryBean bEntityManagerFactory(@Qualifier("bDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean emfb = new LocalContainerEntityManagerFactoryBean();
        emfb.setDataSource(dataSource);
        emfb.setPersistenceProvider(new HibernatePersistenceProvider());
        emfb.setPersistenceUnitName("bEntityManager");
        emfb.setPackagesToScan(
                "b DataSource를 이용하는 도메인의 entity가 위치한 패키지"
        );
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setShowSql(true);
        jpaVendorAdapter.setGenerateDdl(false);
//        //properties.setProperty(“hibernate.hbm2ddl.auto”, “none”);
//        Properties properties = new Properties();
//        properties.setProperty("show_sql", "true");
//        emfb.setJpaProperties(properties);
        emfb.setJpaVendorAdapter(jpaVendorAdapter);

        return emfb;
    }

    @Bean
    public PlatformTransactionManager bTransactionManager(EntityManagerFactory entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor bExceptionTranslationPostProcessor() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

 

이와 같이 하게 되면 2개의 별도 DB에 접속할 수 있다.

다만 QueryDSL을 사용하게 될 경우 기존에는 QuerydslRepositorySupport을 상속받아 사용했는데, 이렇게 되면 2개 중 어떤 것을 사용해야 할지 몰라 에러를 내게 된다.

따라서 다음과 같이 QuerydslRepositorySupport을 상속받아 특정 메소드를 OverRide 한 클래스를 만들어야 한다.

그리고 QueryDSL을 사용하는 곳에서는 새롭게 상속받아 작성한 클래스를 상속받아 사용하면 된다.

 

a QuerydslRepositorySupport

import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@Repository
public abstract class AQueryDslRepositorySupport extends QuerydslRepositorySupport {
    public AQueryDslRepositorySupport(Class<?> domainClass) {
        super(domainClass);
    }

    @Override
    @PersistenceContext(unitName = "aEntityManager")
    public void setEntityManager(EntityManager entityManager) {
        super.setEntityManager(entityManager);
    }
}

 

b QuerydslRepositorySupport

import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@Repository
public abstract class BQueryDslRepositorySupport extends QuerydslRepositorySupport {
    public BQueryDslRepositorySupport(Class<?> domainClass) {
        super(domainClass);
    }

    @Override
    @PersistenceContext(unitName = "bEntityManager")
    public void setEntityManager(EntityManager entityManager) {
        super.setEntityManager(entityManager);
    }
}

 

각 entityManger에 맞게 QueryDSL을 사용하게 되는 경우...

import kr.co.within.cms.user.api.infrastructure.config.AQueryDslRepositorySupport;

public class AQueryRepositoryImpl extends AQueryDslRepositorySupport implements AQueryRepositoryCustom {
    public AQueryRepositoryImpl() {
        super(A.class);
    }
}

import kr.co.within.cms.user.api.infrastructure.config.BQueryDslRepositorySupport;

public class BQueryRepositoryImpl extends BQueryDslRepositorySupport implements BQueryRepositoryCustom {
    public BQueryRepositoryImpl() {
        super(B.class);
    }
}

 

기억이 짧아 자꾸 까먹는 나를 위하여 기록한다.

 

참고. 멀티 DataSource에서 트랜잭션을 처리하기 위해서는 아래 포스팅 참조.

https://supawer0728.github.io/2018/03/22/spring-multi-transaction/

(Spring)다중 DataSource 처리