Spring Boot Multiple Database Configuration + Mybatis +HikariCP, MySQL, Oracle, Using Mapper annotation

Spring Boot Multiple Database Configuration + Mybatis +HikariCP, MySQL, Oracle, Using Mapper Annotation

스프링 부트에서 멀티 Database 연결 설정 Mybatis + HikariCp, MySql, Oracle, 어노테이션 방식

 

Spring Boot version : 2.1.1

Gradle

spring boot 2.0.0 이상부터는 DBCP는 HikariCP가 기본입니다.

 

스프링 부트, Mybatis 환경에서 Oracle과 Mysql로 멀티 데이터베이스 연동하는 방법을 알아보겠습니다.

 

우선 필요한 라이브러리의 의존성 주입을 합니다.

dependencies {
	compile('org.springframework.boot:spring-boot-starter')
	
	compile('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2')
	compile('org.springframework.boot:spring-boot-starter-jdbc')
	compile('mysql:mysql-connector-java')
}

Oracle은 별도의 커넥터가 필요없지만 Mysql은 의존성 주입해주어야 합니다.

이제 application.properties에 DB 관련 정보를 설정해줍니다.

 

  - src/main/resource/application.properties

#Oracle
spring.oracle.datasource.jdbc-url=jdbc:oracle:thin:@IP:PORT:스키마
spring.oracle.datasource.username=ID
spring.oracle.datasource.password=PASSWORD
spring.oracle.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.oracle.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.oracle.datasource.hikari.maximum-pool-size=10
spring.oracle.datasource.hikari.connection-test-query=SELECT 1 FROM SYS.DUAL
spring.oracle.datasource.hikari.minimum-idle=5
spring.oracle.datasource.hikari.connection-timeout=100000

#Mysql
spring.mysql.datasource.jdbc-url=jdbc:mysql://IP:PORT/스키마?characterEncoding=UTF-8&serverTimezone=UTC
spring.mysql.datasource.username=ID
spring.mysql.datasource.password=PASSWORD
spring.mysql.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.mysql.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.mysql.datasource.hikari.maximum-pool-size=10
spring.mysql.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
spring.mysql.datasource.hikari.minimum-idle=5
spring.mysql.datasource.hikari.connection-timeout=100000

Oracle은 :스키마 이지만 MySql은 /스키마에 주의하세요

 

이제 각각의 DB에 접속할 어노테이션을 작성합니다.

저의 경우 Oracle은 OracleConnMapper,

Mysql은 MysqlConnMapper 명으로 설정하였습니다.

 

 - MysqlConnMapper.java

import java.lang.annotation.Documented;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import org.springframework.stereotype.Component;

@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Component
public @interface MysqlConnMapper {
	String value() default "";
}

  - OracleConnMapper.java

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.springframework.stereotype.Component;

@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Component
public @interface OracleConnMapper {
	String value() default "";
}

이제 위에 설정한 DB 연결정보와 Mapper를 활용하여 Mybatis 연동 설정을 합니다.

 

  - OracleDatabaseConfig.java

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(value="최상위 패키지 경로", annotationClass=OracleConnMapper.class, sqlSessionFactoryRef="oracleSqlSessionFactory")
@EnableTransactionManagement
public class OracleDatabaseConfig {

	@Bean(name = "oracleDataSource", destroyMethod = "close")
	@Primary
	@ConfigurationProperties(prefix = "spring.oracle.datasource")
	public DataSource oracleDataSource() {
	    return DataSourceBuilder.create().type(HikariDataSource.class).build();
	}
	
    @Bean(name = "oracleSqlSessionFactory")
    @Primary
    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource, ApplicationContext applicationContext) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(oracleDataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath*:mapper/oracle/*.xml"));
        return sqlSessionFactoryBean.getObject();
    } 

    @Bean(name = "oracleSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate oracleSqlSessionTemplate(SqlSessionFactory oracleSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(oracleSqlSessionFactory);
    }
}

 

  - MysqlDatabaseConfig.java

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(value="최상위 패키지 경로", annotationClass=MysqlConnMapper.class, sqlSessionFactoryRef="mysqlSqlSessionFactory")
@EnableTransactionManagement
public class MysqlDatabaseConfig {

	@Bean(name = "mysqlDataSource", destroyMethod = "close")
	@ConfigurationProperties(prefix = "spring.mysql.datasource")
	public DataSource mysqlDataSource() {
	    return DataSourceBuilder.create().type(HikariDataSource.class).build();
	}
	
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDataSource, ApplicationContext applicationContext) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(mysqlDataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath*:mapper/mysql/*.xml"));
        return sqlSessionFactoryBean.getObject();
    } 

    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(SqlSessionFactory mysqlSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(mysqlSqlSessionFactory);
    }
}

주로 사용할 DB에는 Primary 어노테이션을 추가했고,

MapperScan 시 어노테이션 클래스 설정으로 어노테이션에 따른 DB 연결 설정을 하였습니다.

이제 Mapper에서 @MysqlConnMapper를 쓰면 mysql로, @OracleConnMapper 를 쓰면 Oracle로 연결이 됩니다.

 

  - MysqlMapper.java

@MysqlConnMapper
public interface MysqlMapper{
	int getCountMysql();
}

  - OracleMapper.java

@OracleConnMapper
public interface OracleMapper{
	int getCountOracle();
}