close
프로필 배경
프로필 로고

하루하나

Programming/Springboot · 2017. 3. 29. fullscreen 넓게보기

[springboot] mybatis-multiple-datasource / db 여러개 사용

- Goal

SpringBoot에서 mybatis에 DB를 여러개 사용하기 위한 설정


- Time

30min


- Enviroment

java 1.8, springboot 1.5.2


- Tutorial

개발을 하다보면 데이터베이스를 여러개 운영 해야하는 경우가 생긴다. mybatis를 이용할 경우 설정하는 방법인데, 결론적으로는 마음에 들진 않는다. 


JPA로 설정하는법은 아래 포스팅을 참고

2017/03/24 - [Springboot] - [springboot] jpa multiple datasource


우선 기본적으로 application.properties에 DB정보를 작성한다. 편의를 위해 DB는 H2를 사용한다. 


# DB1

spring.db1.datasource.driverClassName=org.h2.Driver

spring.db1.datasource.url=jdbc:h2:~/db1

 

# DB2

spring.db2.datasource.driverClassName=org.h2.Driver

spring.db2.datasource.url=jdbc:h2:~/db2


그다음 JAVA로 DB를 설정해 준다. 


첫번째 DB 설정 

-  @MapperScan 에 scan할 package명을 작성 해 주고 sqlSessionFactoryBean.setMapperLocations에 mapper에 위치를 작성해 준다. 

- 첫번째 db에 경우 @Primary 어노테이션을 붙여 준다. 


package com.example.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.autoconfigure.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.context.annotation.Primary;


@Configuration

@MapperScan(value="com.example.dao1", sqlSessionFactoryRef="db1SqlSessionFactory")

public class Db1DatabaseConfig {

    @Bean(name = "db1DataSource")

    @Primary

    @ConfigurationProperties(prefix = "spring.db1.datasource")

    public DataSource db1DataSource() {

        return DataSourceBuilder.create().build();

    }

 

    @Bean(name = "db1SqlSessionFactory")

    @Primary

    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource db1DataSource, ApplicationContext applicationContext) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        sqlSessionFactoryBean.setDataSource(db1DataSource);

        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/db1/*.xml"));

        return sqlSessionFactoryBean.getObject();

    }

 

    @Bean(name = "db1SqlSessionTemplate")

    @Primary

    public SqlSessionTemplate db1SqlSessionTemplate(SqlSessionFactory db1SqlSessionFactory) throws Exception { 

        return new SqlSessionTemplate(db1SqlSessionFactory);

    }

}


두번째 DB설정

- 이름만 살짝살짝 다르고 @Primary 어노테이션이 빠진다. 


package com.example.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.autoconfigure.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;


@Configuration

@MapperScan(value="com.example.dao2", sqlSessionFactoryRef="db2SqlSessionFactory")

public class Db2DatabaseConfig {

@Bean(name = "db2DataSource")

    @ConfigurationProperties(prefix = "spring.db2.datasource")

    public DataSource db2DataSource() {

        return DataSourceBuilder.create().build();

    }

 

    @Bean(name = "db2SqlSessionFactory")

    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource db2DataSource, ApplicationContext applicationContext) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        sqlSessionFactoryBean.setDataSource(db2DataSource);

        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/db2/*.xml"));

        return sqlSessionFactoryBean.getObject();

    }

 

    @Bean(name = "db2SqlSessionTemplate")

    public SqlSessionTemplate db2SqlSessionTemplate(SqlSessionFactory db2SqlSessionFactory) throws Exception {

 

        return new SqlSessionTemplate(db2SqlSessionFactory);

    }

}


첫번째 DB mapper interface를 작성해 준다. 

- 조회 말고도 테스트를 위해 몇가지를 더 추가해 주었다. 

package com.example.dao1;


import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Select;


public interface Db1Mapper {

@Select("SELECT count(*) FROM MEMBER")

public String getCount(); 

@Insert("INSERT INTO MEMBER (NAME) VALUES (#{name})")

public void insertMember(String name);

public String getDb1Dual() throws Exception;

public void createTbl();

}


두번째 DB mapper insterface 작성

- 이름만 다르고 내용은 같다. 

package com.example.dao2;


import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Select;


public interface Db2Mapper {

@Select("SELECT count(*) FROM MEMBER")

public String getCount();


@Insert("INSERT INTO MEMBER (NAME) VALUES (#{name})")

public void insertMember(String name);

public String getDb2Dual() throws Exception;

public void createTbl();

}


서비스를 작성해 준다. 

- 마찬가지로 테스트를 위한 코드가 추가되어있다. 


package com.example.service;


import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.transaction.interceptor.TransactionAspectSupport;


import com.example.dao1.Db1Mapper;

import com.example.dao2.Db2Mapper;


@Service

public class DbService {

@Autowired

    Db1Mapper db1Mapper;

    @Autowired

    Db2Mapper db2Mapper;

 

    /* select dual */

    public String getDb1Dual() throws Exception{

        return db1Mapper.getDb1Dual();

    }

 

    /* select dual */

    public String getDb2Dual() throws Exception{

        return db2Mapper.getDb2Dual();

    }

    

    @Transactional

    public void transactionTest(boolean exceptionFlag ) {

    db1Mapper.insertMember("test");

    System.out.println("db1 count=" + db1Mapper.getCount());

   

    db1Mapper.insertMember("test");

    System.out.println("db2 count=" + db1Mapper.getCount());

   

    if( exceptionFlag ){

    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();    

    }

    }

}


controller를 작성. 서비스를 테스트코드로 작성해도 되지만 내용이 얼마 안되니 controller에서 테스트

package com.example.controller;


import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.ResponseBody;


import com.example.service.DbService;


@Controller

public class DbController {

    @Autowired

    DbService dbService;

     

    @RequestMapping("/")

    public @ResponseBody String root_test() throws Exception{  

        return "Hello World";

    }

 

    @RequestMapping("/db1")

    public @ResponseBody String db1() throws Exception{

        return dbService.getDb1Dual();

    }

     

    @RequestMapping("/db2")

    public @ResponseBody String db2() throws Exception{

        return dbService.getDb2Dual();

    }

    

    @RequestMapping("/transaction")

    public @ResponseBody String transaction() throws Exception{

   

    dbService.transactionTest(true);

   

    // rollback

    System.out.println("- rollaback");    

dbService.transactionTest(false);

    return "ok";

    }

}


main class에 기본데이터 셋팅부분을 추가

- 처음 실행시 테이블 생성, transaction 테스트용

package com.example;


import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;


import com.example.dao1.Db1Mapper;

import com.example.dao2.Db2Mapper;


@SpringBootApplication

public class SpringbootMybatisMultiDatasourceApplication implements CommandLineRunner{

@Autowired

private Db1Mapper db1Mapper;

@Autowired

private Db2Mapper db2Mapper;

public static void main(String[] args) {

    SpringApplication.run(SpringbootMybatisMultiDatasourceApplication.class, args);

}

@Override

    public void run(String... args) throws Exception {

        System.out.println("table create");

db1Mapper.createTbl();

        db2Mapper.createTbl();

        

        System.out.println("table search");

        System.out.println("db1 count=" + db1Mapper.getCount());

        System.out.println("db2 count=" + db2Mapper.getCount());

    }

}


프로그램을 실행한 후 

  • localhost:8080/db1
  • localhost:8080/db2
  • localhost:8080/transaction
을 실행하여 각 db에 데이터를 가져오고 transaction 처리가 제대로 되는지 확인합니다. 


원래 목적은 똑같은 테이블에 데이터를 다른 데이터베이스로 복사(백업)하는 기능을 만들기 위해서 시작한 작업인데, 
이런식이면 똑같은 쿼리와 똑같은 Mapper interface를 DB 수만큼 생성해야 한다는 문제가 생긴다. 
다른 방법을 찾아봐야 할듯 싶다. 

해당 소스는 아래 gitHub에서 확인 가능하다



저작자표시

'Programming > Springboot' 카테고리의 다른 글

[springboot] jar 실행시 profile 선택  (0) 2019.01.09
[springboot] springboot + spring security + jpa + thymeleaf  (1) 2017.06.15
[springboot] springboot starter maven dependency  (0) 2017.03.27
[springboot] jpa multiple datasource  (0) 2017.03.24
[springboot] h2 console 사용하기  (0) 2017.03.22
Programming/Springboot 관련 글
더 보기

티스토리툴바