Dynamic Query의 사용

Iterate 예

<iterate prepend="AND" property="deptList" open="(" close=")" conjunction=",">
     #deptList[]#
</iterate> 

com.oracleclub.study.mvc.model.EmpSearch.java

package com.oracleclub.study.mvc.model;

import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;

/**
 * 
 * @author oramaster
 */
public class EmpSearch {
	private int deptno;

	private String searchKey;
	private String searchValue;

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	public String getSearchKey() {
		return searchKey;
	}

	public void setSearchKey(String searchKey) {
		this.searchKey = searchKey;
	}

	public String getSearchValue() {
		return searchValue;
	}

	public void setSearchValue(String searchValue) {
		this.searchValue = searchValue;
	}

	public String toString() {
		return ToStringBuilder.reflectionToString(this, ToStringStyle.SHORT_PREFIX_STYLE);
	}

}

/src/main/resources/sqlmap/Emp.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="emp">

  	<typeAlias alias="emp" type="com.oracleclub.study.mvc.model.Emp"/>
  	<typeAlias alias="empSearch" type="com.oracleclub.study.mvc.model.EmpSearch"/>

 	<select id="selectEmpList" resultClass="emp" parameterClass="empSearch" cacheModel="cachemodel.empCache" >
 	<![CDATA[
		SELECT empno, ename, job, sal, NVL(mgr,0) mgr
		  FROM emp 
		 WHERE empno > 0
	]]> 	 		 
		<isNotEqual property="deptno" compareValue="0" >	  
	       AND deptno = #deptno#	    
	    </isNotEqual>
	   
	    <isNotEmpty property="searchValue"> 
	        <isEqual property="searchKey" compareValue="ename">
		       AND ename LIKE '%'||#searchValue#||'%'
		    </isEqual>
		    
		    <isEqual property="searchKey" compareValue="job">
		       AND job LIKE '%'||#searchValue#||'%'
		    </isEqual>	
	    </isNotEmpty>
 	</select> 	

    <select id="selectEmp" resultClass="emp" parameterClass="int"  >
		SELECT empno, ename, job, sal, NVL(mgr,0) mgr, deptno  
		  FROM emp 
		 WHERE empno = #empno#
 	</select> 
 	
	<insert id="insertEmp"  parameterClass="emp"  >
 	    INSERT INTO emp (empno, ename, job, sal, mgr,  hiredate, deptno)
        VALUES (#empno#, #ename#, #job#, #sal#, 0, #hiredate#, #deptno#)
    </insert> 
  	
</sqlMap>

com.oracleclub.study.mvc.dao.EmpDAO.java

...
	/**
	 * 사원목록 정보 조회
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Emp> selectEmpList(EmpSearch search) {
		return (List<Emp>)getSqlMapClientTemplate().queryForList(NAMESPACE + "selectEmpList", search);
	}

...
 

com.oracleclub.study.mvc.dao.EmpDAOTest.java

...
	@Test
	public void testSelectEmpList() {
		EmpSearch search = new EmpSearch();
		search.setDeptno(10);
		Assert.assertTrue(empDAO.selectEmpList(search).size() > 0);
	}
...

com.oracleclub.study.mvc.bo.EmpBO.java

...

	/**
	 * 사원목록 조회
	 * 
	 * @return
	 */
	public List<Emp> getEmpList(EmpSearch search) {
		return empDAO.selectEmpList(search);
	}

...

com.oracleclub.study.mvc.controller.EmpListController.java

package com.oracleclub.study.mvc.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.oracleclub.study.mvc.bo.EmpBO;
import com.oracleclub.study.mvc.model.EmpSearch;

/**
 * 사원목록 조회
 * 
 * @author : gurubee
 * 
 */
@Controller
public class EmpListController {

	@Autowired
	private EmpBO empBO;

	@RequestMapping(value = "/emp/list")
	public ModelAndView list(@ModelAttribute EmpSearch empSearch) {

		ModelAndView mv = new ModelAndView();

		// 사원정보 조회 추가
		mv.addObject("empList", empBO.getEmpList(empSearch));

		// View 지정
		// mvc-dispatcher-servlet.xml 파일의 viewResolver를 사용
		mv.setViewName("emp/list");

		return mv;
	}
}

/WEB-INF/pages/emp/list.jsp

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="ko">
<head>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">
<style>
.error {
    color: #ff0000;
}
.errorblock{
    color: #000;
    background-color: #ffEEEE;
    border: 3px solid #ff0000;
    padding:8px;
    margin:16px;
}
</style>
<script type="text/javascript" language="JavaScript">
    function showMessage(msg){
        if(msg != ''){
            alert(msg); 
        }   
    }

    function runRegisterForm(){        
        location.href = "/emp/form";        
    }

    function runDelete(){
        if(isChecked(document.getElementsByName("empno"))){
            if(confirm('정말 삭제하시겠습니까?')){
                 document.form.action = '/emp/remove';
                 document.form.submit();
            }else{              
                return;
            }           
        }else{
            alert('삭제할 사원을 선택해 주세요!');
        }
    }

    function isChecked(chkbox){
        var flag = false;
        for(var i=0 ; i<chkbox.length ; i++){
            if(chkbox[i].checked){
                flag = true;
                break;
            }   
        }
        return flag;
    }
    
</script>
</head>
<body onload="showMessage('${msg}');">
<form name="sform" method="post" action="/emp/list">
    <table width="50%" border="0">
        <tr>
            <td width="100%" align="right">
            	
            	<select name="deptno" class="combobox">
	                <option value="0" >부서</option>
	                <option value="10" <c:if test="${empSearch.deptno==10}">selected</c:if>>10</option>                 
	                <option value="20" <c:if test="${empSearch.deptno==20}">selected</c:if>>20</option> 
	                <option value="30" <c:if test="${empSearch.deptno==30}">selected</c:if>>30</option>
	            </select>
	            
	            <select name="searchKey" class="combobox">
	                <option value="ename" <c:if test="${empSearch.searchKey=='ename'}">selected</c:if>>이름</option>
	                <option value="job"  <c:if test="${empSearch.searchKey=='job'}">selected</c:if>>직업</option> 
	            </select>

                <input type="text" name="searchValue" value="${empSearch.searchValue}" class="txtbox" size="25" />
                <input type="submit" value="검색" />  
                
                <input type="button" value="등록" onclick="runRegisterForm();"/>
                <input type="button" value="삭제" onclick="runDelete();"/>
            </td>
        </tr>
    </table>

    <table width="50%" border="1">
        <tr>
            <th>선택</th>
            <th>empno</th>
            <th>ename</th>
            <th>job</th>
            <th>sal</th>
            <th>mgr</th>
            <th>deptno</th>            
        </tr>
        <c:forEach var="emp" items="${empList}" varStatus="c">
            <tr  > 
                <td><input type="radio" name="empno" value="${emp.empno}" /></td>
                <td>${emp.empno}</td>
                <td><a href="/emp/view/${emp.empno}">${emp.ename}</a></td>
                <td>${emp.job}</td>
                <td><fmt:formatNumber value="${emp.sal}" type="number" /></td>
                <td>${emp.mgr}</td>
                <td>${emp.deptno}</td>                
            </tr>
        </c:forEach>
    </table>
</form>
</body>
</html>