MapperScan을 통한 Spring boot DB 연결 - Oracle

작업자의 작업 환경

- Intellij

- Oracle 11g

- Windows 10

 

 

File > New Project > Spring Initializer 선택

Project Type은 Gradle Project로 설정합니다.

Spring Web, MyBatis Framework, Oracle Driver 3개를 선택하고 프로젝트 셋팅을 해줍니다.

필요에 따라 dependencies를 추가해줍니다.

 

    compile 'org.springframework:spring-web:5.2.0.RELEASE'
    compile group: 'org.springframework', name: 'spring-webmvc', version: '5.2.0.RELEASE'

    compile 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'

    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'mysql:mysql-connector-java'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }

    //boot 자동빌드
    compile 'org.springframework.boot:spring-boot-devtools'


    //mybatis
    compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter'
    compile 'org.springframework.boot:spring-boot-starter-jdbc'

    //oracle
    runtimeOnly 'com.oracle.ojdbc:ojdbc8'

 

Yml 파일에 대해 설정합니다.

 

spring:
  profiles:
    active: dev


  datasource:
    db1:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@localhost:1521:ORCL
      username: db1
      password: db1
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        maximum-pool-size: 10
        connection-test-query: SELECT 1 FROM SYS.DUAL
        minimum-idle: 5
        connection-timeout: 100000

    db2:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@localhost:1521:orcl
      username: db2
      password: db2
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        maximum-pool-size: 10
        connection-test-query: SELECT 1 FROM SYS.DUAL
        minimum-idle: 5
        connection-timeout: 100000

  devtools:
    livereload:
      enabled: true


server:
  port: 8080

 

DB를 두개 연결하기 때문에 db1: 과 db2: 로 구분합니다. DB 한개만 쓰시려면 db2: 이하는 삭제해주세요.

 

 

DB1 Controller 설정

package conn.multiple.dbone.controller;

import conn.multiple.dbone.service.OneOracleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/one")
public class OneOracleController {

    @Autowired private OneOracleService oneOracleService;

    @RequestMapping(value = "/getHostName", method = RequestMethod.GET)
    private String getHostName(){
        return this.oneOracleService.getHostName();
    }

}

DB1 Service 설정

package conn.multiple.dbone.service;

public interface OneOracleService {

    String getHostName();
}

package conn.multiple.dbone.service;

import conn.multiple.dbone.dao.OneOracleDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Slf4j
@Service
public class OneOracleServiceImpl implements OneOracleService{

    @Autowired
    private OneOracleDao oneOracleDao;

    @Override
    public String getHostName() {

        log.info("one oracle :: getHostName");

        return this.oneOracleDao.getHostName();
    }
}

DB1 Repository 설정

package conn.multiple.dbone.dao;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OneOracleDao {

    String getHostName();

}

DB1 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="conn.multiple.dbone.dao.OneOracleDao">
    <select id="getHostName" resultType="java.lang.String">
        SELECT  host_name
        FROM    v$instance
    </select>
</mapper>

구현의 편의를 위해 DB의 host name 정보를 가져옵니다.

 

 

DB1 Configure 설정

 

package conn.multiple.config;

import com.zaxxer.hikari.HikariDataSource;
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.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"conn.multiple.dbone.**.dao"}, sqlSessionFactoryRef = "oracleOneSqlSessionFactory")
public class MybatisDbOneConfig {


    @Bean(name = "oracleOneDatasource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DataSource oracleOneDatasource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name= "oracleOneSqlSessionFactory")
    @Primary
    public SqlSessionFactory oracleOneSqlSessionFactory(@Qualifier(value = "oracleOneDatasource") DataSource oracleOneDatasource) throws Exception{

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(oracleOneDatasource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        //oracle mapper 설정
        bean.setMapperLocations(resolver.getResources("classpath:mapper/oracle/one/**/*.xml"));

        return bean.getObject();

    }

    @Bean(name="oracleOneSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate oracleOneSqlSessionTemplate(@Qualifier(value = "oracleOneSqlSessionFactory")SqlSessionFactory oracleOneSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(oracleOneSqlSessionFactory);
    }
}

 

DB2를 쓰지 않을거면, @Primary 가 적힌 3부분에 대해 삭제합니다.

@MapperScan에 basePackages 에 있는 conn.multiple.dbone.**.dao 의 경로에 존재하는 dao(repository)만

oracleOneDatasource로 연결합니다.

 

 

 

 

DB2 Controller 설정

package conn.multiple.dbtwo.controller;

import conn.multiple.dbtwo.service.TwoOracleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/two")
public class TwoOracleController {

    @Autowired private TwoOracleService twoOracleService;

    @RequestMapping(value = "/getHostName", method = RequestMethod.GET)
    private String getHost0Name(){
        return this.twoOracleService.getHostName();
    }

}

DB2 Service 설정

package conn.multiple.dbtwo.service;

public interface TwoOracleService {

    String getHostName();
}

package conn.multiple.dbtwo.service;

import conn.multiple.dbtwo.dao.TwoOracleDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Slf4j
@Service
public class TwoOracleServiceImpl implements TwoOracleService {

    @Autowired
    private TwoOracleDao twoOracleDao;

    @Override
    public String getHostName() {

        log.info("one oracle :: getHostName");

        return this.twoOracleDao.getHostName();
    }
}

 

DB2 Repository 설정

package conn.multiple.dbtwo.dao;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TwoOracleDao {

    String getHostName();

}

DB2 Configure 설정

<?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="conn.multiple.dbtwo.dao.TwoOracleDao">
    <select id="getHostName" resultType="java.lang.String">
        SELECT  host_name
        FROM    v$instance
    </select>
</mapper>