본문 바로가기
Java/└ Mybatis

Mybatis) 동적쿼리 - 심화된 검색 기능 1-2 (<![CDATA[]]>, <choose>분기처리)

by 박채니 2022. 8. 10.
SMALL

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

검색타입, 성별, 급여에 대해 검색할 수 있도록 해보겠습니다.

 

Controller

EmpSearchController2

@Log4j
@RequiredArgsConstructor
public class EmpSearchController2 extends AbstractController {
	private final EmpService empService;
	
	@Override
	public String doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String searchType = request.getParameter("searchType"); // "검색어" | "" | null
		String searchKeyword = request.getParameter("searchKeyword"); // "검색어" | "" | null
		String gender = request.getParameter("gender");	// "성별" | null
		int salary = 0;
		try {
			salary = Integer.parseInt(request.getParameter("salary")); // "금액" | "" | null
		} catch(NumberFormatException e) {}
		String salaryCompare = request.getParameter("salaryCompare"); // "범위" | null
		
		Map<String, Object> param = new HashMap<>();
		param.put("searchType", searchType);
		param.put("searchKeyword", searchKeyword);
		param.put("gender", gender);
		param.put("salary", salary);
		param.put("salaryCompare", salaryCompare);
		
		List<Map<String, Object>> list = empService.search2(param);
		log.debug("list = " + list);
		request.setAttribute("list", list);
		
		return "emp/search2";
	}
}

 

Service

EmpServiceImpl

(interface 생략)

@Override
public List<Map<String, Object>> search2(Map<String, Object> param) {
    try(SqlSession sqlSession = getSqlSession()) {
        return empDao.search2(sqlSession, param);			
    }
}

 

Dao

EmpDaoImpl

(interface 생략)

@Override
public List<Map<String, Object>> search2(SqlSession sqlSession, Map<String, Object> param) {
    return sqlSession.selectList("emp.search2", param);
}

 

emp-mapper.xml

<select id="search2" resultMap="empMap">
    select
        e.*
    from (
        select
            e.*,
            decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
        from
            emp e
    ) e
    where
        1 = 1
        <if test="searchType != null and searchType != '' and searchKeyword != null and searchKeyword != ''">
            and
            ${searchType} like '%' || #{searchKeyword} || '%'
        </if>
        <if test="gender != null">
            and
            gender = #{gender}
        </if>
        <if test="salary != null and salary != 0">
            <if test="salaryCompare != null and salaryCompare eq 'le'">
                and
                salary <![CDATA[ <= ]]> #{salary}
            </if>
            <if test="salaryCompare != null and salaryCompare eq 'ge'">
                <![CDATA[
                    and
                    salary >= #{salary}			
                ]]>
            </if>
        </if>
</select>

<resultMap type="map" id="empMap">
    <id column="emp_id" property="empId"/>
    <result column="emp_name" property="empName"/>
    <result column="emp_no" property="empNo"/>
    <result column="gender" property="gender"/>
    <result column="email" property="email"/>
    <result column="phone" property="phone"/>
    <result column="dept_code" property="deptCode"/>
    <result column="job_code" property="jobCode"/>
    <result column="sal_level" property="salLevel"/>
    <result column="salary" property="salary"/>
    <result column="bonus" property="bonus"/>
    <result column="manager_id" property="managerId"/>
    <result column="hire_date" property="hireDate"/>
    <result column="quit_date" property="quitDate"/>
</resultMap>

<![CDATA[]]>를 이용하여 '<=', '>='가 파싱 중에 태그로 인식 되지 않고 문자열로 인식되도록 해주어 오류를 해결해주었습니다.

 

 search2.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="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Mybatis실습</title>
<style>
div#emp-container{text-align:center;}
table.tbl-emp{
	margin:0 auto;
	border:1px solid; 
	border-collapse:collapse;
}
table.tbl-emp th, table.tbl-emp td{
	border:1px solid;
	padding:5px;
}
div#search-container{
	padding:15px 0;
}
table#tbl-search { margin:0 auto; }
table#tbl-search th,table#tbl-search td {padding:5px 15px; font-weight: normal; }
table#tbl-search td {text-align:left;}
</style>
</head>
<body>
<div id="emp-container">
	<h2>사원정보 </h2>
	<div id="search-container">
		<form name="empSearchFrm">
			<table id="tbl-search">
				<tr>
					<th colspan="2">
						<select name="searchType">
							<option value="">검색타입</option>
							<!-- required여부를 판단할 value="" 반드시 있어야함.-->
						    <option value="emp_id" ${param.searchType eq 'emp_id' ? 'selected' : ''}>사번</option>
			                <option value="emp_name" ${param.searchType eq 'emp_name' ? 'selected' : ''}>사원명</option>
			                <option value="email" ${param.searchType eq 'email' ? 'selected' : ''}>이메일</option>
			                <option value="phone" ${param.searchType eq 'phone' ? 'selected' : ''}>전화번호</option>
						</select>
						<input type="search" name="searchKeyword" value="${param.searchKeyword}" />
					</th>
				</tr>
				<tr>
					<th>성별</th>
					<td>
						<input type="radio" name="gender" id="gender1" value="남" ${param.gender eq '남' ? 'checked' : ''}/>
						<label for="gender1">남</label>
						<input type="radio" name="gender" id="gender2" value="여" ${param.gender eq '여' ? 'checked' : ''}/>
						<label for="gender2">여</label>
					</td>
				</tr>
			    <!-- 급여기준 -->
			    <tr>
			        <th>급여</th>
			        <td>
			            <input type="number" name="salary" min="0" step="500000" value="${param.salary}"/>
			            <input type="radio" name="salaryCompare" id="salaryCompareGE" value='ge' ${param.salaryCompare eq 'ge' ? 'checked' : ''} />
			            <label for="salaryCompareGE">이상</label>
			            <input type="radio" name="salaryCompare" id="salaryCompareLE" value='le' ${param.salaryCompare eq 'le' ? 'checked' : ''}/>
			            <label for="salaryCompareLE">이하</label>
			        </td>
			    </tr>
				<tr>
					<th colspan="2">
						<input type="submit" value="검색" />
						<input type="button" onclick="location.href='${pageContext.request.contextPath}/emp/search2.do'" value="초기화" />
					</th>
				</tr>
			</table>
		</form>
	</div>
	
	<table class="tbl-emp">
		<tr>
			<th></th><!-- 1부터 넘버링 처리 -->
			<th>사번</th>
			<th>사원명</th>
			<th>주민번호</th><!--뒷6자리는 ******처리-->
			<th>성별</th>
			<th>이메일</th>
			<th>전화번호</th>
			<th>부서코드</th>
			<th>직급코드</th>
			<th>급여레벨</th>
			<th>급여</th><!--원화기호, 세자리마다 콤마표시-->
			<th>보너스율</th><!--percent로 표시-->
			<th>매니져 사번</th>
			<th>입사일</th><!--날짜형식 yyyy/MM/dd-->
			<th>퇴사여부</th>
		</tr>
		<!-- 조회된 데이터가 있는 경우와 없는 경우를 분기처리 하세요 -->
		<c:if test="${empty list}">
			<tr>
				<td colspan="14">조회된 정보가 없습니다.</td>
			</tr>
		</c:if>
		<c:if test="${!empty list}">
			<c:forEach items="${list}" var="emp" varStatus="vs">
				<tr>
					<td>${vs.count}</td>
					<td>${emp.empId}</td>
					<td>${emp.empName}</td>
					<td>
						${fn:substring(emp.empNo, 0, 8)}******
					</td>
					<td>${emp.gender}</td>
					<td>${emp.email}</td>
					<td>${emp.phone}</td>
					<td>${emp.deptCode}</td>
					<td>${emp.jobCode}</td>
					<td>${emp.salLevel}</td>
					<td>
						<fmt:formatNumber value="${emp.salary}" type="currency"/>
					</td>
					<td>
						<fmt:formatNumber value="${emp.bonus}" type="percent"/>
					</td>
					<td>${emp.managerId}</td>
					<td>
						<fmt:formatDate value="${emp.hireDate}" pattern="yyyy/MM/dd"/>
					</td>
					<td><fmt:formatDate value="${emp.quitDate}" pattern="yyyy/MM/dd" /></td>
				</tr>
			</c:forEach>
		</c:if>
	</table>
</div>

</body>
</html>

<choose><when>이용하여 분기처리

<select id="search2" resultMap="empMap">
    select
        e.*
    from (
        select
            e.*,
            decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
        from
            emp e
    ) e
    where
        1 = 1
        <if test="searchType != null and searchType != '' and searchKeyword != null and searchKeyword != ''">
            and
            ${searchType} like '%' || #{searchKeyword} || '%'
        </if>
        <if test="gender != null">
            and
            gender = #{gender}
        </if>
        <!-- 
        <if test="salary != null and salary != 0">
            <if test="salaryCompare != null and salaryCompare eq 'le'">
                and
                salary <![CDATA[ <= ]]> #{salary}
            </if>
            <if test="salaryCompare != null and salaryCompare eq 'ge'">
                <![CDATA[
                    and
                    salary >= #{salary}			
                ]]>
            </if>
        </if> 
        -->
        <!-- if, when의 test 속성에는
                && || < > <= >= 연산자 사용 불가
                and or lt gt le ge 사용할 것	
         -->
        <if test="salary != null and salary != 0">
            <choose>
                <when test="salaryCompare != null and salaryCompare eq 'le'">
                    and
                    salary <![CDATA[ <= ]]> #{salary}
                </when>
                <when test="salaryCompare != null and salaryCompare eq 'ge'">
                    <![CDATA[
                        and
                        salary >= #{salary}			
                    ]]>
                </when>
            </choose>
        </if>
</select>

 

※ if, when의 test 속성에는 && || < > <= >= 연산자 사용 불가 (and or lt gt le ge 사용할 것)

LIST
저작자표시

'Java > └ Mybatis' 카테고리의 다른 글

Mybatis) 동적쿼리 - 심화된 검색 기능 2-1 (foreach 태그)  (0) 2022.08.10
Mybatis) 동적쿼리 - 심화된 검색 기능 1-3 (where 태그)  (0) 2022.08.10
Mybatis) 동적쿼리 - 심화된 검색 기능 1-1 (폼 초기화)  (0) 2022.08.10
Mybatis) 검색 기능 추가하기  (0) 2022.08.10
Mybatis) emp 패키지 시작 - 사원 정보 전체 조회  (0) 2022.08.09

관련글

티스토리툴바