Mybatis) 동적쿼리 - 심화된 검색 기능 2-2 (resultMap없이 처리하기)

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

 

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

---

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

 

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");
		String[] deptCodes = request.getParameterValues("deptCode");
		Map<String, Object> param = new HashMap<>();
		param.put("jobCodes", jobCodes);
		param.put("deptCodes", deptCodes);
		
		// 직급/부서별 검색
		List<Map<String, Object>> list = empService.search3(param);
		log.debug("list = " + list);
		
		// 폼에 나열할 직급 리스트 조회
		List<Map<String, Object>> jobCodeList = empService.selectJobList();
		// 폼에 나열할 부서 리스트 조회
		List<Map<String, Object>> deptCodeList = empService.selectDeptList();
		log.debug("jobCodeList = " + jobCodeList);
		log.debug("deptCodeList = " + deptCodeList);
		
		request.setAttribute("jobCodeList", jobCodeList);
		request.setAttribute("deptCodeList", deptCodeList);
		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);			
    }
}

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

 

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);
}

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

 

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>
        <if test="deptCodes != null">
            and
            e.dept_code in
            <foreach collection="deptCodes" item="deptCode" open="(" close=")" separator=",">
                #{deptCode}
            </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="selectDeptList" resultMap="deptMap">
    select * from dept
</select>

<resultMap type="map" id="deptMap">
    <id column="dept_id" property="deptCode"/>
    <result column="dept_title" property="deptTitle"/>
</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>

 

resultMap을 사용하지 않고도 해결 가능!

<select id="selectDeptList" resultType="map">
    select
        dept_id "deptCode",
        dept_title "deptTitle"
    from
        dept
</select>

컬럼 별칭을 지정한다면, ""안은 대소문자를 구분하기 때문에 별칭 그대로를 key값으로 가져다 사용하게 됩니다.

따라서 resultMap을 사용하지 않고 해결하고 싶다면, 별칭을 통해서 해결 할 수 있습니다.

 

---

인턴 사원 조회 (부서 미지정)

 

search3.jsp

    <tr>
        <th>부서</th>
        <td>
            <c:forEach items="${deptCodeList}" var="dept" varStatus="vs">
                <input type="checkbox" name="deptCode" id="deptCode${vs.count}" value="${dept.deptCode}" ${deptList.contains(dept.deptCode) ? 'checked' : ''}/>
                <label for="deptCode${vs.count}">${dept.deptTitle}</label>		
                ${vs.count mod 3 eq 0 ? '<br/>' : ''}				
            </c:forEach>
            <input type="checkbox" name="deptCode" id="deptCode0" value="D0" ${deptList.contains('D0') ? 'checked' : ''}/>
            <label for="deptCode0">인턴</label>		
        </td>
    </tr>

 

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>
        <if test="deptCodes != null">
            and
            nvl(e.dept_code, 'D0') in
            <foreach collection="deptCodes" item="deptCode" open="(" close=")" separator=",">
                #{deptCode}
            </foreach>
        </if>
    </where>
</select>

nvl() 함수를 이용하여 부서가 null인 경우 'D0'으로 처리 되게 하였습니다.