다중 DB 연결을 위한 dataSource 설정(AbstractRoutingDataSource)

properties 파일에 저장한 각 부서의 DB 접속 정보를 가지고 필요에 따라 접속할 수 있는 구성이 필요했다.

처음에는 구글링해봤을 때 각 DB별로 SqlSessionFactory와 SqlSessionTemplate을 @Bean 생성하여 우선 적용하는 DB에 @Primary 어노테이션을 붙여주는 방식이 가장 많이 나와서 활용해보려고 했으나 이렇게 하면 DB마다 config class와 mapper를 모두 작성해주어야 하는데 나의 경우는 접속 DB 목록이 최소 30개였기 때문에 이런 구조는 매우 비효율적이었다. 그리고 @Primary로 사용하는 master DB가 있고 그 외 slave DB가 존재하는 게 아니라 여러 개의 DB 중 사용자의 선택에 따라 동적으로 접속하는 것이었기 때문에 본질적으로 다르다고 생각했다.

그래서 좀 더 검색하다보니 AbstractRoutingDataSource 를 발견하게 되었다. 조회 키(lookup key)를 기반으로 다양한 대상 dataSource 중 하나를 호출하는 추상 클래스인데 딱 필요한 기능이었다.

바로 적용해보았다.

 

---

패키지 구성은 아래와 같다.

 

 

1. DatabaseEnum.java 클래스에 key로 사용할 DB 목록을 정의해준다. enum 열거체는 대문자로 작성해준다.

public enum DatabaseEnum {
    slave1,
    slave2,
    slave3;
}

 

 

2. RoutingDatabaseConfig.java 클래스에 DataSource, SqlSessionFactory, SqlSessionTemplate 코드를 작성해준다.

@Configuration
@MapperScan(value ="test.mapper", sqlSessionFactoryRef="routeSqlSessionFactory")
@EnableTransactionManagement
public class RoutingDatabaseConfig {
    
    @Bean(name="slave1Datasource")
    @ConfigurationProperties("slave1.datasource")
    public DataSource slave1Datasource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name="slave2Datasource")
    @ConfigurationProperties("slave2.datasource")
    public DataSource slave2Datasource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name="slave3Datasource")
    @ConfigurationProperties("slave3.datasource")
    public DataSource slave3Datasource() {
        return DataSourceBuilder.create().build();
    }
    
    
    @Bean("routeDataSource")
    public DataSource clientDatasource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DatabaseEnum.slave1, slave1Datasource());
        targetDataSources.put(DatabaseEnum.slave2, slave2Datasource());
        targetDataSources.put(DatabaseEnum.slave3, slave3Datasource());

        RoutingDataSource dataSourceRouter = new RoutingDataSource();
        dataSourceRouter.setTargetDataSources(targetDataSources);

        return dataSourceRouter;
    }

    
    @Bean(name = "routeSqlSessionFactory")
    public SqlSessionFactory routeSqlSessionFactory(@Qualifier("routeDataSource") DataSource routeDataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean =new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(routeDataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:/mybatis/mapper/test_sql.xml"));
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:/mybatis/mybatis-config.xml"));

        return sqlSessionFactoryBean.getObject();
    }
    
    @Bean(name = "routeSqlSessionTemplate")
    public SqlSessionTemplate routeSqlSessionTemplate(@Qualifier("routeSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

 

 

3. RoutingDatabaseContextHolder.java 클래스에는 AbstractRoutingDataSource에 사용될 lookup key를 ThreadLocal에 저장하는 코드를 작성해준다.

import org.springframework.util.Assert;

public class RoutingDatabaseContextHolder {
    private static ThreadLocal<DatabaseEnum> CONTEXT = new ThreadLocal<>();
    
    public static void set(DatabaseEnum clientDatabase) {
        Assert.notNull(clientDatabase, "RoutingDatabase cannot be null");
        CONTEXT.set(clientDatabase);
    }
    
    public static DatabaseEnum getclientDatabase() {
        return CONTEXT.get();
    }
    
    public static void clear() {
        CONTEXT.remove();
    }
}

 

 

4. RoutingDataSource.java 클래스는 AbstractRoutingDataSource 를 상속받은 클래스이다. RoutingDatabaseContextHolder 에서 타겟 DataSource를 가져온다.

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return RoutingDatabaseContextHolder.getclientDatabase();
    }
}

 

 

이후 service단의 method를 작성하여 테스트해보았다.

 

public List<DataVO> retrieveList(DatabaseEnum db, DataVO dataVO) {
    RoutingDatabaseContextHolder.set(db);
    List<DataVO> list = new ArrayList<DataVO>();
    
    if (db == DatabaseEnum.slave1) {
        list = testMapper.retrieveListMysql(dataVO);
    } else if (db == DatabaseEnum.slave2) {
        list = testMapper.retrieveListOracle(dataVO);
    } else if (db == DatabaseEnum.slave3) {
        list = testMapper.retrieveListPostgre(dataVO);
    } 
    RoutingDatabaseContextHolder.clear();

    System.out.println("retrieveList :::" + list);

    return list;
}

 

 

정상적으로 조회해온다.

눈물..

 

 

 

참고

https://maximsungmo.github.io/spring/abstract-routing-datasource/

[Spring] DB 동적 라우팅을 위한 AbstractRoutingDataSource 설정