[Java] QueryDSL 을 사용하여 Multi Data Source 사용하기

안녕하세요. 이번 글에서는 JPA 또는 QueryDSL을 사용하면서 다중으로 데이터베이스를 연결해서 사용할 때에 필요한 정보를 모아보았습니다. 

가장 먼저 Oracle 에서 MSSQL 서버로 데이터를 옮길 때 사용하는 방법입니다. 

1. Oracle SQL Connection Setting

가장 먼저 기본적인 커넥션을 연결하는 Oracle DB의 데이터소스 세팅입니다. @Primary 어노테이션으로 이 DataSource를 기본 데이터소스로 연결하겠다는 것을 표현합니다. 

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "oracleEntityManagerFactory", transactionManagerRef = "oracleTransactionManager", basePackages = { "com.sma.oracle.repository" })
public class OracleDatabaseConfiguration {
    @Primary
    @Bean
    @ConfigurationProperties(prefix = "oracle.datasource")
    public DataSourceProperties  oracleDataSourceProperties(@Qualifier("oracleDataSourceProperties") DataSource oracleDataSourceProperties) {
        return new DataSourceProperties();
    }
    
    @Primary
    @Bean
    public DataSource oracleDataSource(@Qualifier("oracleDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }
    
    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource, EntityManagerFactoryBuilder builder) {
        return builder.dataSource(oracleDataSource).packages("com.sma.oracle.entity").persistenceUnit("oracle").build();
    }
    
    @Primary
    @Bean
    public PlatformTransactionManager oracleTransactionManager(@Qualifier("oracleEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

 

2. SqlServer Connection Setting 

다음으로 2번째로 연결할 데이터 소스인, MSSQL(SqlServer) 를 연결하는 소스입니다. 

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "sqlServerEntityManagerFactory", transactionManagerRef = "sqlServerTransactionManager", basePackages = "com.sma.aml.repository")
public class SqlServerDatabaseConfiguration {
 
    @Bean
    @ConfigurationProperties(prefix = "sqlServer.datasource")
    public DataSourceProperties sqlServerDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean
    public DataSource sqlServerDataSource(@Qualifier("sqlServerDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }


    @Bean
    public EntityManagerFactory  sqlServerEntityManagerFactory(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource, EntityManagerFactoryBuilder builder) {
        return builder.dataSource(sqlServerDataSource).packages("com.sma.aml.entity").persistenceUnit("sqlserver").build();
    }

    @Bean
    public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerEntityManagerFactory") EntityManagerFactory factory) {
        JpaTransactionManager jpa = new JpaTransactionManager(factory);
        jpa.setNestedTransactionAllowed(true);
        jpa.setRollbackOnCommitFailure(true);

        return jpa;
    }
}

 

3. application.properties 설정

이번에는 각각의 데이터베이스를 연결할 설정값을 저장하는 저장소 설정입니다. 

##application.properties
##SQL Server
sqlserver.datasource.url=jdbc:sqlserver://127.0.0.1;databaseName=TEST
sqlserver.datasource.username=sa
sqlserver.datasource.password=YourStrongPassword$123$db
##Oracle
oracle.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:test
oracle.datasource.username=root
oracle.datasource.password=YourStrongPassword$123$db

 

4. TransactionManagerConfiguration 

데이터소스에 연결되면 필요한 트랜잭션 매니저를 설정하는 소스입니다. 

@Configuration
public class TransactionManagerConfig {

    @Bean(name = "chainedTransactionManager")
    public ChainedTransactionManager transactionManager (
            @Qualifier("sqlServerTransactionManager") PlatformTransactionManager sqlTransactionManager,
            @Qualifier("oracleTransactionManager") PlatformTransactionManager oracleTransactionManager) {
        return new ChainedTransactionManager(sqlTransactionManager, oracleTransactionManager);
    }
}

 

5. Service 소스

@Service
@Transactional
public class BackendService {

    //Category table is in SQL Server
    @Autowired
    private CategoryRepository categoryRepository;
 
    //Audit table is in Oracle Server
    @Autowired
    private AuditLogRepository auditLogRepository;

    @Transactional(value = "chainedTransactionManager")
    public void saveIntoDatabase() {
       
       // persist data Sql server
      categoryRepository.save(new record 1);
      //p ersist data Sql server
      categoryRepository.save(new record 2);
      //..more logic and then at somepoints we got Run time error...
      auditLogRepository.trackAuth(); //oracle database
    
     throws new Exception("sth went wrong, rollback transactions");
    }
}

이제 여기까지 다른 데이터 소스를 적용하는 방법을 알아보았다면 아래에서는 같은 데이터 소스를 작동시키는 방법을 살펴보려 합니다. 데이터소스가 2개이고 각각 entityManagerFactory 생성 메소드가 있는 경우에 위와 같이 그대로 구현하면 아래와 같은 오류가 발생하게 됩니다. 

Parameter 0 of method setEntityManager in org.springframework.data.jpa.repository.support.QuerydslRepositorySupport required a single bean, but 2 were found:
	- org.springframework.orm.jpa.SharedEntityManagerCreator#0: defined by method 'createSharedEntityManager' in null
	- org.springframework.orm.jpa.SharedEntityManagerCreator#1: defined by method 'createSharedEntityManager' in null

 

QueryDslConfiguration에서 entityManager에 객체 주입을 하는 과정에서 2개의 entityManager 팩토링 생성 메서드 중 어느 것으로 주입을 할지 알 수 없기에 발생한 오류가 되겠습니다.  아래와 같이 수정합니다. 

 

6. DataSource1Configuration

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("dataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource);
        entityManagerFactoryBean.setPackagesToScan("com.example.rds.jpa.entity1");
        entityManagerFactoryBean.setPersistenceUnitName("firstEntityManager"); // 영속성 객체 이름을 지정
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
        entityManagerFactoryBean.setJpaPropertyMap(hibernateProperties());
        return entityManagerFactoryBean;
    }

 

7. DataSource2Configuration

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("dataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource);
        entityManagerFactoryBean.setPackagesToScan("com.example.rds.jpa.entity2");
        entityManagerFactoryBean.setPersistenceUnitName("secondEntityManager"); // 영속성 객체 이름을 지정
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
        entityManagerFactoryBean.setJpaPropertyMap(hibernateProperties());
        return entityManagerFactoryBean;
    }

 

8. QueryDslConfiguration

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

@Configuration
public class QueryDslConfig {

    @PersistenceContext(unitName = "firstEntityManager")
    private EntityManager firstEntityManager;

    @PersistenceContext(unitName = "secondEntityManager")
    private EntityManager secondEntityManager;

    @Bean
    public JPAQueryFactory firstJpaQueryFactory() {
        return new JPAQueryFactory(firstEntityManager);
    }

    @Bean
    public JPAQueryFactory secondJpaQueryFactory() {
        return new JPAQueryFactory(secondEntityManager);
    }
}

 

9. 커스텀  QuerydslRepositorySupprt 클래스 구현 (상속으로)

Setter로 EntityManager를 지정합니다. SecondQuerydslRepositorySupprt 클래스도 마찬가지로 setter에 @PersistenceContext(unitName = "secondEntityManager") 라고 지정하여 구현하면 됩니다. 

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 FirstQuerydslRepositorySupport extends QuerydslRepositorySupport {

    /**
     * Creates a new {@link QuerydslRepositorySupport} instance for the given domain type.
     *
     * @param domainClass must not be {@literal null}.
     */
    public FirstQuerydslRepositorySupport(Class<?> domainClass) {
        super(domainClass);
    }

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

 

10. 실제 사용

위에서 구현한 커스텀 QuerydslRepositorySupprt을 상속받아 실세 QuertDSL을 사용하는 클래스를 구현합니다. JPAQueryFactory 객체 주입할 때 @Qualifier 어노테이션으로 특정 빈을 지정합니다

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.List;

import static com.example.db.jpa.entity.QTestInfo.testInfo;


@Repository
public class ExampleTestRepositorySupport extends QuerydslRepositorySupport {

    private final JPAQueryFactory queryFactory; // jPAQueryFactory 주입 받아 사용 

    public ExampleTestRepositorySupport(@Qualifier("firstJpaQueryFactory")JPAQueryFactory queryFactory) {
        super(TestInfo.class);
        this.queryFactory = queryFactory;
    }

    public List<TestInfo> findTestInfoListBySearch(long test_id) {
        return queryFactory
                .selectFrom(testInfo)
                .where(testInfo.testId.eq(test_id))
                .fetch();
    }
}

 

많은 도움이 되었던 곳

https://stackoverflow.com/questions/44975605/spring-boot-data-jpa-multiple-datasources-entitymanagerfactory-error

Spring boot data jpa multiple datasources entityManagerFactory error

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