spring boot multi data source(jpa query dsl로 DB 다중 연결)

업무 중에 중앙 관제 서버에 연결된 Edge서버에 데이터를 가져와야 하는 이슈가 있었다.

1(관제):n(Edge)구조에 아키텍처 였는데, 이렇게 한 이유는 각각에 Edge서버들이 중앙서버와 무관하게 스탠드 얼론으로 구동되기 원하는 요구 사항이 있어서 이렇게 설계 하게 되었다.

이런 구조는 정말 데이터에 동기화 부터해서 신경 써야할 포인트가 많아 머리 아픈 구조다...

결국 이 아키텍처에 해결 방법으로 kafka connect를 사용했지만 multi data sources도 고려 했던 방법으로 테스트를 해봤던 것을 기록으로 남기려고 합니다.

 

• 폴더 구조

 

• gradle 설정

plugins {
    id 'org.springframework.boot' version '2.4.2'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
    id 'java'
    id 'war'
    id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
}

group = 'com.multi'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
    mavenCentral()
}

// querydsl 사용할 경로 지정합니다. 현재 지정한 부분은 .gitignore에 포함되므로 git에 올라가지 않습니다.
def querydslDir = "$buildDir/generated/'querydsl'"

// JPA 사용여부 및 사용 경로 설정
querydsl {
    jpa = true
    querydslSourcesDir = querydslDir
}

// build시 사용할 sourceSet 추가 설정
sourceSets {
    main.java.srcDir querydslDir
}


// querydsl 컴파일 시 사용할 옵션 설정
compileQuerydsl {
    options.annotationProcessorPath = configurations.querydsl
}

// querydsl이 compileClassPath를 상속하도록 설정
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
    querydsl.extendsFrom compileClasspath
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'
    annotationProcessor 'org.projectlombok:lombok'
    providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'

    // db log view
    implementation 'org.bgee.log4jdbc-log4j2:log4jdbc-log4j2-jdbc4.1:1.16'
    implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.7.1'

    // querydsl
    implementation 'com.querydsl:querydsl-jpa'
    implementation 'com.querydsl:querydsl-apt'
}

bootWar {
    archiveBaseName = 'ROOT'
    archiveFileName = 'ROOT.war'
    archiveVersion = '0.0.0'
}

tasks.named('test') {
    useJUnitPlatform()
}

 

 

• application.yml

--- #local 환경
spring:
  config:
    activate:
      on-profile: local
  imagesRoot: /files/
  fileRoot: C:/data/webFiles/
data1:
  datasource:
    url: jdbc:mariadb://192.168.56.102:20810/test?characterEncoding=UTF-8&serverTimezone=UTC
    username: tester
    password: 1234
data2:
  datasource:
    url: jdbc:mariadb://192.168.56.104:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
    username: tester
    password: 1234
data3:
  datasource:
    url: jdbc:mariadb://192.168.56.105:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
    username: tester
    password: 1234
decorator:
  datasource:
    p6spy:
      enable-logging: true

server:
  port: 9081

 

• BaseEntity

package com.multi.data.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Getter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.EntityListeners;
import javax.persistence.MappedSuperclass;
import java.time.LocalDateTime;

@Getter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public class BaseEntity {

    @CreatedDate
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Seoul")
    private LocalDateTime createdDate;

    @LastModifiedDate
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Seoul")
    private LocalDateTime modifiedDate;
}

 

• ConnectLog Entity
  • entity는 공통으로 사용할 수 있습니다. DataConfig에 packges() 설정을 변경해 주면 됩니다.

package com.multi.data.entity;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;


@Entity
@Data
public class ConnectLog extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String status;
}

 

• data1 config

package com.multi.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;
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.data.jpa.repository.config.EnableJpaRepositories;
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.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "data1EntityManagerFactory",
        transactionManagerRef = "data1TransactionManager",
        basePackages = {"com.multi.data.repository.repo1"}
)
public class Data1Config {

    @Autowired
    private Environment env;

    @Primary
    @Bean
    @ConfigurationProperties("data1.datasource")
    public DataSourceProperties data1DataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean
    @ConfigurationProperties("data1.datasource.configuration")
    public DataSource data1DataSource(@Qualifier("data1DataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean data1EntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("data1DataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.multi.data.entity")
                .persistenceUnit("data1EntityManager")
                .properties(jpaProperties())
                .build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager data1TransactionManager(
            @Qualifier("data1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> jpaProperties() {
        Map<String, Object>  properties = new HashMap<String, Object>();

        final String rootPath = "spring.jpa.hibernate";

        properties.put("hibernate.hbm2ddl.auto", env.getProperty(rootPath + ".ddl-auto"));
        properties.put("hibernate.dialect", env.getProperty(rootPath + ".dialect"));
        properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()); // 테이블 물리 네이밍 언더스코어 설정
        properties.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName()); // 테이블 논리 네이밍 언더스코어 설정
//        properties.put("hibernate.show_sql", env.getProperty(rootPath + ".show_sql"));
//        properties.put("hibernate.format_sql", env.getProperty(rootPath + ".format_sql"));
//        properties.put("hibernate.enable_lazy_load_no_trans",
//                env.getProperty(rootPath + ".enable_lazy_load_no_trans"));

        return properties;
    }
}

 

• data2 config
  • data2, data3...n 부터 다른점은 @Primary 어노테이션이 없는 것 외에는 data1라고 되어 있는 부분만 구분되게 변경 해서 사용하면 된다. 
  • @Primary는 @Bean우선 순위를 지정해 주어 메인 data source에 지정해주면 됩니다.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "data2EntityManagerFactory",
        transactionManagerRef = "data2TransactionManager",
        basePackages = {"com.multi.data.repository.repo2"}
)
public class Data2Config {

    @Autowired
    private Environment env;

    @Bean
    @ConfigurationProperties("data2.datasource")
    public DataSourceProperties data2DataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("data2.datasource.configuration")
    public DataSource data2DataSource(@Qualifier("data2DataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean data2EntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("data2DataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.multi.data.entity")
                .persistenceUnit("data2EntityManager")
                .properties(jpaProperties())
                .build();
    }

    @Bean
    public PlatformTransactionManager data2TransactionManager(
            @Qualifier("data2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> jpaProperties() {
        Map<String, Object> properties = new HashMap<String, Object>();

        final String rootPath = "spring.jpa.hibernate";

        properties.put("hibernate.hbm2ddl.auto", env.getProperty(rootPath + ".ddl-auto"));
        properties.put("hibernate.dialect", env.getProperty(rootPath + ".dialect"));
        properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()); // 테이블 물리 네이밍 언더스코어 설정
        properties.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName()); // 테이블 논리 네이밍 언더스코어 설정
//        properties.put("hibernate.show_sql", env.getProperty(rootPath + ".show_sql"));
//        properties.put("hibernate.format_sql", env.getProperty(rootPath + ".format_sql"));
//        properties.put("hibernate.enable_lazy_load_no_trans",
//                env.getProperty(rootPath + ".enable_lazy_load_no_trans"));

        return properties;
    }
}

 

• SearchConnectLog1Repository 

package com.multi.data.repository.repo1.search;

import com.multi.data.entity.ConnectLog;
import com.multi.data.entity.QConnectLog;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

public class SearchConnectLog1RepositoryImpl extends QuerydslRepositorySupport implements SearchConnectLog1Repository {

    public SearchConnectLog1RepositoryImpl() {
        super(ConnectLog.class);
    }

    private EntityManager em;
    private JPAQueryFactory queryFactory;

    @Override
    @PersistenceContext(unitName="data1EntityManager")
    public void setEntityManager(EntityManager entityManager) {
        super.setEntityManager(entityManager);
        em = entityManager;
        this.queryFactory = new JPAQueryFactory(entityManager);
    }

    @Override
    public List<ConnectLog> searchConnectLog(String status) {
        QConnectLog connectLog = QConnectLog.connectLog;
        return queryFactory.selectFrom(connectLog).where(connectLog.status.eq(status)).fetch();
    }
}

 

• SearchConnectLog1RepositoryImpl
  • 여기서 주목할 점은 @PersistenceContext(unitName="data1EntityManager") 설정 부분이며, 각 DataCfig에서 설정한 persistenceUnit("data1EntityManager") 설정과 사용할 repo를 일치 시켜주면 됩니다.

package com.multi.data.repository.repo1.search;

import com.multi.data.entity.ConnectLog;
import com.multi.data.entity.QConnectLog;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

public class SearchConnectLog1RepositoryImpl extends QuerydslRepositorySupport implements SearchConnectLog1Repository {

    public SearchConnectLog1RepositoryImpl() {
        super(ConnectLog.class);
    }

    private EntityManager em;
    private JPAQueryFactory queryFactory;

    @Override
    @PersistenceContext(unitName="data1EntityManager")
    public void setEntityManager(EntityManager entityManager) {
        super.setEntityManager(entityManager);
        em = entityManager;
        this.queryFactory = new JPAQueryFactory(entityManager);
    }

    @Override
    public List<ConnectLog> searchConnectLog(String status) {
        QConnectLog connectLog = QConnectLog.connectLog;
        return queryFactory.selectFrom(connectLog).where(connectLog.status.eq(status)).fetch();
    }
}

 

• ConnectLog1Repository

package com.multi.data.repository.repo1;

import com.multi.data.entity.ConnectLog;
import com.multi.data.repository.repo1.search.SearchConnectLog1Repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ConnectLog1Repository extends JpaRepository<ConnectLog, Long>, SearchConnectLog1Repository {
}

 

• 나머지 2, 3 Repository리 설정도 동일하게 패키지를 repo1과 구분해서 만들고 겹치지 않게 작명하여 설정해 주시면 됩니다.

 

• Test Code

package com.multi;

import com.multi.data.entity.ConnectLog;
import com.multi.data.repository.repo1.ConnectLog1Repository;
import com.multi.data.repository.repo2.ConnectLog2Repository;
import com.multi.data.repository.repo3.ConnectLog3Repository;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Commit;

import java.util.List;

import static org.assertj.core.api.Assertions.*;

@SpringBootTest
public class MultiConnectServerApplicationTests {

    @Autowired
    ConnectLog1Repository connectLog1Repository;

    @Autowired
    ConnectLog2Repository connectLog2Repository;

    @Autowired
    ConnectLog3Repository connectLog3Repository;

    Logger log = LoggerFactory.getLogger(this.getClass());

    @Test
    @Commit
    void multiConnectDataSave() {
        ConnectLog connectLog1 = new ConnectLog();
        connectLog1.setStatus("test11111");
        connectLog1Repository.save(connectLog1);

        ConnectLog connectLog2 = new ConnectLog();
        connectLog2.setStatus("test22222");
        connectLog2Repository.save(connectLog2);

        ConnectLog connectLog3 = new ConnectLog();
        connectLog3.setStatus("test33333");
        connectLog3Repository.save(connectLog3);
    }

    @Test
    void multiConnectDataSelect() {
        List<ConnectLog> data1 = connectLog1Repository.searchConnectLog("test11111");
        List<ConnectLog> data2 = connectLog2Repository.searchConnectLog("test11111");
        List<ConnectLog> data3 = connectLog3Repository.searchConnectLog("test33333");
        log.debug("===================data1: {}", data1);
        log.debug("===================data2: {}", data2);
        log.debug("===================data3: {}", data3);
        assertThat(data1.size()).isNotEqualTo(0);
        assertThat(data2.size()).isEqualTo(0);
        assertThat(data3.size()).isNotEqualTo(0);
    }
}

 

• multiConnectDataSave 결과 

 

• multiConnectDataSelect 테스트 결과.