Spring 4 MyBatis 연동

Spring4 MyBatis 소개

[출처] http://aid.altibase.com/pages/viewpage.action?pageId=7340856

MyBatis 는 개발자가 지정한 SQL, 저장프로시저 그리고 몇가지 고급 매핑을 지원하는 퍼시스턴스 프레임워크이다.

MyBatis 는 JDBC 코드와 수동으로 셋팅하는 파라미터와 결과 매핑을 제거한다.

MyBatis 는 데이터베이스 레코드에 원시타입과 Map 인터페이스 그리고 자바 POJO 를 설정하고 매핑하기 위해 XML 과 애노테이션을 사용할 수 있다.

기존의 JDBC를 이용하여 프로그래밍하는 방식은 프로그램 소스 안에 SQL문을 작성하였지만, MyBatis를 이용하면 SQL문을 프로그램에서 분리하여 XML 파일에 별도로 작성한다. 따라서 프로그래머가 기존의 JDBC를 사용할 때 보다 프로그래밍하는 부담이 줄어들게 된다. 뿐만 아니라 SQL을 변경하고자 할 경우 기존처럼 프로그램을 수정하는 것이 아니라 XML 파일의 SQL문 만을 변경하면 되기 때문에 SQL 변환이 자유롭다는 특징이 있다.

다음은 MyBatis의 구조를 간단하게 표현한 그림이다.

MyBatis 프로젝트 구성

IntelliJ로 시작하는 Spring Framework 4 글을 참조하여 기본 프로젝트 틀 구성합니다.

그리고, MyBatis를 사용하기 위한 외부 라이브러리 의존성을 다음과 같이 설정합니다.

 

/pom.xml

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.first</groupId>
  <artifactId>first</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>first Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <properties>
    <java-version>1.8</java-version>
    <spring.version>4.3.4.RELEASE</spring.version>
    <org.slf4j-version>1.6.6</org.slf4j-version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>

    <!-- Logging -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>${org.slf4j-version}</version>
    </dependency>

    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>jcl-over-slf4j</artifactId>
      <version>${org.slf4j-version}</version>
    </dependency>

    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.14</version>
      <scope>runtime</scope>
    </dependency>

    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>${org.slf4j-version}</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.31</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.7</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.2</version>
    </dependency>
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>

    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>

    <!-- Jackson -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-annotations</artifactId>
      <version>2.7.5</version>
    </dependency>

    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.7.5</version>
    </dependency>

    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.7.5</version>
    </dependency>



  </dependencies>
  <build>
    <finalName>first</finalName>
  </build>
</project>

MyBatis 디렉토리 구성

MyBatis 프로젝트 설정

다음과 같이 MyBatis 사용에 필요한 설정파일을 적절히 생성 또는 수정합니다.

/src/main/resources/database/query.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="database">
    <select id="getSuperAdmin" resultType="String" parameterType="String">
        SELECT admin_user_name
        FROM tb_admin_user
        WHERE admin_type=#{adminType}
        LIMIT 1
    </select>

    <select id="getAdminUserNameList" resultType="String" parameterType="String">
        SELECT admin_user_name
        FROM tb_admin_user
        WHERE admin_type IN (#{one}, #{two})
    </select>

    <select id="getAllAdminUserNameList" resultType="String">
        SELECT admin_user_name
        FROM tb_admin_user
    </select>

    <select id="updateAdminName" parameterType="String">
        UPDATE tb_admin_user
        SET admin_user_name = #{newAdminName}
        WHERE admin_user_name = #{findAdminName}
    </select>

    <select id="findAdminEMail" parameterType="com.first.domain.Admin" resultType="String">
        SELECT email_address
        FROM tb_admin_user
        WHERE admin_user_name = #{admin_user_name}
        AND admin_type = #{admin_type}
    </select>
</mapper>

/src/main/webapp/WEB-INF/log4j.properties

# Root logger option
log4j.rootLogger=DEBUG, stdout, file

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

# Redirect log messages to a log file
log4j.appender.file=org.apache.log4j.RollingFileAppender
#outputs to Tomcat home
log4j.appender.file.File=${catalina.home}/logs/myapp.log
log4j.appender.file.MaxFileSize=5MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

/src/main/webapp/jsp/selectWhere.jsp

<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<html>
<head>
    <title>Title</title>
</head>
<body>

 <c:if test="${not empty adminList}">

     <ul>
         <c:forEach var="adminUserName" items="${adminList}">
             <li>${adminUserName}</li>
         </c:forEach>
     </ul>

 </c:if>

</body>
</html>

/src/main/webapp/WEB-INF/applicationContext.xml

applicationContext 설정파일에서는 MyBatis를 사용하기 위해서 필요한 DataSource, SqlSessionFactory, SqlSession 에 대한 Bean을 정의합니다. 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="com.first"/>
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/db_tmonplus" />
        <property name="username" value="root" />
        <property name="password" value="" />
    </bean>
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath*:database/**" />
    </bean>
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg ref="sqlSessionFactory" />
    </bean>
</beans>

/src/main/webapp/WEB-INF/dispatcher-servlet.xml

dispatcher-servlet 설정에서는 JSP를 ViewResolver로 사용하기 위해서 필요한 ViewResolver에 대한 Bean을 정의합니다.

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns:beans="http://www.springframework.org/schema/beans"
             xmlns="http://www.springframework.org/schema/mvc"
             xmlns:context="http://www.springframework.org/schema/context"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
             xsi:schemaLocation="http://www.springframework.org/schema/beans
             http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
             http://www.springframework.org/schema/mvc
             http://www.springframework.org/schema/mvc/spring-mvc.xsd
             http://www.springframework.org/schema/context
             http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    <context:component-scan base-package="com.first" />
    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />
    <beans:bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">

        <beans:property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
        <beans:property name="prefix" value="/jsp/" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>
</beans:beans>

/src/main/webapp/WEB-INF/web.xml

web 설정에서는 applicationContext, log, servlet 등과 같은 설정을 추가합니다.

<주의>

JSTL을 사용하기 위해서 web-app scheme 버전을 2.5 이상으로 설정해야 합니다.

<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
          http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         version="2.5">

    <display-name>Archetype Created Web Application</display-name>
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

    <!-- ============================================================= -->
    <!-- log4j setting -->
    <!-- ============================================================= -->

    <listener>
        <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
    </listener>
    <context-param>
        <param-name>log4jConfigLocation</param-name>
        <param-value>/WEB-INF/log4j.properties</param-value>
    </context-param>

    <context-param>
        <param-name>webAppRootKey</param-name>
        <param-value>incross.spring</param-value>
    </context-param>

    <servlet>
        <servlet-name>action</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <!--
            <param-value>classpath:spring/*-servlet.xml</param-value>
            -->
            <param-value>/WEB-INF/dispatcher-servlet.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>action</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>

    <!--
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    -->

    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class> org.springframework.web.filter.CharacterEncodingFilter </filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>utf-8</param-value>
        </init-param>
    </filter>

    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>*.do</url-pattern>
    </filter-mapping>


</web-app>

MyBatis CRUD 예제 구현

/src/main/java/com.first/repository/MyRepository.java

package com.first.repository;

import com.first.domain.Admin;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class MyRepository {
    @Autowired
    private SqlSession sql;

    private static final Logger logger = LoggerFactory.getLogger(MyRepository.class);

    public String getAdmin(String adminType) throws SQLException {
        String superAdmin = (String)sql.selectOne("database.getSuperAdmin", adminType);
        return superAdmin;
    }

    public List<String> getAdminUserNameList(Map<String, String> params) throws SQLException {
        List<String> adminUserNameList = sql.selectList("database.getAdminUserNameList", params);
        return adminUserNameList;
    }

    public List<String> getAllAdminUserNameList() throws SQLException {
        List<String> adminUserNameList = sql.selectList("database.getAllAdminUserNameList");
        return adminUserNameList;
    }

    public void updateAdminName(Map<String, String> params) throws SQLException {
        sql.update("database.updateAdminName", params);
    }

    public List<String> findAdminEmail(Admin admin) {
        return sql.selectList("database.findAdminEMail", admin);
    }
}

@Repository 와 @Service 어노테이션의 차이점.

@Repository는 DAO(Data Access Object)에서 사용이 되며,

@Service는 서비스 계층의 Business Object 에서 사용이 됩니다.

보통 여러개의 연산을 동시에 트랜잭션 처리해야 하는 경우 @Service 계층에서, 단일 트랜잭션을 처리해야하는 경우에는 @Repository 계층에서 간단히 처리하기도 합니다.

그리고, 두 계층 모두 컴퍼넌트 범위(@Scope)는 싱글톤 입니다. 

추가로 DI (Dependency Injection)를 이용한 bean 생성은 내부적으로 싱글톤 패턴을 이용해 객체를 인스턴스화 합니다.

/src/main/java/com.first/MainController.java

package com.first;

import com.first.repository.MyRepository;
//import org.apache.log4j.Logger;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class MainController {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private MyRepository myRepository;

    @RequestMapping("/selectWhere")
    public String selectWhere(ModelMap model) throws Exception {

        String superAdmin = null;
        List<String> adminUserNameList = null;
        Map<String, String> param = new HashMap<String, String>();

        try {
            param.put("one", "PARTNER");
            param.put("two", "CONSULTANT");
            superAdmin = myRepository.getAdmin("SUPERADMIN");
            adminUserNameList = myRepository.getAdminUserNameList(param);
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (null != superAdmin) {
            model.addAttribute("superAdmin", superAdmin);
        }

        if (null != adminUserNameList) {
            model.addAttribute("adminList", adminUserNameList);
        }

        return "selectWhere";
    }
}

/src/main/java/com.first/MyRestController.java

package com.first;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.first.domain.Admin;
import com.first.repository.MyRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class MyRestController {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private MyRepository myRepository;

    @RequestMapping("/")
    public String index() {
        return "Hello mybatis";
    }

    @RequestMapping(value = "/selectList", method = RequestMethod.GET, produces = "application/json")
    public List<String> selectList() {

        List<String> adminUserNameList = null;

        try {
            adminUserNameList = myRepository.getAllAdminUserNameList();
        } catch (Exception e) {
            e.printStackTrace();
        }

        logger.info("fetch " + adminUserNameList );
        return adminUserNameList;
    }

    @RequestMapping("/selectOne")
    public String selectOne() {
        String superAdmin = null;
        List<String> adminUserNameList = null;
        Map<String, String> param = new HashMap<String, String>();

        try {
            superAdmin = myRepository.getAdmin("SUPERADMIN");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return superAdmin;
    }

    /// http://localhost:8080/update?findAdminName=root&newAdminName=Root
    @RequestMapping("/update")
    public List<String> update(@RequestParam HashMap params) {

        try {
            myRepository.updateAdminName(params);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return selectList();
    }

    /// http://localhost:8080/find?admin_user_name=root&admin_type=SUPERADMIN
    @RequestMapping(value = "/find", method = RequestMethod.GET)
    public List<String> find(@RequestParam HashMap params) {

        List<String> adminEMailList = null;
        ObjectMapper mapper = new ObjectMapper();
        Admin admin = mapper.convertValue(params, Admin.class);

        try {
            adminEMailList = myRepository.findAdminEmail(admin);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return adminEMailList;
    }
}

실행결과

localhost:8080/

localhost:8080/selectWhere

localhost:8080/selectList

localhost:8080/selectWhere

localhost:8080/update?findAdminName=root&newAdminName=RoOt

http://localhost:8080/find?admin_user_name=root&admin_type=SUPERADMIN

출처: http://jinhokwon.tistory.com/92 [I am]