본문 바로가기
Java/└ Mybatis

Mybatis) 동적쿼리 - 심화된 검색 기능 2-1 (foreach 태그)

by 박채니 2022. 8. 10.

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

 

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

직급 리스트 나열 및 검색 기능 구현하기

 

Controller

EmpSearchController3

@Log4j
@RequiredArgsConstructor
public class EmpSearchController3 extends AbstractController {
	private final EmpService empService;
	
	@Override
	public String doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String[] jobCodes = request.getParameterValues("jobCode");
		Map<String, Object> param = new HashMap<>();
		param.put("jobCodes", jobCodes);
		
		// 직급별 검색
		List<Map<String, Object>> list = empService.search3(param);
		log.debug("list = " + list);
		
		// 폼에 나열할 직급 리스트 조회
		List<Map<String, Object>> jobCodeList = empService.selectJobList();
		log.debug("jobCodeList = " + jobCodeList);
		
		request.setAttribute("jobCodeList", jobCodeList);
		request.setAttribute("list", list);
		
		return "emp/search3";
	}
}

 

Service

EmpServiceImpl

(interface 생략)

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

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

 

Dao

EmpDaoImpl

(interface 생략)

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

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

 

emp-mapper.xml

<select id="search3" resultMap="empMap">
    select
        e.*
    from (
        select
            e.*,
            (select job_name from job where job_code = e.job_code) job_name,
            (select dept_title from dept where dept_id = e.dept_code) dept_title,
            decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
        from
            emp e
    ) e	
    <where>
        <if test="jobCodes != null">
            e.job_code in
            <foreach collection="jobCodes" item="jobCode" open="(" close=")" separator=",">
                #{jobCode}
            </foreach>
        </if>
    </where>
</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="dept_title" property="deptTitle"/>
    <result column="job_code" property="jobCode"/>
    <result column="job_name" property="jobName"/>
    <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>

<select id="selectJobList" resultMap="jobMap">
    select * from job
</select>

<resultMap type="map" id="jobMap">
    <id column="job_code" property="jobCode"/>
    <result column="job_name" property="jobName"/>
</resultMap>

<foreach>를 이용해 전달받은 jobCodes를 반복하여 e.job_code in ('~', '~', ...)을 완성시켰습니다.

- collection : 사용할 인자

- item : 꺼낸 인자를 담을 변수명

- open : 시작

- close : 끝

- separator : 구분자

옵션을 이용하여 where절을 구현하였습니다.

 

search3.jsp

<%@page import="java.util.List"%>
<%@page import="java.util.Arrays"%>
<%@ 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>
<%
	String[] jobCodes = request.getParameterValues("jobCode");
	List<String> jobList = jobCodes != null ? Arrays.asList(jobCodes) : null;
	pageContext.setAttribute("jobList", jobList);
%>
<div id="emp-container">
	<h2>사원정보 </h2>
	<div id="search-container">
		<form name="empSearchFrm">
			<table id="tbl-search">
				<tr>
					<th>직급</th>
					<td>
						<c:forEach items="${jobCodeList}" var="job" varStatus="vs">
							<input type="checkbox" name="jobCode" id="jobCode${vs.count}" value="${job.jobCode}" ${jobList.contains(job.jobCode) ? 'checked' : ''}/>
							<label for="jobCode${vs.count}">${job.jobName}</label>
							<c:if test="${vs.count mod 3 eq 0}"><br /></c:if>							
						</c:forEach>
					</td>
				</tr>
				<tr>
					<th colspan="2">
						<input type="submit" value="검색" />
						<input type="button" onclick="location.href='${pageContext.request.contextPath}/emp/search3.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="15">조회된 정보가 없습니다.</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.deptTitle}</td>
					<td>${emp.jobName}</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>

선택한 직급에 대해 'checked'처리를 위해 선택한 직급들을 스크립트 태그를 이용하여 jobCodes에 담은 후,

이를 List로 만들어준 후 jstl로 사용하기 위해 pageContext.setAttribute 해주었습니다.

contains를 이용하여 존재한다면 'checked', 존재하지 않다면 '' 처리해주었습니다.

저작자표시

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

Mybatis) 동적쿼리 - 심화된 검색 기능 2-2 (resultMap없이 처리하기)  (0) 2022.08.10
Mybatis) 동적쿼리 - 심화된 검색 기능 1-3 (where 태그)  (0) 2022.08.10
Mybatis) 동적쿼리 - 심화된 검색 기능 1-2 (<![CDATA[]]>, <choose>분기처리)  (0) 2022.08.10
Mybatis) 동적쿼리 - 심화된 검색 기능 1-1 (폼 초기화)  (0) 2022.08.10
Mybatis) 검색 기능 추가하기  (0) 2022.08.10

관련글

티스토리툴바