[Spring] MySQL 연동 방법

테스트 환경

- Spring 4.3.18

- STS 플러그인 3.9.5

- JDK 1.8

- Tomcat 8.0

- MySQL 8.0

Step 0. 준비 사항

---

- 테스트 할 프로젝트가 준비되어 있지 않다면 Eclipse에 STS 플러그인을 설치한 후 Spring MVC Project 생성

- Spring, JDK, Tomcat 버전을 테스트 환경과 같이 설정

- MySQL DB 서버가 없다면 설치

Step 1. pom.xml 파일에 관련 라이브러리 추가

---

        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
 
        <!-- Apache Commons-DBCP -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.4.0</version>
        </dependency>

Step 2. root-context.xml 파일에 dataSource 추가

---

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    
    <!-- Root Context: defines shared resources visible to all other web components -->
    
    <!-- MySQL dataSource -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> 
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/> 
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> 
        <property name="username" value="user"/> 
        <property name="password" value="user123"/>
    </bean>
                
</beans>

* spring-jdbc에서 제공하는 기능으로 DB 연동을 할수도 있지만 추후 Connection Pool을 사용하기 위해 apache에서 제공하는 commons-dbcp 라이브러리를 사용하였음

Step 3. HomeController.java 파일에 테스트 소스 작성

---

package com.my.mysql;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Date;
import java.util.Locale;
 
import org.apache.commons.dbcp2.BasicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
 
/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {
    
    private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
    
    @Autowired
    BasicDataSource dataSource;
    
    /**
     * Simply selects the home view to render by returning its name.
     */
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String home(Locale locale, Model model) {
        logger.info("Welcome home! The client locale is {}.", locale);
        
        Date date = new Date();
        DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);
        
        String formattedDate = dateFormat.format(date);
        
        model.addAttribute("serverTime", formattedDate );
        
        return "home";
    }
    
    @RequestMapping("/dbTest.do")
    public String dbTest(Model model) {
        Connection conn = null;
        Statement st = null;
        
        try {
            conn = dataSource.getConnection();
            st = conn.createStatement();
            ResultSet rs = st.executeQuery("select now() as now;");
            
            while(rs.next()) {
                model.addAttribute("serverTime", rs.getString("now"));
            }
            
        } catch (Exception e) {
            e.printStackTrace();    
            
        } finally {
            try {
                if(st != null) st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            try {
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }                        
        }
        
        return "home";
    }
    
}
 

48~82 라인이 DB 접속 테스트를 위해 작성된 코드이다.

Step 4. 테스트

---