Java EE 정리 31
10 Apr 2019
Reading time ~7 minutes
Java EE 정리 31 - MyBatis(6)
MyBatis를 사용한 다양한 SELECT 예(5)
- Dynamic Query : choose, foreach
- Procedure 사용
Dynamic Query : choose
- if 노드는 단일 조건에 맞을 때에 쿼리를 실행할 때 사용
- choose 노드는 여러개의 조건을 비교할 때 사용
<choose>
<when test="조건식1">
조건이 맞을 때 실행될 코드
</when>
<when test="조건식2">
조건이 맞을 때 실행될 코드
</when>
...
<otherwiser>
모든 조건이 맞지 않을 때
</otherwise>
</choose>
- choose 사용 예
- 부서번호가 10~19사이면 10번 부서 사원 조회
- 부서번호가 20~29사이면 20번 부서 사원 조회
- 그렇지 않으면 30번 부서 사원 조회
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0410/dynamic_choose">dynamic choose</a></li>
...
<!-- exam_mapper2.xml -->
...
<mapper namespace="kr.co.sist.exam2">
<!-- 어제 if 사용하며 만든 resultMap 재사용 -->
<resultMap type="kr.co.sist.exam.domain.DynamicIf" id="ifResult">
<result column="empno" property="empno"/>
<result column="deptno" property="deptno"/>
<result column="ename" property="ename"/>
<result column="job" property="job"/>
<result column="hiredate" property="hiredate"/>
</resultMap>
<!-- dynamic choose -->
<select id="dynamicChoose" resultMap="ifResult" parameterType="kr.co.sist.exam.vo.DeptNo">
SELECT empno, deptno, ename, job, TO_CHAR(hiredate, 'yyyy-mm-dd') hiredate
FROM emp
<where>
<choose>
<when test="deptno >= 10 and deptno < 20">
deptno=10
</when>
<when test="deptno >= 20 and deptno < 30">
deptno=20
</when>
<otherwise>
deptno=30
</otherwise>
</choose>
</where>
</select>
...
- 중복된 id가 있더라도 mapper의 namespace를 사용하여 식별이 가능하다.
public class MyBatisDAO1 {
public List<DynamicIf> dynamicChoose(DeptNo deptno) {
List<DynamicIf> list = null;
SqlSession ss = MyBatisDAO.getInstance().getSessionFactory().openSession();
list = ss.selectList("kr.co.sist.exam2.dynamicChoose", deptno);
return list;
}
...
public class MyBatisService1 {
public List<DynamicIf> dynamicChoose(DeptNo deptno) {
List<DynamicIf> list = null;
MyBatisDAO1 mb_dao = new MyBatisDAO1();
list = mb_dao.dynamicChoose(deptno);
return list;
}
...
<!-- dynamic_choose.jsp -->
...
<%
MyBatisService1 mbs = new MyBatisService1();
String deptno = request.getParameter("deptno");
if(deptno != null) {
List<DynamicIf> list = mbs.dynamicChoose(new DeptNo(Integer.parseInt(deptno)));
pageContext.setAttribute("empList", list);
}
%>
<form name="frm" action="main.jsp">
<label>부서번호</label>
<input type="text" name="deptno" class="inputBox"/>
<input type="hidden" name="page" value="date0410/dynamic_choose"/>
<input type="submit" value="사원 조회" class="btn"/><br/>
* 10~30번 부서의 사원을 조회, 없는 부서번호가 입력되면 30번 부서 사원을 조회
</form>
<div>
<c:if test="${ not empty param.deptno }">
<div>
${ param.deptno } 번 부서 사원조회결과
</div>
<table border="1">
<tr>
<th width="80">번호</th>
<th width="80">사원번호</th>
<th width="120">사원명</th>
<th width="80">부서번호</th>
<th width="100">직무</th>
<th width="150">입사일</th>
</tr>
<c:if test="${ empty empList }">
<tr>
<td colspan="6" align="center">
입력하신 부서에는 부서가 존재하지 않거나 사원이 없습니다.
</td>
</tr>
</c:if>
<c:forEach var="emp" items="${ empList }">
<c:set var="i" value="${ i+1 }"/>
<tr>
<td><c:out value="${ i }"/></td>
<td><c:out value="${ emp.empno }"/></td>
<td><c:out value="${ emp.ename }"/></td>
<td><c:out value="${ emp.deptno }"/></td>
<td><c:out value="${ emp.job }"/></td>
<td><c:out value="${ emp.hiredate }"/></td>
</tr>
</c:forEach>
</table>
</c:if>
</div>
Dynamic Query : foreach
- 배열이나 리스트에 존재하는 값을 반복시킬 때 사용
- IN 쓸 때 좋다
- 대표적으로 checkbox 를 사용할 때
<forEach collection="배열이나 리스트명" item="객체명" open="시작문자" close="끝문자" separator="값구분문자">
${ getter명 }
</forEach>
SELECT 컬럼명, ...
FROM 테이블명
WHERE 컬럼명 IN
<forEach open="(" close=")" collection="배열|리스트를반환하는getter명"
item="객체명" separator=",">
#{ 객체명 }
</forEach>
- forEach 사용 예
- 제조사가 조회되면 제조사별 차량 조회
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0410/dynamic_foreach">dynamic forEach</a></li>
...
package kr.co.sist.exam.vo;
import java.util.List;
public class CarVO {
private List<String> makerList;
public CarVO(List<String> makerList) {
this.makerList = makerList;
}
public List<String> getMakerList() {
return makerList;
}
}
<!-- exam_mapper2.xml -->
...
<resultMap type="kr.co.sist.exam.domain.Car" id="carResult">
<result column="img" property="img"/>
<result column="maker" property="maker"/>
<result column="car_year" property="carYear"/>
<result column="price" property="price"/>
<result column="car_option" property="carOption"/>
<result column="hiredate" property="hiredate"/>
</resultMap>
...
<!-- dynamic forEach -->
<select id="dynamicForEach" resultMap="carResult" parameterType="kr.co.sist.exam.vo.CarVO">
SELECT img, maker, model, car_year, price, car_option,
TO_CHAR(hiredate, 'yyyy-mm-dd hh24:mi') hiredate
FROM (SELECT img, maker, model, car_year, price, car_option, hiredate,
ROW_NUMBER() OVER(ORDER BY hiredate DESC) r_num
FROM (SELECT rownum r, car_img img, maker, cmo.model, car_year, price, car_option, hiredate
FROM car_model cmo, car_maker cma
WHERE cmo.model = cma.model
<if test="makerList != null">
AND cma.maker IN
<foreach collection="makerList" item="maker"
open="(" close=")" separator=",">
#{maker}
</foreach>
</if>))
</select>
...
public class MyBatisDAO1 {
public List<Car> dynamicForEach(CarVO cv) {
List<Car> list = null;
SqlSession ss = MyBatisDAO.getInstance().getSessionFactory().openSession();
list = ss.selectList("kr.co.sist.exam2.dynamicForEach", cv);
return list;
}
...
public class MyBatisService1 {
public List<Car> dynamicForEach(String[] makerArr) {
List<Car> list = null;
// 입력되는 배열의 값이 존재한다면 리스트에 추가
List<String> makerList = null;
if (makerArr != null) {
makerList = new ArrayList<String>();
for(String temp : makerArr) {
makerList.add(temp);
}
}
CarVO cv = new CarVO(makerList);
MyBatisDAO1 mb_dao = new MyBatisDAO1();
list = mb_dao.dynamicForEach(cv);
return list;
}
...
<!-- dynamic_foreach.jsp -->
...
<%
String[] makerArr = request.getParameterValues("maker");
MyBatisService1 mbs = new MyBatisService1();
List<Car> list = mbs.dynamicForEach(makerArr);
pageContext.setAttribute("carList", list);
%>
<div style="margin-top:25px;">
<form action="main.jsp" method="get" id="frm">
<input type="hidden" name="page" value="date0410/dynamic_foreach"/>
<c:set var="maker" value="현대,기아,삼성,쌍용,BMW,BENZ,AUDI"/>
제조사 선택
<c:forTokens items="${ maker }" delims="," var="maker">
<input type="checkbox" value="${ maker }" name="maker"/>
<c:out value="${ maker }" escapeXml="false"/>
</c:forTokens>
<input type="submit" value="조회"/>
</form>
</div>
<div style="margin-top:25px;">
<table style="border-top:2px solid #333; border-bottom:1px solid #333; margin:0px auto;">
<tr>
<th width="80">이미지</th>
<th width="400">차량정보</th>
<th width="150">등록일</th>
</tr>
<c:if test="${ empty carList }">
<tr>
<td colspan="3" style="text-align:center;">등록된 차량이 존재하지 않습니다..</td>
</tr>
</c:if>
<c:forEach var="car" items="${ carList }">
<tr>
<td><img src="http://localhost:8080/mybatis_prj/date0409/images/<c:out value="${ car.img }"/>" style="width:80px; height:60px;"/></td>
<td>
<div>
<c:out value="${ car.maker }"/> <c:out value="${ car.model }"/>
</div>
<div>
<c:out value="${ car.carYear }"/>년식 <c:out value="${ car.price }"/>
</div>
<div>
<c:out value="${ car.carOption }"/>
</div>
</td>
<td><c:out value="${ car.hiredate }"/></td>
</tr>
</c:forEach>
</table>
</div>
Procedure 사용
- 프로시저
- 반환형은 없고 아웃 파라미터 존재
CREATE OR REPLACE PROCEDURE 프로시저명(
매개변수 IN 데이터형, 매개변수 IN 데이터형,
매개변수 OUT 데이터형)
IS
...
BEGIN
...
END;
/
- select노드로 호출한다.
- VO에는 입력되어 사용할 값과 저장할 값을 모두 가지고 있어야 한다.
<select id="id명" parameterType="VO" statementType="CALLABLE">
{ call 프로시저명(
#{ getter명,mode=IN },
#{ getter명,mode=IN },
#{ setter명,mode=OUT,
jdbcType=DB에서사용하는데이터형,
javaType=Java에서저장할데이터형 }) }
</select>
// insert를 하더라도 프로시저를 호출해야 하기 때문에 selectOne 사용
SqlSession.selectOne("id명", VO);
// VO.getOOO;로 out parameter에 대한 값을 얻는다.
- Procedure 사용 예
- 11월 8일에 만든 프로시저 재사용
-- 사원번호, 사원명, 연봉, 직급를 입력받아 test_proc테이블에
-- 추가하는 PROCEDURE를 작성하세요.
-- 제약들
-- * 사원번호는 1~9999 사이의 값만 입력되어야 한다.
-- * 연봉 2500~8000 사이만 입력되어야한다.
-- 2500 이하라면 2500으로 8000 이상이라면 8000으로 설정하여 추가
-- * 직급은 사원, 주임, 대리, 과장, 차장, 부장, 이사가 입력되었을 때만
-- 추가한다.
-- * 사원명은 영어인 경우 첫자를 대문자로 변경하여 추가
-- * 중복된 사원 번호가 입력되면 예외로 처리한다.
CREATE OR REPLACE PROCEDURE insert_test_proc(
empno NUMBER,
ename VARCHAR2,
sal NUMBER,
job VARCHAR2,
msg OUT VARCHAR2,
row_cnt OUT NUMBER
)
IS
-- in parameter는 값 변경이 안되기 때문에 값이 변경되는 경우
-- 새로운 변수를 생성해야 한다
temp_sal NUMBER := sal;
BEGIN
row_cnt := 0;
IF empno BETWEEN 1 AND 9999 THEN
IF temp_sal < 2500 THEN
temp_sal := 2500;
ELSIF temp_sal > 8000 THEN
temp_sal := 8000;
END IF;
IF job IN ('사원', '주임', '대리', '과장', '차장', '부장', '이사') THEN
INSERT INTO test_proc(empno, ename, hiredate, sal, job)
VALUES(empno, INITCAP(ename), SYSDATE, temp_sal, job);
row_cnt := SQL%ROWCOUNT;
COMMIT;
msg := empno||'번 사원 정보가 추가되었습니다.';
ELSE
msg := job||'은 입력가능한 직급이 아닙니다';
END IF;
ELSE
msg := empno||'사원번호는 1에서 9999사이가 아닙니다.';
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
msg := empno||'번 사원번호는 이미 존재합니다.';
WHEN OTHERS THEN
IF sqlcode = -12899 THEN
msg := ename||'명은 한글 3자, 영어 10자를 초과하였습니다.';
END IF;
END;
/
- 프로시저 사용법
- 바인드 변수를 사용해서 out parameter로 넣음
- 수행 후 결과를 받아서 사용
- MyBatis에서 프로시저 사용 예
- 프로시저를 호출할 때 in parameter값과 out parameter값은 모두 VO에 함께 들어가 있어야 한다.
- in parameter => #{ getter, mode=IN}
- out parameter
- 단일 데이터 형일 때 => #{ setter, mode=OUT, jdbcType=, javaType= }
package kr.co.sist.exam.vo;
/**
* Procedure에 사용되는 VO는
* in parameter와 out parameter 값을
* 모두 갖고 있어야 한다.
*/
public class TestProcVO {
private String ename, job, msg;
private int empno, sal, rowCnt;
// 인자있는 생성자, getter, setter
...
<!-- exam_mapper2.xml -->
...
<!-- procedure 호출 -->
<!-- insert : insert_test_proc -->
<select id="insertProcedure" parameterType="kr.co.sist.exam.vo.TestProcVO"
statementType="CALLABLE">
{
call insert_test_proc(
#{ empno,mode=IN },
#{ ename,mode=IN },
#{ sal,mode=IN },
#{ job,mode=IN },
#{ msg,mode=OUT,jdbcType=VARCHAR,javaType=String },
#{ rowCnt,mode=OUT,jdbcType=NUMERIC,javaType=int })
}
</select>
...
- 사용한 VO를 다시 반환해도 괜찮고 out parameter를 담은 Domain을 만들어서 반환해도 괜찮다.
public class MyBatisDAO1 {
public TestProcVO insertProc(TestProcVO tpvo) {
SqlSession ss = MyBatisDAO.getInstance().getSessionFactory().openSession();
System.out.println(tpvo.getMsg());
ss.selectOne("insertProcedure",tpvo);
System.out.println(tpvo.getMsg());
ss.close();
return tpvo;
}
...
- 단위 테스트
public static void main(String[] args) {
MyBatisDAO1 md = new MyBatisDAO1();
TestProcVO tpvo = new TestProcVO("오영근", "사원", "", 3322, 3000, 0);
md.insertProc(tpvo);
}
public class MyBatisService1 {
public TestProcVO insertProcedure(TestProcVO tpvo) {
MyBatisDAO1 mb_dao = new MyBatisDAO1();
tpvo = mb_dao.insertProc(tpvo);
return tpvo;
}
...
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0410/insert_procedure">프로시저 사용</a></li>
...
<!-- insert_procedure.jsp -->
...
<%
String empno = request.getParameter("empno");
String ename = request.getParameter("ename");
String sal = request.getParameter("sal");
String job = request.getParameter("job");
if(empno != null) {
MyBatisService1 mbs = new MyBatisService1();
TestProcVO tp = new TestProcVO(ename, job, "",
Integer.parseInt(empno), Integer.parseInt(sal), 0);
tp = mbs.insertProcedure(tp);
pageContext.setAttribute("tp", tp);
}
%>
<div style="margin-top:25px;">
<form action="main.jsp" method="get" id="frm">
<input type="hidden" name="page" value="date0410/insert_procedure"/>
사원번호:
<input type="text" name="empno" class="inputBox"/><br/>
사원명:
<input type="text" name="ename" class="inputBox"/><br/>
직급:
<input type="text" name="job" class="inputBox"/><br/>
연봉:
<input type="text" name="sal" class="inputBox"/><br/>
<input type="submit" value="사원추가" class="btn"/>
</form>
</div>
<div style="margin-top:25px;">
<c:if test="${ not empty param.empno }">
<c:out value="${ tp.rowCnt }"/>건 수행 되었습니다.<br/>
<c:out value="${ tp.msg }"/>
</c:if>
</div>
