아티클 목록

Java EE 정리 29

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

  • subquery 의 조회
  • union 의 조회
  • join 의 조회

subquery 의 조회

  • diary 테이블의 가장 마지막글에서부터 시작번호와 끝번호 사이의 글을 조회해서 게시판으로 보여줄 것
public class DiaryListParam { // VO
      private int startNum, endNum;
      // 인자있는 생성자, setter, getter 생성
      ...
public class DiaryList { // DOMAIN
      private int num;
      private String subject, writer, e_date, w_date;
      // 프레임워크가 생성하는 객체는 getter, setter만 생성
      ...
<!-- exam_mapper2.xml -->
...
<mapper namespace="kr.co.sist.exam2">
      <!--  DiaryList에 대한 Mapper 설정 -->
      <resultMap type="kr.co.sist.exam.domain.DiaryList" id="dlResult">
            <result column="num" property="num"/>
            <result column="subject" property="subject"/>
            <result column="writer" property="writer"/>
            <result column="e_date" property="e_date"/>
            <result column="w_date" property="w_date"/>
      </resultMap>
 
      <!-- subquery 의 조회 -->
      <select id="diaryCnt" resultType="int">
            SELECT COUNT(*) cnt
            FROM diary
      </select>
      
      <select id="subquery" resultMap="dlResult"  parameterType="kr.co.sist.exam.vo.DiaryListParam">
            SELECT r_num, num,  writer, subject, e_year||'-'||e_month||'-'||e_date e_date,
                  TO_CHAR(w_date, 'yyyy-mm-dd hh24:mi') w_date
            FROM (SELECT num, subject, writer, e_year, e_month, e_date,  w_date,
                        ROW_NUMBER() OVER( ORDER BY w_date DESC )  r_num
                  FROM diary)
            WHERE r_num between #{ startNum } and #{ endNum }
      </select>
...
...
public class MyBatisDAO1 {
      
      public int diaryTotalCount() {
            int cnt = 0;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            cnt = ss.selectOne("diaryCnt");
            ss.close();
            
            return cnt;
      }
      public List<DiaryList> subquery(DiaryListParam dlpvo) {
            List<DiaryList> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("subquery", dlpvo);
            
            return list;
      }
...
  • 게시판처럼 보여주기 위해 Service에서 totalCount, pageScale, totalPage, startNum, endNum을 구한다.
...
public class MyBatisService1 {
      
      ////////////////////// 게시판의 리스트  /////////////////////////////////
      public int totalCount() {
            int cnt = 0;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            cnt = mb_dao.diaryTotalCount();
            
            return cnt;
      }
      
      // 한 화면에 보여줄 게시물의 갯수
      public int pageScale() {
            return 10;
      }
      
      // 총 페이지 수
      public int totalPage(int totalCnt, int pageScale) {
            int totalPage = 0;
            
            totalPage = totalCnt/pageScale;
            
            if (totalCnt%pageScale != 0) {
                  totalPage += 1;
            }
            
            return totalPage;
      }
      
      // 시작번호
      public int startNum(String currentPage, int pageScale) {
            int startNum = 1;
            
            if (currentPage != null) {
                  int tempPage = Integer.parseInt(currentPage);
                  startNum = tempPage*pageScale - pageScale + 1;
            }
            
            return startNum;
      }
      
      // 끝번호
      public int endNum(int startNum, int pageScale) {
            return startNum + pageScale - 1;
      }
 
      public List<DiaryList> diaryList(DiaryListParam dlpvo) {
            List<DiaryList> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.subquery(dlpvo); // 시작번호, 끝번호 사이 글을 가져옴
            
            return list;
      }
...
  • 메뉴에 추가
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0408/subquery">서브쿼리를 사용한  조회</a></li>
...
  • 결과창
<!-- subquery.jsp -->
...
<%
      String currentPage = request.getParameter("currentPage");
      MyBatisService1 mbs1 = new MyBatisService1();
 
      int totalCnt = mbs1.totalCount();
      int pageScale = mbs1.pageScale();
      int totalPage = mbs1.totalPage(totalCnt, pageScale);
      int startNum = mbs1.startNum(currentPage, pageScale);
      int endNum = mbs1.endNum(startNum, pageScale);
      
      DiaryListParam dlpvo = new DiaryListParam(startNum, endNum);
      List<DiaryList> list = mbs1.diaryList(dlpvo);
      pageContext.setAttribute("diaryList", list);
%>
<table style="border:1px solid #333;">
<tr>
      <th width="80">번호</th>
      <th width="400">제목</th>
      <th width="120">작성자</th>
      <th width="120">이벤트일</th>
      <th width="120">작성일</th>
</tr>
<c:if test="${ empty diaryList }">
<tr>
      <td colspan="5">작성된 글이 없습니다.</td>
</tr>
</c:if>
<c:forEach items="${ diaryList }" var="diary">
<c:set var="i" value="${ i+1 }"/>
<tr>
      <td><c:out value="${ i }"/></td>
      <td><c:out value="${ diary.subject }"/></td>
      <td><c:out value="${ diary.writer }"/></td>
      <td><c:out value="${ diary.e_date }"/></td>
      <td><c:out value="${ diary.w_date }"/></td>
</tr>
</c:forEach>
</table>

01

union 의 조회

  • union 조회를 위해 가데이터 테이블 생성
CREATE TABLE union_test AS
(SELECT empno, ename, job, sal, hiredate, deptno
FROM emp);
 
ALTER TABLE union_test ADD retired CHAR(1);
 
UPDATE union_test
SET retired='Y'
WHERE TO_CHAR(hiredate, 'yyyymm') BETWEEN '198102' AND '198110';
 
 
UPDATE union_test
SET retired='N'
WHERE NOT TO_CHAR(hiredate, 'yyyymm') BETWEEN '198102' AND '198110';
 
COMMIT;
  • 퇴사한 사원과 재직중인 사원을 구분하여 조회후 union 처리
package kr.co.sist.exam.domain;
public class Union {
      private String ename, job, hiredate, retired;
      private int empno, sal, deptno;
      // domain이므로 getter, setter 생성
      ...
<!-- exam_mapper2.xml -->
...
      <!-- union에서 사용되는 resultMap -->
      <resultMap type="kr.co.sist.exam.domain.Union" id="unionResult">
            <result column="empno" property="empno"/>
            <result column="sal" property="sal"/>
            <result column="deptno" property="deptno"/>
            <result column="ename" property="ename"/>
            <result column="job" property="job"/>
            <result column="hiredate" property="hiredate"/>
            <result column="retired" property="retired"/>
      </resultMap>
      
      <!-- union 의 조회 -->
      <select id="union" resultMap="unionResult" >
            SELECT empno, sal, deptno, ename, job, retired
            FROM union_test
            WHERE retired='Y'
            UNION ALL
            SELECT empno, sal, deptno, ename, job, retired
            FROM union_test
            WHERE retired='N'
      </select>
...
public class MyBatisDAO1 {
      
      public List<Union> union() {
            List<Union> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("union");
            ss.close();
            
            return list;
      }
...
public class MyBatisService1 {
      
      public List<Union> union() {
            List<Union> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.union();
            
            return list;
      }
      
...
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0408/union">UNION를 사용한 조회</a></li>
...
<!-- union.jsp -->
...
<%
      MyBatisService1 mbs1 = new MyBatisService1();
      List<Union> list = mbs1.union();
      pageContext.setAttribute("list", list);
%>
<table border="1">
      <tr>
            <th width="60">사원번호</th>
            <th width="120">사원명</th>
            <th width="120">직무</th>
            <th width="80">연봉</th>
            <th width="150">입사일</th>
            <th width="60">부서번호</th>
            <th width="60">퇴사여부</th>
      </tr>
      <c:if test="${ empty list }">
      <tr>
            <td colspan="7">사원이 존재하지 않습니다.</td>
      </tr>
      </c:if>
      <c:forEach items="${ list }" var="emp">
      <tr>
            <td><c:out value="${ emp.empno }"/></td>
            <td><c:out value="${ emp.ename }"/></td>
            <td><c:out value="${ emp.job }"/></td>
            <td><c:out value="${ emp.sal }"/></td>
            <td><c:out value="${ emp.hiredate }"/></td>
            <td><c:out value="${ emp.deptno }"/></td>
            <td><c:out value="${ emp.retired eq 'N' ? '재직자' :  '퇴사자' }"/></td>
      </tr>
      </c:forEach>
</table>

02

join 의 조회

  • 매니저번호를 입력받아 매니저가 관리하는 사원들의 사원번호, 사원명, 연봉, 입사일, 부서번호, 부서명, 위치를 조회
package kr.co.sist.exam.domain;
public class EmpJoin {
      
      private int empno, sal, deptno;
      private String ename, hiredate, dname, loc;
      // getter, setter만 생성
      ...
  • 양쪽에 있는 것만 가지고 하는 INNER JOIN
<!-- exam_mapper2_.xml -->
...
      <!--  join resultMap -->
      <resultMap type="kr.co.sist.exam.domain.EmpJoin" id="joinResult">
            <result column="empno" property="empno"/>
            <result column="ename" property="ename"/>
            <result column="sal" property="sal"/>
            <result column="hiredate" property="hiredate"/>
            <result column="deptno" property="deptno"/>
            <result column="dname" property="dname"/>
            <result column="loc" property="loc"/>
      </resultMap>
      
      <!-- join 의 조회 -->
      <select id="join" resultMap="joinResult" parameterType="Integer">
            SELECT e.empno, e.ename, e.sal, e.hiredate, d.deptno,  d.dname, d.loc
            FROM emp e, dept d
            WHERE d.deptno = e.deptno AND e.mgr = #{ mgr }
      </select>
...
public class MyBatisDAO1 {
      
      public List<EmpJoin> join(int mgr) {
            List<EmpJoin> list = null;
            
            SqlSession ss =  MyBatisDAO.getInstance().getSessionFactory().openSession();
            list = ss.selectList("join", mgr);
            ss.close();
            
            return list;
      }
...
public class MyBatisService1 {
      
      public List<EmpJoin> join(int mgr) {
            List<EmpJoin> list = null;
            
            MyBatisDAO1 mb_dao = new MyBatisDAO1();
            list = mb_dao.join(mgr);
            
            return list;
      }
      ...
<!-- main_menu.jsp -->
...
<li><a href="main.jsp?page=date0408/join">JOIN을 사용한 조회</a></li>
...
<!-- join.jsp -->
...
<%
      String mgr = request.getParameter("mgr");
      if(mgr != null) {
            int intMgr = Integer.parseInt(mgr);
            MyBatisService1 mbs = new MyBatisService1();
            List<EmpJoin> list = mbs.join(intMgr);
            
            pageContext.setAttribute("empList", list);
      }
%>
<div>
      <c:set var="mgr" value="7566,7698,7839,7902,7788"/>
      <c:forTokens var="mgr" items="${ mgr }" delims="," >
            [<a href="main.jsp?page=date0408/join&mgr=${ mgr }">
                  <c:out value="${ mgr }"/>
            </a>]
      </c:forTokens>
</div>
<div>
      <table border="1">
            <tr>
                  <th width="60">사원번호</th>
                  <th width="100">사원명</th>
                  <th width="80">연봉</th>
                  <th width="150">입사일</th>
                  <th width="60">부서번호</th>
                  <th width="100">부서명</th>
                  <th width="80">위치</th>
            </tr>
            <c:if test="${ empty empList }">
                  <tr>
                        <td colspan="7">조회된 결과가 없습니다.</td>
                  </tr>
            </c:if>
            <c:forEach items="${ empList }" var="emp">
                  <tr>
                        <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>
                        <td><c:out value="${ emp.deptno }"/></td>
                        <td><c:out value="${ emp.dname }"/></td>
                        <td><c:out value="${ emp.loc }"/></td>
                  </tr>
            </c:forEach>
      </table>
</div>

03

댓글