본문 바로가기
Java/Spring

Spring) 2개 이상 테이블 조회(JOIN) - association, collection

by 박채니 2022. 9. 2.
안녕하세요, 코린이의 코딩 학습기 채니 입니다.
개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

 

게시글 상세조회

 

방법 ① - 쿼리 두 번 날리기 (board, attachment)

 

boardList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<jsp:include page="/WEB-INF/views/common/header.jsp">
	<jsp:param value="게시판" name="title"/>
</jsp:include>
<style>
/*글쓰기버튼*/
input#btn-add{float:right; margin: 0 0 15px;}
</style>
<section id="board-container" class="container">
	<input type="button" value="글쓰기" id="btn-add" class="btn btn-outline-success" 
		onclick="location.href='${pageContext.request.contextPath}/board/boardForm.do'"/>
	<table id="tbl-board" class="table table-striped table-hover">
		<tr>
			<th>번호</th>
			<th>제목</th>
			<th>작성자</th>
			<th>작성일</th>
			<th>첨부파일</th> <!-- 첨부파일 있을 경우, /resources/images/file.png 표시 width: 16px-->
			<th>조회수</th>
		</tr>
		<c:if test="${not empty list}">
			<c:forEach items="${list}" var="list">
				<tr data-no="${list.no}">
					<td>${list.no}</td>
					<td>${list.title}</td>
					<td>${list.memberId}</td>
					<td>
						<fmt:parseDate value="${list.createdAt}" var="createdAt" pattern="yyyy-MM-dd'T'HH:mm" />
						<fmt:formatDate value="${createdAt}" pattern="yy/MM/dd HH:mm"/>
					</td>
					<td>
						<c:if test="${list.attachCount gt 0}">
							<img src="${pageContext.request.contextPath}/resources/images/file.png" width="16px"/>
						</c:if>
					</td>
					<td>${list.readCount}</td>
				</tr>
			</c:forEach>
		</c:if>
		<c:if test="${empty list}">
			<tr>
				<td colspan="6" class="text-center">조회된 게시글이 없습니다.</td>
			</tr>
		</c:if>
	</table>
	<nav>
		${pagebar}
	</nav>
</section> 
<script>
document.querySelectorAll("tr[data-no]").forEach((tr) => {
	tr.addEventListener('click', (e) => {
		//console.log(e.target);
		const tr = e.target.parentElement;
		const no = tr.dataset.no;
		//console.log(no);
		
		if(no) {
			location.href = `${pageContext.request.contextPath}/board/boardDetail.do?no=\${no}`;	
		}
	})
});
</script>
<jsp:include page="/WEB-INF/views/common/footer.jsp"></jsp:include>

 

Controller

BoardController

@GetMapping("/boardDetail.do")
public void boardDetail(@RequestParam int no, Model model) {
    Board board = boardService.selectOneBoard(no);
    log.debug("board = {}", board);
    model.addAttribute("board", board);
}

 

Service

BoardService interface 생략

BoardServiceImpl

@Override
public Board selectOneBoard(int no) {
    Board board = boardDao.selectOntBoard(no);
    List<Attachment> attachments = boardDao.selectAttachmentByBoardNo(no);
    board.setAttachments(attachments);

    return board;
}

 

Dao

BoardDao interface

@Select("select * from board where no = #{no}")
Board selectOntBoard(int no);

@Select("select * from attachment where board_no = #{no}")
List<Attachment> selectAttachmentByBoardNo(int no);

 

boardDetail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<jsp:include page="/WEB-INF/views/common/header.jsp">
	<jsp:param value="게시판 상세보기" name="title"/>
</jsp:include>
<style>
div#board-container{width:400px;}
input, button, textarea {margin-bottom:15px;}
button { overflow: hidden; }
/* 부트스트랩 : 파일라벨명 정렬*/
div#board-container label.custom-file-label{text-align:left;}
</style>
<div id="board-container" class="mx-auto text-center">
	<input type="text" class="form-control" 
		   placeholder="제목" name="boardTitle" id="title" 
		   value="${board.title}" required>
	<input type="text" class="form-control" 
		   name="memberId" 
		   value="${board.memberId}" readonly required>
	
	<c:if test="${not empty board.attachments}">
		<c:forEach items="${board.attachments}" var="attachment" varStatus="vs">
			<button type="button" 
					class="btn btn-outline-success btn-block">
				첨부파일${vs.count} - ${attachment.originalFilename}
			</button>			
		</c:forEach>
	</c:if>
	
    <textarea class="form-control" name="content" 
    		  placeholder="내용" required>${board.content}</textarea>
    <input type="number" class="form-control" name="readCount" title="조회수"
		   value="${board.readCount}" readonly>
	<input type="datetime-local" class="form-control" name="created_at" 
		   value='${board.createdAt}'>
</div>
<jsp:include page="/WEB-INF/views/common/footer.jsp"></jsp:include>

방법 ② - 조인 처리 (board, attachment, member)

 

Controller 동일

 

Service

BoardService interface생략

BoardServiceImpl

@Override
public Board selectOneBoard(int no) {
//		Board board = boardDao.selectOntBoard(no);
//		List<Attachment> attachments = boardDao.selectAttachmentByBoardNo(no);
//		board.setAttachments(attachments);
//		
//		return board;

    return boardDao.selectOneBoardCollection(no);
}

 

Dao

BoardDao interface

Board selectOneBoardCollection(int no);

 

board-mapper.xml

 

association / collection

- resultMap에서만 사용 가능! (1:1 관계 or 1:N 관계를 처리)

- 테이블과 테이블간의 관계가 1:1 → association 사용

- 테이블과 테이블간의 관계가 1:N → collection 사용

- 컬럼명, property mapping 관계 생략 불가

- property는 getter/setter 이름을 작성

 

collection의 ofType

- 요소의 타입을 지정 (List<Attachemtn>의 요소 타입은 Attachment이므로 attachment)

<mapper namespace="com.ce.spring2.board.model.dao.BoardDao">
  
  <select id="selectOneBoardCollection" resultMap="boardMap">
  	select
	    b.*,
	    m.*,
        a.no attach_no,
	    a.board_no,
	    a.original_filename,
	    a.renamed_filename,
	    a.download_count,
	    a.created_at
	from
	    board b left join member m
	        on b.member_id = m.member_id
	    left join attachment a  
	        on b.no = a.board_no
	where
	    b.no = #{no}
  </select>
  
  <resultMap type="board" id="boardMap">
  	<id column="no" property="no"/>
	<result column="title" property="title"/>
	<result column="member_id" property="memberId"/>
	<result column="content" property="content"/>
	<result column="read_count" property="readCount"/>
	<result column="created_at" property="createdAt"/>
	<result column="updated_at" property="updatedAt"/>
	
	<association property="member" javaType="member">
		<id column="member_id" property="memberId"/>
		<result column="name" property="name"/>
	</association>
	
	<collection property="attachments" ofType="attachment">
		<id column="attach_no" property="no"/>
		<result column="board_no" property="boardNo"/>
		<result column="original_filename" property="originalFilename"/>
		<result column="renamed_filename" property="renamedFilename"/>
		<result column="download_count" property="downloadCount"/>
		<result column="attach_created_at" property="createdAt"/>
	</collection>
  </resultMap>
</mapper>

board의 no와 attachment의 no → 컬럼명이 겹치므로 oracle에선 중복되는 컬럼명에 _1이 붙어 결과가 출력되었을 지라도 mybatis에는 컬럼명 그대로 'no'로 전달되기 때문에,

board의 no인지 attachment의 no인지 구분을 하지 못해 오류가 발생합니다.

따라서!! join시 중복되는 컬럼명이 있다면 반드시 별칭을 이용해 중복을 제거 해줘야 합니다.

 

※ Mybatis collection

https://mybatis.org/mybatis-3/sqlmap-xml.html#result-maps

 

mybatis – MyBatis 3 | Mapper XML Files

Mapper XML Files The true power of MyBatis is in the Mapped Statements. This is where the magic happens. For all of their power, the Mapper XML files are relatively simple. Certainly if you were to compare them to the equivalent JDBC code, you would immedi

mybatis.org

 

boardDetail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<jsp:include page="/WEB-INF/views/common/header.jsp">
	<jsp:param value="게시판 상세보기" name="title"/>
</jsp:include>
<style>
div#board-container{width:400px;}
input, button, textarea {margin-bottom:15px;}
button { overflow: hidden; }
/* 부트스트랩 : 파일라벨명 정렬*/
div#board-container label.custom-file-label{text-align:left;}
</style>
<div id="board-container" class="mx-auto text-center">
	<input type="text" class="form-control" 
		   placeholder="제목" name="boardTitle" id="title" 
		   value="${board.title}" required>
	<input type="text" class="form-control" 
		   name="memberId" 
		   value="${board.member.name}" readonly required>
	
	<c:if test="${not empty board.attachments}">
		<c:forEach items="${board.attachments}" var="attachment" varStatus="vs">
			<button type="button" 
					class="btn btn-outline-success btn-block">
				첨부파일${vs.count} - ${attachment.originalFilename}
			</button>			
		</c:forEach>
	</c:if>
	
    <textarea class="form-control" name="content" 
    		  placeholder="내용" required>${board.content}</textarea>
    <input type="number" class="form-control" name="readCount" title="조회수"
		   value="${board.readCount}" readonly>
	<input type="datetime-local" class="form-control" name="created_at" 
		   value='${board.createdAt}'>
</div>
<jsp:include page="/WEB-INF/views/common/footer.jsp"></jsp:include>

콘솔출력값

DEBUG: com.ce.spring2.board.controller.BoardController - board = Board(super=BoardEntity(no=62, title=테스트, memberId=honggd, content=안녕, readCount=0, createdAt=2022-08-30T15:00:30, updatedAt=null), attachCount=0, member=Member(memberId=honggd, password=null, name=홍길동그랑땡, gender=null, birthday=null, email=null, phone=null, address=null, hobby=null, createdAt=null, updatedAt=null, enabled=false), attachments=[Attachment(no=21, boardNo=62, originalFilename=2022-02-24.png, renamedFilename=20220830_150030667_393.png, downloadCount=0, createdAt=null), Attachment(no=22, boardNo=62, originalFilename=루피.jpg, renamedFilename=20220830_150030671_715.jpg, downloadCount=0, createdAt=null)])

잘 출력되는 것을 확인할 수 있습니다.

저작자표시

'Java > Spring' 카테고리의 다른 글

Spring) Ajax - jsonView빈을 통해 응답  (0) 2022.09.05
Spring) 파일 다운로드  (0) 2022.09.04
Spring) 트랜잭션 처리 - @Transactional  (0) 2022.08.31
Spring) 파일 업로드 처리  (0) 2022.08.31
Spring) 게시글 작성 - 파일 업로드 처리를 위한 설정  (0) 2022.08.30

관련글

티스토리툴바