AbstractRouteDatasource 사용 -2

강민욱·2023년 2월 20일
0

springboot

목록 보기
1/2

1. 목표

이전 AbstractRouteDatasource 사용 -1 에서는 모니터링쿼리의 수행을 위해 datasource의 동적 사용을 구현했다 헌데 JPA에도 동시에 사용할 수 있는지 궁금해서 이를 테스트해보려 한다.
https://lemontia.tistory.com/967 해당 사이트를 보니 @EnableJpaRepositories 어노테이션을 사용하면 할 수 있다고 한다.

2. RoutingConfiguration

@Configuration과 함께 @EnableJpaRepositories 어노테이션을 사용해야 되므로 AbstractRouteDatasource 사용 -1 에서 RoutingTestConfiguration 가 변해야 한다.
위 참조 사이트를 보면 DB종류에 따라 RoutingTestConfiguration 파일이 분리돼있다. 그래서 해당 파일을 쪼개려고 보니

import com.example.abstractroutingdatasource.ClientDataSourceRouter;
import com.example.abstractroutingdatasource.ClientDatasource;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class RoutingConfiguration {

    @Autowired
    protected JpaProperties jpaProperties;

    @Autowired
    protected HibernateProperties hibernateProperties;
    @Bean
    ClientDatasource clientDatasource(){
        return new ClientDatasource(getclientDatasSource());
    }

    @Bean
    @Primary
    public DataSource getclientDatasSource() {
        ClientDataSourceRouter clientDataSourceRouter = new ClientDataSourceRouter();
        clientDataSourceRouter.initDatasource(agensDatasource(), mysqlDatasource());
        return clientDataSourceRouter;
    }
    @Bean
    @ConfigurationProperties("datasource.agens")
    public DataSourceProperties agensDatasourProperties(){
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("datasource.mysql")
    public DataSourceProperties mysqlDatasourProperties(){
        return new DataSourceProperties();
    }

    @Bean
    public DataSource agensDatasource() {
        return agensDatasourProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    public DataSource mysqlDatasource() {
        return mysqlDatasourProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }
}

위의 코드가 중복적으로 선언된다. 그래서 extends를 사용하기로 했다. 부모클래스인 RoutingConfiguration, 각각의 DB를 위한 AgensRoutingConfiguration (agens), MysqlRoutingConfiguration(Mysql) 이렇게 3개로 나누기로 했다.

3. AgensRoutingConfiguration

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
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.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import java.util.Map;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.example.abstractroutingdatasource.repository.agens"
        , entityManagerFactoryRef = "agensEntityManagerFactory"
        , transactionManagerRef = "agensTransactionManager"
)
public class AgensRoutingConfiguration extends RoutingConfiguration{

    @Primary
    @Bean(name = "agensEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean agensEntityManagerFactory(EntityManagerFactoryBuilder builder){
        Map<String, Object> properties = hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
        return builder
                .dataSource(agensDatasource())
                .packages("com.example.abstractroutingdatasource.entity")
                .persistenceUnit("agensEntityManager")
                .properties(properties)
                .build();
    }

    @Bean(name = "agensTransactionManager")
    @Primary
    public PlatformTransactionManager agensTransactionManager(@Qualifier(value = "agensEntityManagerFactory") EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }
}

코드를 보면

@Configuration
@EnableJpaRepositories(
        basePackages = "com.example.abstractroutingdatasource.repository.agens"
        , entityManagerFactoryRef = "agensEntityManagerFactory"
        , transactionManagerRef = "agensTransactionManager"
)
public class AgensRoutingConfiguration extends RoutingConfiguration{

@EnableJpaRepositories가 추가 됐는데 basePackges는 해당 클래스를 사용할 repository를 지정하고 밑에 @bean으로 선언한 entityManagerFactoryRef, transactionManagerRef를 지정했다.
그리고 2.에서 선언한 RoutingConfiguration를 상속했다. 

@Primary
@Bean(name = "agensEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean agensEntityManagerFactory(EntityManagerFactoryBuilder builder){
    Map<String, Object> properties = hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    return builder
            .dataSource(agensDatasource())
            .packages("com.example.abstractroutingdatasource.entity")
            .persistenceUnit("agensEntityManager")
            .properties(properties)
            .build();
}

dataSource(agensDatasource()) 메소드에 해당하는 datasource를 지정했다.
packages("com.example.abstractroutingdatasource.entity") 위 설정과 다르게 repository가 아니라 entity 패키지명을 넣어야 한다. 잘못넣게 되면 entitiy를 찾을 수 없다는 에러가 발생한다.

4. MysqlRoutingConfiguration

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import java.util.Map;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.example.abstractroutingdatasource.repository.mysql"
        , entityManagerFactoryRef = "mysqlEntityManagerFactory"
        , transactionManagerRef = "mysqlTransactionManager"
)
public class MysqlRoutingConfiguration extends RoutingConfiguration{

    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder){
        Map<String, Object> properties = hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
        return builder
                .dataSource(mysqlDatasource())
                .packages("com.example.abstractroutingdatasource.entity")
                .persistenceUnit("mysqlEntityManager")
                .properties(properties)
                .build();
    }

    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier(value = "mysqlEntityManagerFactory") EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

}

AgensRoutingConfiguration과 동일한 방식이고 datasource와 repository, entity 패키지만 다르므로 따로 설명은 생략

5. MemberAgensRepository

import com.example.abstractroutingdatasource.entity.Member;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MemberAgensRepository extends JpaRepository<Member, String> {

}

com.example.abstractroutingdatasource.repository.agens
com.example.abstractroutingdatasource.repository.mysql
이런식으로 package명으로 구분해서 딱히 설명할게 없음
Member entity클래스 역시 생략

6. MainController

import com.example.abstractroutingdatasource.BoardService;
import com.example.abstractroutingdatasource.ClientDatasource;
import com.example.abstractroutingdatasource.config.ClientDatabase;
import com.example.abstractroutingdatasource.entity.Member;
import com.example.abstractroutingdatasource.repository.agens.MemberAgensRepository;
import com.example.abstractroutingdatasource.repository.mysql.MemberMysqlRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.sql.SQLException;

@RestController
@RequiredArgsConstructor
@RequestMapping("/api/v1")
public class MainController {

    private final BoardService boardService;

    private final MemberAgensRepository memberAgensRepository;

    private final MemberMysqlRepository memberMysqlRepository;

    @GetMapping("/datasource/{dbName}")
    public ResponseEntity<?> getData(@PathVariable String dbName) throws SQLException {
        Object result = null;
        DataSource dataSource = null;
        if("agens".equals(dbName)){
            //System.out.println("MainController getData dbName : "+dbName);
            dataSource = ClientDatasource.getDatasource(ClientDatabase.AGENS);
            result = boardService.getData(dataSource);

        }else{
            dataSource = ClientDatasource.getDatasource(ClientDatabase.MYSQL);
            result = boardService.getData(dataSource);
        }

        return ResponseEntity.ok(result);
    }

@PostMapping("/jpa/{dbName}")
public ResponseEntity<?> creatMember(@PathVariable String dbName) throws SQLException {

    Member member = new Member();


    Member result = null;
    if("agens".equals(dbName)){
        member.setId("bitnine-agens-user");
        member.setUserName("비트나인 agens");
        member.setAddr("서울특별시 강남구 테헤란로 516 정헌빌딩 4층");
        result = memberAgensRepository.save(member);
        return ResponseEntity.ok(result);

    }else{
        member.setId("bitnine-mysql-user");
        member.setUserName("비트나인 mysql");
        member.setAddr("서울특별시 강남구 테헤란로 516 정헌빌딩 4층");
        result = memberMysqlRepository.save(member);
        return ResponseEntity.ok(result);
    }

}

@PathVariable dbName에 따라 다른 repository.save를 실행하는 creatMember 메소드가 추가됐다.

7. 실행

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.
at org.postgresql.Driver.notImplemented(Driver.java:699) ~[postgresql-42.3.6.jar:42.3.6]
at org.postgresql.jdbc.PgConnection.createClob(PgConnection.java:1347) ~[postgresql-42.3.6.jar:42.3.6]
에러발생 이유는 간단

spring:
  jpa:
    database: mysql

application.yml파일에서 spring.jpa.database를 mysql로 설정했는데 postgres entity테이블도 만들라고 하니 에러가 발생 반대로 postgresql로 설정하면

spring:
  jpa:
    database: postgresql

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: mysqlEntityManager] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.SQLGrammarException: Unable to build DatabaseInformation
mysql측에서 에러를 토해낸다.

해결방법은 

spring:
  jpa:
    #database: mysql
    #database: postgresql

주석처리 하면 된다.
이러고 실행하면
2023-02-17 17:10:51.737 INFO 5968 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL10Dialect
Hibernate: create table tb_member (id varchar(255) not null, addr varchar(255), user_name varchar(255), primary key (id))
2023-02-17 17:10:52.149 INFO 5968 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'agensEntityManager'
등록된 agensEntityManager가 작동하고 hibernate가 PostgreSQL10Dialect를 이용하여 tb_member를 생성하는것을 알 수 있다.

2023-02-17 17:10:52.337 INFO 5968 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect
Hibernate: create table tb_member (id varchar(255) not null, addr varchar(255), user_name varchar(255), primary key (id)) engine=InnoDB
2023-02-17 17:10:52.402 INFO 5968 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'mysqlEntityManager'
mysqlEntityManager도 마찬가지

8. 결과

(1) agens

(2) mysql

각각의 DB에 이상없이 등록되는걸 알 수 있다.

code
github - AbstractRoutingDatasource-with-JPA

reference
https://lemontia.tistory.com/967/

profile
강민욱
백엔드 개발자
다음 포스트

Transaction Propagation and Isolation in Spring @Transactional

0개의 댓글