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>