springboot2+mybatis+maven 다중(멀티) datasource 설정.

보통 학습 시점 또는 토이 프로젝트를 진행하는 시점에 1개의 database를 기준으로 개발 환경을 설정하게 된다.

그러나 실무환경에서는 거의 대부분 1개의 프로젝트에 연결해야 하는?(연동해야 하는) database가 2개 이상일 때가

많다. 

 

하여 springboot+mybatis+maven 환경에서 mariadb와 ms-sql 이렇게 두 개의 database에 연동 설정했던 내용을

정리한다. 

 

1. 환경.

- springboot2

- myBatis

- maven

- mariadb

- MS-sql

 

2. Maven dependency설정.

<!-- DB -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>provided</scope>
</dependency>	
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>2.0.1</version>
</dependency>
<dependency>
  <groupId>org.bgee.log4jdbc-log4j2</groupId>
  <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
  <version>1.16</version>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-configuration-processor</artifactId>
  <optional>true</optional>
</dependency>
<!-- DB -->

 

3. db1DataSource 설정 class [mariadb용 datasource] 주석 처리한 부분 들을 잘 설정해주어야 하며, @Primary를 붙여

   주어야 정상 동작 한다.

[참고: @Primary 를 사용하여 동일한 유형의 Bean이 여러 개 있을 때 해당 Bean에 더 높은 우선권을 부여합니다.] 

package com.graykang.testpro.config;

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;

@Configuration
@MapperScan(basePackages="com.graykang.testpro.common.mapper.mariadb",sqlSessionFactoryRef="db1SqlSessionFactory")/*멀티DB사용시 mapper클래스파일 스켄용 basePackages를 DB별로 따로설정*/
@EnableTransactionManagement
public class MariadbDatabaseConfig {
	
	@Bean(name="db1DataSource")
	@Primary
	@ConfigurationProperties(prefix="spring.db1.datasource") //appliction.properties 참고.
	public DataSource db1DataSource() {
		return DataSourceBuilder.create().build();
		
	}
	
	@Bean(name="db1SqlSessionFactory")
	@Primary
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource db1DataSource, ApplicationContext applicationContext) throws Exception{
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(db1DataSource);
		sessionFactory.setMapperLocations(applicationContext.getResources("classpath:mybatis/mariadbmapper/*.xml")); //쿼리작성용 mapper.xml위치 설정.
		return sessionFactory.getObject();
	}
	
	@Bean(name="db1SqlSessionTemplate")
	@Primary
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db1sqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(db1sqlSessionFactory);
	}
	
    @Bean(name = "db1transactionManager")
	@Primary
    public PlatformTransactionManager transactionManager(@Qualifier("db1DataSource") DataSource db1DataSource) {
        return new DataSourceTransactionManager(db1DataSource);
    }
}

 

728x90

반응형

SMALL

4. db2DataSource 설정 class [MS-sql용 datasource] 주석 부분 위 3번과 동일 참고.

package com.graykang.testpro.config;

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;

@Configuration
@MapperScan(basePackages="com.graykang.testpro.common.mapper.mssql",sqlSessionFactoryRef="db2SqlSessionFactory")/*멀티DB사용시 mapper클래스파일 스켄용 basePackages를 DB별로 따로설정*/
@EnableTransactionManagement
public class MsSqlDatabaseConfig {
	
	@Bean(name="db2DataSource")
	@ConfigurationProperties(prefix="spring.db2.datasource")
	public DataSource db2DataSource() {
		return DataSourceBuilder.create().build();
		
	}
	
	@Bean(name="db2SqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource db2DataSource, ApplicationContext applicationContext) throws Exception{
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(db2DataSource);
		sessionFactory.setMapperLocations(applicationContext.getResources("classpath:mybatis/mssqlmapper/*.xml"));
		return sessionFactory.getObject();
	}
	
	@Bean(name="db2SqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db2sqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(db2sqlSessionFactory);
	}
	
    @Bean(name = "db2transactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("db2DataSource") DataSource db2DataSource) {
        return new DataSourceTransactionManager(db2DataSource);
    }
}

 

5. mariadb용 UserMapper.java 파일 설정.

(참고: mybatis용 src/main/resources/mybatis/mariadb/user_mapper.xml의 인터 페이스?)

package com.graykang.testpro.common.mapper.mariadb;

import java.util.List;

import com.graykang.testpro.admin.model.SearchParamUserVO;
import com.graykang.testpro.admin.model.UserVO;

public interface UserMapper {

	List<UserVO> selectUserInfoList();

}

 

6. MS-sql용 TempInfoMapper.java 파일 설정.

(참고: mybatis용 src/main/resources/mybatis/mssql/tempinfo_mapper.xml의 인터 페이스?)

package com.graykang.testpro.common.mapper.mssql;

import com.graykang.testpro.common.model.TempInfoVO;
import com.graykang.testpro.mmmgr.model.SearchParamVO;

public interface TempInfoMapper {

	TempInfoVO selectTempInfo(SearchParamVO searchParam);

}

 

7. mariadb용 src/main/resources/mybatis/mariadb/user_mapper.xml 설정.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.graykang.testpro.common.mapper.mariadb.UserMapper">
    <select id="selectUserInfoList" resultType="com.graykang.testpro.admin.model.UserVO">
        SELECT 
			user_code AS userCode,
			user_name AS username,
			user_no AS userno,
			user_status AS userStatus,
			user_idate AS userIdate,
			user_id AS userId,
			user_role AS userRole
       	FROM USER 
    </select>    
</mapper>

 

8. MS-sql용 src/main/resources/mybatis/mssql/tempinfo_mapper.xml 설정.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.graykang.testpro.common.mapper.mssql.TempInfoMapper">
    <select id="selectTempInfo" resultType="com.graykang.testpro.common.model.TempInfoVO">
		SELECT TOP 1 id AS msId FROM TempInfo with(nolock) WHERE id=#{no} AND status=1
    </select>
    
</mapper>

 

9. appliction.properties 설정.

##mariadb DB1
spring.db1.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.db1.datasource.jdbc-url=jdbc:log4jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC#no-log-set#spring.db1.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.db1.datasource.username=super
spring.db1.datasource.password=1234
spring.db1.datasource.maximum-pool-size=10
spring.db1.datasource.minimum-idle=5
spring.db1.datasource.connection-timeout=60000
spring.db1.datasource.idleTimeout=60000
spring.db1.datasource.max-lifetime=1200000
##MS-SQL DB2
spring.db2.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.db2.datasource.jdbc-url=jdbc:log4jdbc:sqlserver://127.0.0.1:1433;databasename=test2
spring.db2.datasource.username=super
spring.db2.datasource.password=1234
spring.db2.datasource.maximum-pool-size=10
spring.db2.datasource.minimum-idle=5
spring.db2.datasource.connection-timeout=60000
spring.db2.datasource.idleTimeout=20000
spring.db2.datasource.max-lifetime=30000

10. log4jdbc.log4j2.properties 설정.

#dev
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

log4jdbc.dump.sql.maxlinelength=0