MyBatis with resultMap
Spring, MyBatis, resultMap 예제
EmpMapper.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="org.kdea.mybatis.EmpDAO"> <!--이 sql 문장과 매핑될 인터페이스의 완전한 경로-->
<resultMap id="empMap" type="org.kdea.mybatis.Emp">
<result property="page" column="page"/>
<result property="ttcnt" column="ttcnt"/>
<association property="pageBean" column="page" javaType="org.kdea.mybatis.PageUtilBean">
<result property="currPage" column="page"/>
<result property="rows" column="ttcnt"/>
</association>
<collection property="list" column="empno" javaType="ArrayList" ofType="org.kdea.mybatis.EmpVO">
<id property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="deptno" column="deptno"/>
<result property="sal" column="sal"/>
</collection>
</resultMap>
<select id="list" resultType="org.kdea.mybatis.EmpVO">
select * from emp2
</select>
<insert id="insert" parameterType="org.kdea.mybatis.EmpVO">
insert into emp2 (empno, ename, deptno, job, sal)
values ( #{empno}, #{ename}, #{deptno}, #{job}, #{sal} )
</insert>
<select id="info" resultType="org.kdea.mybatis.EmpVO">
select * from emp2 where empno=#{empno}
</select>
<update id="update" parameterType="org.kdea.mybatis.EmpVO">
update emp2 set deptno=#{deptno}, sal=#{sal} where empno=#{empno}
</update>
<delete id="delete" >
delete from emp2 where empno=#{empno}
</delete>
<!-- resultMap 설정 예 -->
<select id="listPage" parameterType="int" resultMap="empMap">
select empno,ename,job,deptno,sal,ttcnt,page from
(
select t2.*, FLOOR( (rn-1)/3+1 ) as page, count(*)over() as ttcnt from
(
select ROWNUM rn, t1.* from
(
select * from emp2 order by empno
)t1
)t2
)
where page=#{ page }
</select>
</mapper>
EmpMBController.java
package org.kdea.mybatis;
import java.util.HashMap;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
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;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/mb/")
public class EmpMybatisController {
@Autowired
private SqlSessionTemplate sqlSessionTemplate; // 설정파일에 빈으로 등록되었기 때문에 생성자나 Setter 없이 자동으로 주입
@RequestMapping("empList")
public String getEmpList(Model model) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
List<EmpVO> list = dao.list();
model.addAttribute("empList", list);
return "mb/empList";
}
@RequestMapping("listPage")
public String listPage(Model model,@RequestParam(defaultValue="1") int page) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
Emp e = dao.listPage(page);
e.getPageBean().setRowsPerPage(3);
e.getPageBean().setNumsPerPage(3);
model.addAttribute("e", e);
return "mb/empListPage";
}
@RequestMapping("empInfo")
public String getEmp(Model model, @RequestParam int empno) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
EmpVO emp = dao.info(empno);
model.addAttribute("empInfo", emp);
return "mb/empInfo";
}
@RequestMapping(value="empInputForm", method=RequestMethod.GET)
public String empInputForm(EmpVO emp) {
return "/mb/empInputForm";
}
@RequestMapping(value="empInsert", method=RequestMethod.POST)
public String empInsert(EmpVO emp) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
int n = dao.insert(emp);
return "redirect:/mb/empList";
}
@RequestMapping("empEdit")
public String empEdit(Model model, @RequestParam int empno) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
EmpVO emp = dao.info(empno);
model.addAttribute("empInfo", emp);
return "/mb/empEdit";
}
@RequestMapping("empUpdate")
public String empUpdate(Model model, EmpVO emp) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
int n = dao.update(emp);
model.addAttribute("empno", emp.getEmpno());
return "redirect:/mb/empInfo";
}
@RequestMapping("empDelete")
public String empDelete(@RequestParam int empno) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
int n = dao.delete(empno);
return "redirect:/mb/empList";
}
}
EmpDAO.java
package org.kdea.mybatis;
import java.util.List;
public interface EmpDAO {
public List<EmpVO> list();
public EmpVO info(int empno);
public int insert(EmpVO emp);
public int update(EmpVO emp);
public int delete(int empno);
public Emp listPage(int page);
}
Emp.java
package org.kdea.mybatis;
import java.util.List;
public class Emp {
private List<EmpVO> list;
private int ttcnt;
private int page;
private PageUtilBean pageBean;
public List<EmpVO> getList() {
return list;
}
public void setList(List<EmpVO> list) {
this.list = list;
}
public int getTtcnt() {
return ttcnt;
}
public void setTtcnt(int ttcnt) {
this.ttcnt = ttcnt;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public PageUtilBean getPageBean() {
return pageBean;
}
public void setPageBean(PageUtilBean pageBean) {
this.pageBean = pageBean;
}
}
empListPage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>ResultMap 설정 테스트</title>
<style type="text/css">
a {text-decoration: none; color:blue; }
</style>
</head>
<body><p>
현재 페이지/총페이지수/총 글수( ${e.pageBean.currPage} / ${e.pageBean.totalPages} / ${e.pageBean.rows } )<p>
${e.page} / ${e.ttcnt}<p>
<c:forEach var="empvo" items="${e.list}">
${empvo.empno} ${empvo.ename} ${empvo.job} ${empvo.deptno} ${empvo.sal}<br>
</c:forEach>
<hr>
${e.pageBean.navStr}
</body>
</html>
parameterType="hashmap" 을 사용하여 다수개의 파라미터(오브젝트)를 전달하는 예
HahsMap<String, Object> 을 사용하면 기본형 데이터나 오브젝트형의 데이터를 혼합하여 함께 MyBatis SQL 문장에 파라미터로 전달할 수 있다.
MyBatis 의 맵퍼 파일에서는 전달된 파라미터를 사용하는 방법에 따라서 #{}, ${}으로 구분된다
#{} : PreparedStatement 가 관리하는 SQL문장의 ? 에 전달되는 값
${} : SQL문장의 ?에 전달되는 값이 아닌 문장 자체를 구성하는데 필요한 경우
EmpMapper.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="org.kdea.mybatis.EmpDAO"> <!--이 sql 문장과 매핑될 인터페이스의 완전한 경로-->
<resultMap id="empMap" type="org.kdea.mybatis.Emp">
<result property="page" column="page"/>
<result property="ttcnt" column="ttcnt"/>
<association property="pageBean" column="page" javaType="org.kdea.mybatis.PageUtilBean">
<result property="currPage" column="page"/>
<result property="rows" column="ttcnt"/>
</association>
<collection property="list" column="empno" javaType="ArrayList" ofType="org.kdea.mybatis.EmpVO">
<id property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="deptno" column="deptno"/>
<result property="sal" column="sal"/>
</collection>
</resultMap>
<select id="list" resultType="org.kdea.mybatis.EmpVO">
select * from emp2
</select>
<insert id="insert" parameterType="org.kdea.mybatis.EmpVO">
insert into emp2 (empno, ename, deptno, job, sal)
values ( #{empno}, #{ename}, #{deptno}, #{job}, #{sal} )
</insert>
<select id="info" resultType="org.kdea.mybatis.EmpVO">
select * from emp2 where empno=#{empno}
</select>
<update id="update" parameterType="org.kdea.mybatis.EmpVO">
update emp2 set deptno=#{deptno}, sal=#{sal} where empno=#{empno}
</update>
<delete id="delete" >
delete from emp2 where empno=#{empno}
</delete>
<!-- resultMap 설정 예 -->
<!-- <select id="listPage" parameterType="int" resultMap="empMap">
select empno,ename,job,deptno,sal,ttcnt,page from
(
select t2.*, FLOOR( (rn-1)/3+1 ) as page, count(*)over() as ttcnt from
(
select ROWNUM rn, t1.* from
(
select * from emp2 order by empno
)t1
)t2
)
where page=#{page}
</select> -->
<select id="listPage" parameterType="hashmap" resultMap="empMap">
select empno,ename,job,deptno,sal,ttcnt,page from
(
select t2.*, FLOOR( (rn-1)/${pageBean.rowsPerPage}+1 ) as page, count(*)over() as ttcnt from
(
select ROWNUM rn, t1.* from
(
select * from emp2 where deptno!=${except} order by empno
)t1
)t2
)
where page=#{pageBean.currPage}
</select>
</mapper>
EmpDAO.java
package org.kdea.mybatis;
import java.util.HashMap;
import java.util.List;
public interface EmpDAO {
public List<EmpVO> list();
public EmpVO info(int empno);
public int insert(EmpVO emp);
public int update(EmpVO emp);
public int delete(int empno);
//public Emp listPage(int page);
public Emp listPage(HashMap<String, Object> map);
}
EmpMBController.java
@RequestMapping("listPage")
public String listPage(Model model,@RequestParam(defaultValue="1") int page) {
EmpDAO dao = sqlSessionTemplate.getMapper(EmpDAO.class);
HashMap<String,Object> map = new HashMap<String,Object>();
map.put("except",10); // 기본형 데이터를 MyBatis SQL문장에 파라미터로 전달하기 위해 맵에 저장
PageUtilBean pageBean = new PageUtilBean();
pageBean.setCurrPage(page);
pageBean.setRowsPerPage(3);
map.put("pageBean", pageBean); // 오브젝트형 데이터를 MyBatis SQL문장에 파라미터로 전달하기 위해 맵에 저장
Emp e = dao.listPage(map);
e.getPageBean().setRowsPerPage(3);
e.getPageBean().setNumsPerPage(3);
model.addAttribute("e", e);
return "mb/empListPage";
}
empListPage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>ResultMap 설정 테스트</title>
<style type="text/css">
a {text-decoration: none; color:blue; }
</style>
</head>
<body>
현재 페이지/총페이지수/총 글수( ${e.pageBean.currPage} / ${e.pageBean.totalPages} / ${e.pageBean.rows } )<p>
${e.page} / ${e.ttcnt}<p>
<c:forEach var="empvo" items="${e.list}">
${empvo.empno} ${empvo.ename} ${empvo.job} ${empvo.deptno} ${empvo.sal}<br>
</c:forEach>
<hr>
${e.pageBean.navStr}
</body>
</html>