Java EE 정리 30

09 Apr 2019

Reading time ~6 minutes

Java EE 정리 30 - MyBatis(5)

MyBatis를 사용한 다양한 SELECT 예(4)

join + subquery 의 조회
컬럼명 또는 테이블명이 동적일 때 조회
dynamic query : where, if

join + subquery 의 조회

차량 이미지, 제조사, 모델명, 연식, 가격, 옵션을 조회하세요.
가장 마지막에 입력된 차량부터 10건만 조회하세요.

package kr.co.sist.exam.domain;
public class Car {
      
      private String img, maker, model, carYear, price, carOption, hiredate;
      // setter, getter 추가
      ...

필요한 정보를 조회하도록 join과 subquery를 사용하여 쿼리를 작성

SELECT img, maker, model, car_year, price, car_option, 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))
WHERE r_num BETWEEN 1 AND 10;

<!-- exam_mapper2.xml -->
...
      <!-- Car resultMap : 컬럼명과 setter method명이 다른 경우 -->
      <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>
      
      <!-- join + subquery 의 조회 -->
      <select id="joinSubquery" resultMap="carResult">
            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))
            WHERE r_num BETWEEN 1 AND 10
      </select>   
...

public class MyBatisDAO1 {
      
      public List<Car> joinSubquery() {
            List<Car> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("joinSubquery");
            ss.close();
            
            return list;
      }
...

public class MyBatisService1 {
      
      public List<Car> joinSubquery() {
            List<Car> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.joinSubquery();
            
            // 카 옵션이 25자 이상이라면 24까지 보여주고 나머지는  '...'으로 처리
            for(Car car : list) {
                  if(car.getCarOption().length() > 26) {
                        car.setCarOption(car.getCarOption().substring(0,  24)+"...");
                  }
            }
            
            return list;
      }
...

<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0409/join_subquery">JOIN+SUBQUERY을  사용한 조회</a></li>
...

<!-- join_subquery.jsp -->
...
<%
      MyBatisService1 mbs = new MyBatisService1();
      List<Car> list = mbs.joinSubquery();
      
      pageContext.setAttribute("carList", list);
%>
<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>

(컬럼명 또는 )테이블명이 동적일 때 조회

반드시 parameterType이 VO이어야 하고 ${ getter }를 사용해야 한다.
컬럼이 동일형으로 하나가 조회되는 경우에는 동적으로 사용할 수 있으나 컬럼이 여러개가 조회되는 경우에는 동적으로 사용하기 어렵다.
테이블명(emp cp_emp2)이 외부에서 입력되고, 동일 컬럼이 조회되는 경우
- 조회 컬럼 : empno, ename, hiredate, sal

package kr.co.sist.exam.domain;
public class DynamicEmp {
      private String ename, hiredate;
      private int empno, sal;
      // setter, getter
      ...

package kr.co.sist.exam.vo;
public class TableName {
      private String tname;

      public TableName(String tname) { 
            this.tname = tname;
      }

      public String getTname() { // Framework에서 getter로 사용(#{})
            return tname;
      }
}

<!-- exam_mapper2.xml -->
...
      <resultMap type="kr.co.sist.exam.domain.DynamicEmp"  id="dEmpResult">
            <result column="empno" property="empno"/>
            <result column="ename" property="ename"/>
            <result column="sal" property="sal"/>
            <result column="hiredate" property="hiredate"/>
      </resultMap>      
      
      <!-- 컬럼명 또는 테이블명이 동적일 때 조회 -->
      <select id="dynamicTable" resultMap="dEmpResult"  parameterType="kr.co.sist.exam.vo.TableName">
            SELECT empno, ename, hiredate, sal
            FROM ${ tname }
      </select>
...

public class MyBatisDAO1 {
      
      public List<DynamicEmp> dynamicTable(TableName tname) {
            List<DynamicEmp> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("dynamicTable",tname);
            
            return list;
      }
...

public class MyBatisService1 {
      
      public List<DynamicEmp> dynamicTable(TableName tname) {
            List<DynamicEmp> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.dynamicTable(tname);
            
            return list;
      }
...

<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0409/dynamic_table">테이블명이 동적일 때  조회</a></li>
...

<!-- dynamic_table.jsp -->
...
<%
      String tname=request.getParameter("tname");

      if (tname != null) {
            MyBatisService1 mbs = new MyBatisService1();
            List<DynamicEmp> list = mbs.dynamicTable(new  TableName(tname));
            pageContext.setAttribute("tname", tname);
            pageContext.setAttribute("empList", list);
      }
%>
<form action="main.jsp" method="get">
      <input type="hidden" name="page" value="date0409/dynamic_table"/>
      <input type="radio" name="tname" value="cp_emp2" ${ tname eq  'cp_emp2' ? 'checked="checked"' : "" }/>한국본사
      <input type="radio" name="tname" value="emp" ${ tname eq 'emp' ?  'checked="checked"' : "" } />미국지사
      <input type="submit" value="조회" class="btn"/>
</form>
<c:if test="${ not empty empList }">
      <table style="margin:0px auto;">
      <tr>
            <th colspan="4">
                  <c:out value="${ param.tname eq 'emp' ? '미국지사' :  '한국본사' }"/>의
                  사원정보 조회 결과
            </th>
      </tr>
      <tr>
            <th width="80">사원번호</th>
            <th width="120">사원명</th>
            <th width="80">연봉</th>
            <th width="150">입사일</th>
      </tr>
      <c:if test="${ empty empList }">
      <tr>
            <td colspan="4" style="text-align:center;">
                  사원이 존재하지 않습니다.
            </td>
      </tr>
      </c:if>
      <c:forEach var="emp" items="${ empList }">
      <tr align="center">
            <td><c:out value="${ emp.empno }"/></td>
            <td><c:out value="${ emp.ename }"/></td>
            <td><c:out value="${ emp.sal }"/></td>
            <td><c:out value="${ emp.hiredate }"/></td>
      </tr>
      </c:forEach>
      </table>
</c:if>

dynamic query는 조건에 따라 쿼리문이 다르게 만들어 지는 것
MyBatis에서는 다이나믹 쿼리를 OGNL로 생성할 수 있다.
where 노드
- WHERE절을 동적으로 붙여준다.
- 속성이 존재하지 않고 where를 동적으로 써야하는 곳에서 사용한다.

<where>

if 노드
- 조건을 줄 때 사용
- 연산자 크다 작다는 &lt &gt를 사용

<if test="getter 연산자 비교값">
    조건에 맞을 때 들어갈 쿼리
</if>

choose 노드
- when과 같이 사용, 연관된 여러 조건을 비교할 때 사용

<choose>, <when>, <otherwise>

set 노드
- update에서 set을 동적으로 붙일 때 사용

<set>

forEach 노드
- List로 입력된 값을 반복시킬 때(IN)

<forEach>

위 노드들은 쿼리 모든 부분에 사용할 수 있다.
값이 입력될 때에는 반드시 parameterType이 VO이어야 하고 VO에 존재하는 getter일 때만 사용 가능.
- ${ getter }를 사용하기 때문에 반드시 getter 필요
if의 사용 예
- 부서번호가 10~19사이면 10번 부서 사원 조회
- 부서번호가 20~29사이면 20번 부서 사원 조회
- 부서번호가 30~39사이면 30번 부서 사원 조회
- 그렇지 않으면 전체 사원 조회

package kr.co.sist.exam.domain;
public class DynamicIf {
      
      private int empno, deptno;
      private String ename, job, hiredate;
      // setter, getter만 생성
...

package kr.co.sist.exam.vo;
public class DeptNo {
      private int deptno;

      public DeptNo(int deptno) {
            this.deptno = deptno;
      }
      public int getDeptno() {
            return deptno;
      }
}

public class MyBatisDAO1 {
      
      public List<DynamicIf> dynamicIf(DeptNo deptno) {
            List<DynamicIf> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("dynamicIf", deptno);
            
            return list;
      }
...

<!-- exam_mapper2.xml -->
...
      <!-- dynamic query : where, if, choose, foreach -->
      <!-- if의 사용 -->
      <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>
      <select id="dynamicIf" resultMap="ifResult"  parameterType="kr.co.sist.exam.vo.DeptNo">
            SELECT empno, deptno, ename, job, TO_CHAR(hiredate,  'yyyy-mm-dd') hiredate
            FROM emp
            <where>
                  <if test="deptno &gt;= 10 and deptno &lt; 20">
                        deptno=10
                  </if>
                  <if test="deptno &gt;= 20 and deptno &lt; 30">
                        deptno=20
                  </if>
                  <if test="deptno &gt;= 30 and deptno &lt; 40">
                        deptno=30
                  </if>
            </where>
      </select>
...

public class MyBatisService1 {
      
      public List<DynamicIf> dynamicIf(DeptNo deptno) {
            List<DynamicIf> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.dynamicIf(deptno);
            
            return list;
      }
...

<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0409/dynamic_if">dynamic if</a></li>
...

<!-- dynamic_if.jsp --> 
...
<%
      MyBatisService1 mbs = new MyBatisService1();
      String deptno = request.getParameter("deptno");
      
      if(deptno != null) {
            List<DynamicIf> list = mbs.dynamicIf(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="date0409/dynamic_if"/>
<input type="submit" value="사원 조회" class="btn"/><br/>
* 10~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>