Spring과 MyBatis 연동 예제

■ 전체 예제 소스

dbsample.zip

■ 개발환경 

# Spring : STS 3.8.4.RELEASE (https://spring.io/tools/sts/all/)

# Database : MariaDB (https://mariadb.com/)

■ DB 테이블 생성

CREATE TABLE `tb_code` (
	`CD_DO` VARCHAR(3) NOT NULL COMMENT '도메인',
	`CD_UP` VARCHAR(4) NOT NULL COMMENT '상위코드',
	`CD_DN` VARCHAR(5) NOT NULL COMMENT '하위코드',
	`CD_KOR` VARCHAR(50) NOT NULL COMMENT '한글코드명',
	`CD_ENG` VARCHAR(50) NULL DEFAULT NULL COMMENT '영문코드명',
	`CD_DESC` VARCHAR(300) NULL DEFAULT NULL COMMENT '코드상세설명',
	`USE_YN` VARCHAR(1) NOT NULL COMMENT '사용여부',
	`SORT_SEQ` INT(11) NOT NULL DEFAULT '0' COMMENT '코드정렬순번',
	`MOD_ID` VARCHAR(12) NULL DEFAULT NULL COMMENT '변경자ID',
	`MOD_DT` DATE NULL DEFAULT NULL COMMENT '변경일자',
	`CRE_ID` VARCHAR(12) NOT NULL COMMENT '생성자ID',
	`CRE_DT` DATE NOT NULL COMMENT '생성일자',
	PRIMARY KEY (`CD_DO`, `CD_UP`, `CD_DN`)
)

■ Spring Legacy Project 생성

▼

▼

■ 전체 디렉토리 구조

■ pom.xml 수정

# 라이브러리 추가

- mybatis

- mybatis-spring

- spring-jdbc

- commons-dbcp

- mariadb-java-client (사용하는 Database에 맞게 수정)

- log4jdbc-remix

		<!-- mybatis -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.4.2</version>
		</dependency>
		
		<!-- mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.1</version>
		</dependency>
		
		<!-- spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>3.1.1.RELEASE</version>
		</dependency>

		<!-- commons-dbcp -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

		<!-- mariadb-java-client -->
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<version>1.4.6</version>
		</dependency>

		<!-- log4jdbc-remix -->
		<dependency>
			<groupId>org.lazyluke</groupId>
			<artifactId>log4jdbc-remix</artifactId>
			<version>0.2.7</version>
		</dependency>

■ web.xml 수정

# classpath 하위로 스프링 설정파일 경로 설정

# Characterset Encoding 설정

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 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">
	
	[ ... 중략 ... ]

	<!-- classpath 의 spring/config 경로에서 스프링 설정파일들을 로드함 -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>
			/WEB-INF/spring/root-context.xml
			classpath*:config/spring/context-*.xml
		</param-value>
	</context-param>
	
	[ ... 중략 ... ]

	<!-- 인코딩 설정(UTF-8) -->
	<!-- Encoding Filter -->
	<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>/*</url-pattern>
	</filter-mapping>

</web-app>

■ db.properties 파일 생성

# 경로 : /src/main/resources/config/spring/db.properties

# 내용 : JDBC 파라미터 정보

jdbc.driver=org.mariadb.jdbc.Driver
jdbc.url=jdbc:mariadb://localhost:3306/user
jdbc.username=name
jdbc.password=pass

■ context-datasource.xml 파일 생성

# 경로 : /src/main/resources/config/spring/context-datasource.xml

# 내용 : JDBC Connection 설정, JDBC 관련 Log4j 설정

<?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"
	xmlns:util="http://www.springframework.org/schema/util"
	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-3.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd">
	
	<!-- properties 사용 설정 -->
	<context:property-placeholder location="classpath:config/spring/db.properties" />

	<!-- JDBC Connection 설정 -->	
	<bean id="dataSourceSpied" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>

	<!-- JDBC Log4j 설정 -->
	<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
		<constructor-arg ref="dataSourceSpied" />
		<property name="logFormatter">
			<bean class="net.sf.log4jdbc.tools.Log4JdbcCustomFormatter">
				<property name="loggingType" value="MULTI_LINE" />
			</bean>
		</property>
	</bean>

</beans>

■ context-mapper.xml 파일 생성

# 경로 : /src/main/resources/config/spring/context-mapper.xml

# 내용 : SqlSessionFactoryBean, SqlSessionTemplate 설정

<?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">
	
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="mapperLocations">
			<array>
				<value>classpath:/mapper/**/*_mapper.xml</value>
			</array>
		</property>
		<property name="configLocation" value="classpath:config/mybatis/mybatis-config.xml"></property>
	</bean>
	
	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSession" />
	</bean>

</beans>

■ mapper 파일 생성

# 경로 : /src/main/resources/mapper/code/code_mapper.xml

# 내용 : SQL 정의

<?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="com.mybatis.sample.code_mapper">

	<select id="selectCodeList" parameterType="code" resultType="hashmap">
		SELECT
			CD_DO,
			CD_UP,
			CD_DN,
			CD_KOR,
			CD_ENG,
			CD_DESC,
			USE_YN,
			SORT_SEQ,
			MOD_ID,
			MOD_DT,
			CRE_ID,
			CRE_DT
		FROM
			USER.TB_CODE
		WHERE
			USE_YN = 'Y'
		<if test="cdDo != null and cdDo != ''">
			AND CD_DO = #{cdDo}
		</if>
		<if test="cdUp != null and cdUp != ''">
			AND CD_UP = #{cdUp}
		</if>
		<if test="cdDn != null and cdDn != ''">
			AND CD_DN = #{cdDn}
		</if>
	</select>
	
	<insert id="insertCode" parameterType="code">
		INSERT
			INTO
				USER.TB_CODE(
					CD_DO,
					CD_UP,
					CD_DN,
					CD_KOR,
					CD_ENG,
					CD_DESC,
					USE_YN,
					SORT_SEQ,
					MOD_ID,
					MOD_DT,
					CRE_ID,
					CRE_DT
				)
			VALUES(
				#{cdDo},
				#{cdUp},
				#{cdDn},
				#{cdKor},
				#{cdEng},
				#{cdDesc},
				#{useYn},
				0,
				NULL,
				NULL,
				#{creId},
				SYSDATE()
			)
	</insert>
	
	<delete id="deleteCode" parameterType="code">
		UPDATE
			USER.TB_CODE
		SET
			USE_YN = 'N',
			MOD_ID = #{modId},
			MOD_DT = SYSDATE()
		WHERE
			CD_DO = #{cdDo}
			AND CD_UP = #{cdUp}
			AND CD_DN = #{cdDn}
	</delete>

</mapper>

■ mybatis-config.xml 파일 생성

# 경로 : /src/main/resources/config/mybatis/mybatis-config.xml

# 내용 : MyBatis 관련 설정

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeAliases>
		<typeAlias type="com.mybatis.sample.vo.CodeVO" alias="code" />
	</typeAliases>
</configuration>

■ 상수선언 파일 생성

# 경로 : /src/main/java/com/mybatis/sample/constance/DaoConst.java

package com.mybatis.sample.constance;

public class DaoConst {
	
	public static final String NAMESPACE_CODE = "com.mybatis.sample.code_mapper";

}

■ VO 생성

# 경로 : /src/main/java/com/mybatis/sample/vo/CodeVO.java

package com.mybatis.sample.vo;

public class CodeVO {
	
	private String cdDo;
	private String cdUp;
	private String cdDn;
	private String cdKor;
	private String cdEng;
	private String cdDesc;
	private String useYn;
	private String sortSeq;
	private String modId;
	private String modDt;
	private String creId;
	private String creDt;
	
	public String getCdDo() {
		return cdDo;
	}
	public void setCdDo(String cdDo) {
		this.cdDo = cdDo;
	}
	public String getCdUp() {
		return cdUp;
	}
	public void setCdUp(String cdUp) {
		this.cdUp = cdUp;
	}
	public String getCdDn() {
		return cdDn;
	}
	public void setCdDn(String cdDn) {
		this.cdDn = cdDn;
	}
	public String getCdKor() {
		return cdKor;
	}
	public void setCdKor(String cdKor) {
		this.cdKor = cdKor;
	}
	public String getCdEng() {
		return cdEng;
	}
	public void setCdEng(String cdEng) {
		this.cdEng = cdEng;
	}
	public String getCdDesc() {
		return cdDesc;
	}
	public void setCdDesc(String cdDesc) {
		this.cdDesc = cdDesc;
	}
	public String getUseYn() {
		return useYn;
	}
	public void setUseYn(String useYn) {
		this.useYn = useYn;
	}
	public String getSortSeq() {
		return sortSeq;
	}
	public void setSortSeq(String sortSeq) {
		this.sortSeq = sortSeq;
	}
	public String getModId() {
		return modId;
	}
	public void setModId(String modId) {
		this.modId = modId;
	}
	public String getModDt() {
		return modDt;
	}
	public void setModDt(String modDt) {
		this.modDt = modDt;
	}
	public String getCreId() {
		return creId;
	}
	public void setCreId(String creId) {
		this.creId = creId;
	}
	public String getCreDt() {
		return creDt;
	}
	public void setCreDt(String creDt) {
		this.creDt = creDt;
	}
	
	@Override
	public String toString() {
		return "CodeVO [cdDo=" + cdDo + ", cdUp=" + cdUp + ", cdDn=" + cdDn + ", cdKor=" + cdKor + ", cdEng=" + cdEng
				+ ", cdDesc=" + cdDesc + ", useYn=" + useYn + ", sortSeq=" + sortSeq + ", modId=" + modId + ", modDt="
				+ modDt + ", creId=" + creId + ", creDt=" + creDt + "]";
	}
	
}

■ DAO interface 생성

# 경로 : /src/main/java/com/mybatis/sample/dao/CodeDAO.java

public interface CodeDAO {
	
	public List<Map<String, Object>> selectCodeList(CodeVO vo);

	public int insertCode(CodeVO vo);

	public int deleteCode(CodeVO vo);
	
}

■ DAO 구현 class 생성

# 경로 : /src/main/java/com/mybatis/sample/dao/impl/CodeDAOImpl.java

@Repository("codeDAO")
public class CodeDAOImpl implements CodeDAO {

	Logger log = Logger.getLogger(this.getClass());
	
	@Autowired
	private SqlSessionTemplate sqlSession;
	
	@Override
	public List<Map<String, Object>> selectCodeList(CodeVO vo) {
		return sqlSession.selectList(DaoConst.NAMESPACE_CODE + ".selectCodeList", vo);
	}

	@Override
	public int insertCode(CodeVO vo) {
		return sqlSession.insert(DaoConst.NAMESPACE_CODE + ".insertCode", vo);
	}

	@Override
	public int deleteCode(CodeVO vo) {
		return sqlSession.delete(DaoConst.NAMESPACE_CODE + ".deleteCode", vo);
	}
	
}

■ Service interface 생성

# 경로 : /src/main/java/com/mybatis/sample/service/CodeService.java

public interface CodeService {

	List<Map<String, Object>> getCodeList(CodeVO vo);

	int saveCode(CodeVO vo);

	int removeCode(CodeVO vo);

}

■ Service 구현 class 생성

# 경로 : /src/main/java/com/mybatis/sample/service/impl/CodeServiceImpl.java

@Service("codeService")
public class CodeServiceImpl implements CodeService {

	Logger log = Logger.getLogger(this.getClass());
	
	@Autowired
	private CodeDAO codeDAO;
	
	@Override
	public List<Map<String, Object>> getCodeList(CodeVO vo) {
		return codeDAO.selectCodeList(vo);
	}

	@Override
	public int saveCode(CodeVO vo) {
		int result = codeDAO.insertCode(vo);
		log.info("[ save result ] " + result);
		return result;
	}

	@Override
	public int removeCode(CodeVO vo) {
		int result = codeDAO.deleteCode(vo);
		log.info("[ remove result ] " + result);
		return result;
	}
}

■ Controller 생성

# 경로 : /src/main/java/com/mybatis/sample/controller/CodeController.java

@Controller
public class CodeController {
	
	Logger log = Logger.getLogger(this.getClass());
	
	@Autowired
	private CodeService codeService;
	
	@RequestMapping(value="/code/list")
	public ModelAndView getCodeList(@ModelAttribute CodeVO vo) throws Exception {
		
		log.info(vo.toString().replaceAll("null", ""));
		
		ModelAndView mv = new ModelAndView();
		
		// 목록조회
		List<Map<String, Object>> list = codeService.getCodeList(vo);
		
		// View에 보여줄 데이터 SET
		mv.addObject("list", list);
		
		// View 이름 설정
		mv.setViewName("result");
		
		return mv;
	}

	@RequestMapping(value="/code/save")
	public ModelAndView saveCode(@ModelAttribute CodeVO vo) throws Exception {
		
		log.info(vo.toString().replaceAll("null", ""));
		
		ModelAndView mv = new ModelAndView();
		
		// 데이터 저장
		int result = codeService.saveCode(vo);
		
		// 저장결과 SET
		mv.addObject("result", result);
		
		// View 이름 설정
		mv.setViewName("result");
		
		return mv;
	}

	@RequestMapping(value="/code/remove")
	public ModelAndView removeCode(@ModelAttribute CodeVO vo) throws Exception {
		
		log.info(vo.toString().replaceAll("null", ""));
		
		ModelAndView mv = new ModelAndView();
		
		// 데이터 삭제
		int result = codeService.removeCode(vo);
		
		// 삭제결과 SET
		mv.addObject("result", result);
		
		// View 이름 설정
		mv.setViewName("result");
		
		return mv;
	}
}

■ View 생성 (Controller에서 "/code/list" 요청의 응답결과 페이지)

경로 : /src/main/webapp/WEB-INF/views/result.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>Insert title here</title>

</head>

<body>

<table>

<tr>

<td>도메인</td>

<td>상위코드</td>

<td>하위코드</td>

<td>한글명</td>

<td>영문명</td>

<td>설명</td>

<td>사용여부</td>

<td>코드정렬순번</td>

<td>변경자</td>

<td>변경일자</td>

<td>생성자</td>

<td>생성일자</td>

</tr>

<c:forEach items="${list}" var="row">

<tr>

<td>${row.CD_DO}</td>

<td>${row.CD_UP}</td>

<td>${row.CD_DN}</td>

<td>${row.CD_KOR}</td>

<td>${row.CD_ENG}</td>

<td>${row.CD_DESC}</td>

<td>${row.USE_YN}</td>

<td>${row.SORT_SEQ}</td>

<td>${row.MOD_ID}</td>

<td>${row.MOD_DT}</td>

<td>${row.CRE_ID}</td>

<td>${row.CRE_DT}</td>

</tr>

</c:forEach>

</table>

<br />

<input type="button" value="이전" onclick="javascript:history.back();" />

</body>

</html>