7. Spring boot Oracle DB연동(Thymeleaf, Hikari CP 활용)

소스코드 : https://github.com/

1. 기초 설정 및 순서

2. pom.xml

3. application.yml

4. config

5. mapper

6. view & controller

1. 기초 설정 및 순서   

---

0. 목표

  > Thymeleaf와 Hikari CP를 활용한 Spring boot 프로젝트 생성 및 run

1. 실행 환경

  > spring boot version : 2.1.6

  > jdk 8

  > sts3

2

. dependencies

  > Spring Boot DevTools

  > Lombok

  > MySQL Driver

  > MyBatis Framework

  > Spring Web Starter

  > thymeleaf

- <artifactId> spring-boot-starter-thymeleaf, thymeleaf, thymeleaf-spring5, thymeleaf-layout-dialect

3. 순서

  > pom.xml : dependency, oracle jdbc repository 추가

  > application.yml

- application.property에서 확장자 변경

- 내용 작성

- server.port, spring.database.hikari, thymeleaf

  > config 작성 : OracleDBConfiguration.java

  > mapper 작성(DB) : adminMember.xml

  > view 작성 : index.html

  > controller 작성 : CommonController.java

2. pom.xml

---

pom.xml 소스코드 보기

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.santaMin</groupId>
    <artifactId>fbExam</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>fbExam</name>
    <description>Freeboard Exam</description>
 
    <properties>
        <java.version>1.8</java.version>
    </properties>
 
    <!-- For oracle db -->
    <repositories>
        <repository>
            <id>codelds</id>
            <url>http://code.lds.org/nexus/content/groups/main-repo</url>
        </repository>
        <repository>
            <id>ICMRepository</id>
            <url>http://maven.icm.edu.pl/artifactory/repo/</url>
        </repository>
    </repositories>
 
 
    <dependencies>
        <!-- Spring boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
        <!-- Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <!-- Thymeleaf template engine -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
                <groupId>org.thymeleaf</groupId>
                <artifactId>thymeleaf</artifactId>
            </dependency>
            <dependency>
                <groupId>org.thymeleaf</groupId>
                <artifactId>thymeleaf-spring5</artifactId>
            </dependency>
            <dependency>
                <groupId>nz.net.ultraq.thymeleaf</groupId>
                <artifactId>thymeleaf-layout-dialect</artifactId>
            </dependency>
 
        <!-- DB -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- Oracle lib -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <!-- H2 for oracle db connection -->
        <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>
 
        <!-- Tools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3. application.yml

---

1. DB 설정

  > 사용 할 oracle db 정보

  > pool name (DataBase Connection Pool) 설정

  > driver class name 설정

2. thymeleaf 설정

  > prefix, suffix

application.yml 소스코드 보기

server:
  port: 18087
  
 
#db config
spring:
  datasource:
    hikari:
      jdbc-url: jdbc:oracle:thin:@localhost:1521/xe
      pool-name: hikari-cp
      maximum-pool-size: 30
      minimum-idle: 2
      driver-class-name: oracle.jdbc.driver.OracleDriver
      username: --
      password: --
 
        
  thymeleaf:
    cache: false
    enabled: true
    encoding: UTF-8
    prefix: classpath:/templates/
    suffix: .html
    check-template-location: true
    mode: LEGACYHTML5

4. config

---

1. OracleDBConfiguration

  > @Configuration의 역할

- Spring Framwork가 구동될 때, @Configuration 파일들을 찾아서 설정값을 초기화한다.
- @Configuration 파일들은 각 파일의 Bean 내용들을 토대로 초기값을 설정해준다.
- @Bean 들은 싱글턴 패턴으로 spring framework에 등록된다.

  > Hikari CP 등록

- hikariConfig() 메소드는 hikari cp config을 spring framework에 명시해준다. 
    - spring.datasource.hikari는 application.yml에 명시된 내용이다.
- dataSource() 메소드는 hikariConfig()의 리턴값을 dataSource로 등록한다.
    - dataSource를 설정함으로써 myBatis가 hikari dbcp(DataBaseConnectionPool)을 활용할 수 있도록 연결해주는 것이다.

  > Hikari CP 정보가 담긴 dataSource 등록

- sqlSessionFactory(dataSource)는 spring framework에 등록된 dataSource를 매개변수로 받아서, sqlSessionFactoryBean을 설정하고 그 객체를 spring framework에 등록한다.
- sqlSessionFactoryBean에 매개변수로 받은 myBatis dataSource를 등록한다.
- sqlSessionFactoryBean에 쿼리문이 저장된 xml(mapper)을 매핑하여 해당 쿼리를 활용할 수 있도록한다.

- mapper 위치 : classpath:/mapper/**/**.xml

- sqlSessionTemplate에 sqlSessionFactory를 등록하여 sqlSessionTemplate을 spring framework에 등록한다.

OracleDBConfiguration.java 소스코드 보기

package com.fbExam.config;
 
import javax.sql.DataSource;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
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 com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
 
/**
 * Spring Framework에 Bean 내용들을 토대로 초기값을 설정
 * @Bean 들은 싱글턴 패턴으로 spring framework에 등록된다.
 * 
 * Hikari CP를 Sql세션에 등록하기 위한 설정 내용
 * 
 * @author minhj
 *
 */
 
@Configuration
public class OracleDBConfiguration {
    
    @Autowired
    private ApplicationContext applicationContext;
    
    /**
     * hikari cp config 명시
     * conf prop -> application.yml 
     */
    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        return new HikariConfig();
    }
    
    /**
     * mybatis에 hikari cp 등록
     */
    @Bean
    public DataSource dataSource() {
        DataSource dataSource = new HikariDataSource( hikariConfig() );
        return dataSource;
    }
    
    /**
     * @param dataSource
     * @return
     * @throws Exception
     * SqlFactoryBean에 dataSource와 mapper 경로 등록
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlFactoryBean = new SqlSessionFactoryBean();
        
        sqlFactoryBean.setDataSource(dataSource);
        sqlFactoryBean.setMapperLocations(applicationContext.getResources("classpath:/mapper/**/**.xml"));
        return sqlFactoryBean.getObject();
    }
    
    /**
     * @param sqlSessionFactory
     * @return
     * SqlSessionFactory를 SqlSessionTemplate에 등록
     */
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5. mapper      

---

1. 경로 : src/main/resources/mapper/admin

2. 주의 :

  > config에서 설정한 mapper 경로에 매퍼xml이 하나 이상 있어야 한다.

  > !DOCTYPE을 올바르게 설정해야 한다.

- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">

  > <mapper>태그에 namespace attribute가 존재해야 한다.

  > xml 파일은 </mapper> 태그로 끝나야 한다

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
<mapper namespace="admin.member">
</mapper>

6. view & controller    

---

1. view

  > 경로 : src/main/resources/tamplates

  > thymeleaf xml name space 설정 필요

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
Spring Boot
</body>
</html>

2. controller

package com.fbExam.app.common.controller;
 
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
 
public class CommonController {
    
    @RequestMapping("/")
    public String rootIndex() {
        return "index";
    }
}