[SpringBoot] 오라클 데이터베이스 연동하기!

데이터베이스

[SpringBoot] 오라클 데이터베이스 연동하기!

h__hj 2023. 1. 15. 14:37

# OracleXE, hikariCP, MyBatis 연동하기

기존코드 그대로 사용하면 좋겠지만, document나 example code 찾아보면서 하나하나 맞춘 코드.(힘들었음,,,ㅠ)

Spring Boot 2.7.5 버전에서 OracleXE(11g) 연동 및 hikariCP, MyBatis 연동.

# 환경

tool : STS 4.13.0
ver  : 2.7.5 [GA]
java : 11
repo : MAVEN
DB   : ORACLE XE (11g)
type : server

# 참고 페이지

블로그 글 안보고 아래 있는 참조 페이지가서 연동하는게 제일 좋을 수도,, 제 블로그 글은 허점이 있을 수도 있어요,,,

oracle example code: https://www.baeldung.com/spring-oracle-connection-pooling

hikariCP example code: https://www.baeldung.com/spring-boot-hikari

mybatis example code(spring): https://www.baeldung.com/spring-mybatis

mybatis example code(mybatis): http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

transaction manager example code: https://mybatis.org/spring/ko/transactions.html

# pom.xml

<dependencies>
    ,,,, 생략
    <!-- OJDBC -->
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc11</artifactId>
    </dependency>		
    <!-- JDBC CONNECTION && HikariCP -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <!-- MyBatis -->		
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>
    <!-- @ConfigurationProperties -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <optional>true</optional>
    </dependency>
    ,,,, 생략
</dependencies>

ojdbc를 사용하기 위한 com.oracle.database.jdbc 추가
spring boot 의 jdbc connection과 hikariCP pool을 담당하는 spring-boot-starter-data-jdbc 추가
mybatis, mybatis-spring 대신 mybatis-spring-boot-starter 추가
명확한 설정을 위한 @ConfigurationProperties 추가

# application.yml

spring:
  # OracleDB connection settings
  datasource:
    hikari:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@localhost:1521/XE
      username: ENC(1bp3zUekdgNEy0tovvPJHxDEjcCRdzon)
      password: ENC(NZ/o63yZ76IT8MCu3bqMtvSDXn6i8uNf)
      pool-name: HikariOracle
      #auto-commit: false #설정의 의미가 없는 듯.
      minimumIdle: 3
      maximum-pool-size: 10
      idle-timeout: 30000
      max-lifetime: 2000000
      connection-timeout: 30000

ENC(...) 이 부분은 아래 블로그 참고바람.

Spring Boot JasyptEncryptor 적용하기: https://hjho95.tistory.com/4

설정값에 대한 설명 (READ.md > Configuration) 참조

hikariCP setting: https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby

# OracleConfig.java

@Configuration
@MapperScan(value = {"com.prjt.blog.main.*.mapper", "com.prjt.blog.test.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory")
@EnableTransactionManagement
public class OracleConfig {

    @Autowired
    private SqlLogInterceptor sqlLogInterceptor;
    
    @Autowired
    private ApplicationContext applicationContext;
    
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
    
    @Bean
    public org.apache.ibatis.session.Configuration mybatisConfiguration() {    
        org.apache.ibatis.session.Configuration cofiuration = new org.apache.ibatis.session.Configuration();
        cofiuration.setLogImpl(Slf4jImpl.class);
        cofiuration.setDatabaseId("Oracle");
        cofiuration.setMapUnderscoreToCamelCase(true);
        return cofiuration; 
    }
    
    @Bean
    public SqlSessionFactory sqlSessionFactory(
            DataSource dataSource,
            org.apache.ibatis.session.Configuration mybatisConfiguration
    ) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mapper/main/**/*.xml"));
        sqlSessionFactoryBean.setPlugins(sqlLogInterceptor);
        // sqlSessionFactoryBean.setTypeAliasesPackage("com.example.domain");
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

application.yml에 설정을 다 기재하면 DataSource는 알아서 hikariCP 설정되서 들어 감! (로그로 확인함)

SqlLogInterceptor는 주석처리 해도 됨. (이것도 블로그 써야하는데,,,막막)

중요한 포인트는

@MapperScan 으로 interface로 설정한 Mapper.java 파일을 Bean처리 하는 것.

세션팩토리에서 setMapperLocations 으로 *Mapper.java와 맵핑된 *Mapper.xml 을 읽는 것.

세션팩토리의 dataSource와 tracsactionManager의 dataSource가 같다는 것.

mybatisConfiguration으로 MyBatis의 설정을 추가하는 것.

transactionManager 사용 시 @EnableTransactionManagement 추가 할 것.

사실 다 중요하네요,,,ㅎ

# 테스트

되는지 확인은 해야죠!

Controller

@Slf4j
@RestController
@RequestMapping("/api-v1/test")
public class TestController {

    @Autowired
    private TestService testService;
    
    @GetMapping("/sql/time")
    public RestMessage time(@RequestParam(required = false) String pattern) {
        if(StringUtils.isEmpty(pattern)) {
            pattern = "YYYY-MM-DD HH24:MI:SS";
        }
        String time = testService.time(pattern);
        RestMessage message = new RestMessage();
        message.setOk();
        message.setData(time);
        return message;
    }
}

Service

@Service
public class TestService {
    @Autowired
    private TestMapper testMapper;
    
    public String time(String pattern) {
        return testMapper.time(pattern);
    }
}

Mapper (java)

@Mapper
public interface TestMapper {

    String time(String pattern);
}

Mapper (xml)

<mapper namespace="com.prjt.blog.test.mapper.TestMapper"> 
    <select id="time" parameterType="string" resultType="string">
/** 현재시간 **/ SELECT TO_CHAR(SYSDATE, #{pattern, jdbcType=VARCHAR}) as time FROM DUAL
    </select>
</mapper>

Page

Log

[D] ==>  Preparing: /** 현재시간 **/ SELECT TO_CHAR(SYSDATE, ?) as time FROM DUAL
[D] ==> Parameters: YYYY-MM-DD(String)
[D] <==      Total: 1

추가 Log

[com.zaxxer.hikari.HikariConfig][1103][D] HikariOracle - configuration:
[com.zaxxer.hikari.HikariConfig][1135][D] allowPoolSuspension................................false
[com.zaxxer.hikari.HikariConfig][1135][D] autoCommit................................true
[com.zaxxer.hikari.HikariConfig][1135][D] catalog................................none
[com.zaxxer.hikari.HikariConfig][1135][D] connectionInitSql................................none
[com.zaxxer.hikari.HikariConfig][1135][D] connectionTestQuery................................none
[com.zaxxer.hikari.HikariConfig][1135][D] connectionTimeout................................30000
[com.zaxxer.hikari.HikariConfig][1135][D] dataSource................................none
[com.zaxxer.hikari.HikariConfig][1135][D] dataSourceClassName................................none
[com.zaxxer.hikari.HikariConfig][1135][D] dataSourceJNDI................................none
[com.zaxxer.hikari.HikariConfig][1135][D] dataSourceProperties................................{password=<masked>}
[com.zaxxer.hikari.HikariConfig][1135][D] driverClassName................................"oracle.jdbc.OracleDriver"
[com.zaxxer.hikari.HikariConfig][1135][D] exceptionOverrideClassName................................none
[com.zaxxer.hikari.HikariConfig][1135][D] healthCheckProperties................................{}
[com.zaxxer.hikari.HikariConfig][1135][D] healthCheckRegistry................................none
[com.zaxxer.hikari.HikariConfig][1135][D] idleTimeout................................30000
[com.zaxxer.hikari.HikariConfig][1135][D] initializationFailTimeout................................1
[com.zaxxer.hikari.HikariConfig][1135][D] isolateInternalQueries................................false
[com.zaxxer.hikari.HikariConfig][1135][D] jdbcUrl................................jdbc:oracle:thin:@localhost:1521/XE
[com.zaxxer.hikari.HikariConfig][1135][D] keepaliveTime................................0
[com.zaxxer.hikari.HikariConfig][1135][D] leakDetectionThreshold................................0
[com.zaxxer.hikari.HikariConfig][1135][D] maxLifetime................................2000000
[com.zaxxer.hikari.HikariConfig][1135][D] maximumPoolSize................................10
[com.zaxxer.hikari.HikariConfig][1135][D] metricRegistry................................none
[com.zaxxer.hikari.HikariConfig][1135][D] metricsTrackerFactory................................none
[com.zaxxer.hikari.HikariConfig][1135][D] minimumIdle................................3
[com.zaxxer.hikari.HikariConfig][1135][D] password................................<masked>
[com.zaxxer.hikari.HikariConfig][1135][D] poolName................................"HikariOracle"
[com.zaxxer.hikari.HikariConfig][1135][D] readOnly................................false
[com.zaxxer.hikari.HikariConfig][1135][D] registerMbeans................................false
[com.zaxxer.hikari.HikariConfig][1135][D] scheduledExecutor................................none
[com.zaxxer.hikari.HikariConfig][1135][D] schema................................none
[com.zaxxer.hikari.HikariConfig][1135][D] threadFactory................................internal
[com.zaxxer.hikari.HikariConfig][1135][D] transactionIsolation................................default
[com.zaxxer.hikari.HikariConfig][1135][D] username................................"PRACTICE"
[com.zaxxer.hikari.HikariConfig][1135][D] validationTimeout................................5000

hikariCP 설정에 대한 로그입니다.

# 내용

이거 하나 하려고 정말 하루종일 찾아 본 듯 하다.

이게 맞는 지,,, pom.xml을 줄일 수는 없는 지,,, 각 참고페이지에서 설정이 다르다보니 설정이 겹치거나 다른 부분 확인해 가면서 설정을 한 듯 함.

그래도 만들어 놓으니 조금 뿌듯,,,

# 데이터베이스 시리즈

https://hjho95.tistory.com/34 OracleXE, hikariCP, MyBatis 연동하기

https://hjho95.tistory.com/42 two databases 연결 (PostgreSQL, OracleXE)

# 데이터베이스 시리즈 참조 페이지

oracle example code: https://www.baeldung.com/spring-oracle-connection-pooling

postgre sql: https://www.codejava.net/frameworks/spring-boot/connect-to-postgresql-database-examples

hikariCP example code: https://www.baeldung.com/spring-boot-hikari
hikariCP setting: https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
mybatis example code(spring): https://www.baeldung.com/spring-mybatis
mybatis example code(mybatis): http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
transaction manager example code: https://mybatis.org/spring/ko/transactions.html

현재글[SpringBoot] 오라클 데이터베이스 연동하기!

Today :
Yesterday :

공부 하는 겸 작성하는 블로그