Java EE 정리 29
08 Apr 2019
Reading time ~5 minutes
Java EE 정리 29 - MyBatis(4)
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>
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>
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>
