[Spring boot] 데이터베이스 멀티 커넥션

한프로젝트에 postgressql과 oracle 데이터베이스를 동시에 쓰고싶을때 사용하는 방법입니다.

 

application-properties

#Oracle
spring.oracle.datasource.hikari.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.oracle.datasource.hikari.jdbc-url=jdbc:log4jdbc:oracle:thin:@ip주소:port번호:데이터베이스이름
spring.oracle.datasource.hikari.username=아이디
spring.oracle.datasource.hikari.password=패스워드
spring.oracle.datasource.hikari.maximum-pool-size=50

# postgresql
spring.postgres.datasource.hikari.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.postgres.datasource.hikari.jdbc-url=jdbc:log4jdbc:postgresql://ip주소:port번호/데이터베이스이름
spring.postgres.datasource.hikari.username=아이디
spring.postgres.datasource.hikari.password=패스워드
spring.postgres.datasource.hikari.maximum-pool-size=10

 

OracleConnMapper.java

package com.hy.orderSystem.config.oracle;

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 "";

}

 

OracleDatabaseConfig.java

package com.hy.orderSystem.config.oracle;

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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.hy.orderSystem.common.Constants;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@MapperScan(value = "dao 파일이있는 패키지이름", annotationClass = OracleConnMapper.class, sqlSessionFactoryRef = "oracleSqlSessionFactory")
@EnableTransactionManagement
public class OracleDatabaseConfig {
	
	@Bean(name = "oracleDataSource", destroyMethod = "close")
	@Primary
	@ConfigurationProperties(prefix = "spring.oracle.datasource.hikari")
	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"));
		sqlSessionFactoryBean.setTypeAliasesPackage(Constants.APP_TYPE_ALIASES_PACKAGE);
		return sqlSessionFactoryBean.getObject();
	}

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

 

PostgresConnMapper

package com.hy.orderSystem.config.postgress;

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 PostgresConnMapper {
	String value() default "";

}

 

package com.hy.orderSystem.config.postgress;

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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.hy.orderSystem.common.Constants;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@MapperScan(value = "dao 파일이있는 패키지이름", annotationClass = PostgresConnMapper.class, sqlSessionFactoryRef = "postgresSqlSessionFactory")
@EnableTransactionManagement
public class PostgresDatabaseConfig {
	
	@Bean(name = "postgresDataSource", destroyMethod = "close")
	@ConfigurationProperties(prefix = "spring.postgres.datasource.hikari")
	public DataSource postgresDataSource() {
		return DataSourceBuilder.create().type(HikariDataSource.class).build();
	}

	@Bean(name = "postgresSqlSessionFactory")
	public SqlSessionFactory postgresSqlSessionFactory(@Qualifier("postgresDataSource") DataSource postgresDataSource,
			ApplicationContext applicationContext) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(postgresDataSource);
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath*:mapper/postgress/*.xml"));
		sqlSessionFactoryBean.setTypeAliasesPackage(Constants.APP_TYPE_ALIASES_PACKAGE);
		return sqlSessionFactoryBean.getObject();
	}

	@Bean(name = "postgresSqlSessionTemplate")
	public SqlSessionTemplate postgresSqlSessionTemplate(SqlSessionFactory postgresSqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(postgresSqlSessionFactory);
	}
	
	@Bean(name = "postgresTm")
	public PlatformTransactionManager postgresTransactionManager() {
		return new DataSourceTransactionManager(postgresDataSource());
	}
	
}

 

classpath -> resource를 나타내므로 xml의 경로를 적어주면 됩니다.

 

해당 데이터베이스 멀티커넥션을 하였을경우 @transactional을 사용할때는 이름을 지어서 사용해야 합니다.

@Transactional로 사용하면 동일한 이름의 transactional을 사용한다는 오류가 발생이 됩니다.

oracle일경우에는 @Transactional(value = "oracleTm")

postgres일경우에는 @Transactional(value = "postgresTm")