[Springboot] 28. 계좌 상세 보기 기능(5단계-정답 코드)

[Springboot] 28. 계좌 상세 보기 기능(5단계-정답 코드)

 

1. detail.jsp (입출금 내역 jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>

<!-- header.jsp  -->
<%@ include file="/WEB-INF/view/layout/header.jsp"%>

<!-- start of content.jsp(xxx.jsp)   -->
<div class="col-sm-8">
	<h2>계좌 상세 보기(인증)</h2>
	<h5>Bank App에 오신걸 환영합니다</h5>
	
	<div class="bg-light p-md-5">
		<div class="user--box">
			${principal.username}님 계좌 <br> 계좌 번호 : ${account.number} <br> 잔액 : <fmt:formatNumber value="${account.balance}" type="currency"/>
		</div>
		<br>
		
		<div>
			<a href="/account/detail/${account.id}?type=all&currentPageNum=1" class="btn btn-outline-primary">전체</a>&nbsp;
			<a href="/account/detail/${account.id}?type=deposit&currentPageNum=1" class="btn btn-outline-primary">입금</a>&nbsp;
			<a href="/account/detail/${account.id}?type=withdrawal&currentPageNum=1" class="btn btn-outline-primary">출금</a>&nbsp;
		</div>
		&nbsp;
		<table class="table table-striped">
			<thead>
				<tr>
					<th>ID</th>
					<th>날짜</th>
					<th>보낸 이</th> <%--sender --%>
					<th>받은 이</th> <%--receiver --%>
					<th>입출금 금액</th> <%--amount --%>
					<th>계좌 잔액</th> <%--balance --%>
				</tr>
			</thead>
			<tbody>
			<c:forEach var="historyAccount"  items="${historyList}">
				<tr>
					<th>${historyAccount.id}</th> 
					<th><fmt:formatDate value="${historyAccount.createdAt}" pattern="yyyy년 MM월 dd일 hh시 mm분 ss초"/></th> <%--연-월-일 시:분:초 --%>
					<th>${historyAccount.sender}</th> <%--보낸 이 --%>
					<th>${historyAccount.receiver}</th> <%--받은 이 --%>
					<th> <fmt:formatNumber value="${historyAccount.amount}" type="currency"/></th> <%--입출금 금액 --%>
					<th> <fmt:formatNumber value="${historyAccount.balance}" type="currency"/></th> <%--현재 계좌 잔액 --%>
				</tr>
			</c:forEach>
			</tbody>
		</table>
		
		<br>
		<div class="d-flex justify-content-center">
			<ul class="pagination">
				<%--previous page link--%>
				<li class="page-item  <c:if test='${currentPage == 1}'> disabled</c:if>"><a class="page-link" href="?type=${type}&page=${currentPage-1}&size=${size}">Previous</a></li>&nbsp
				<%--page numbers--%>
				<c:forEach begin="1" end="${totalPages}" var="page">
					<li class="page-item <c:if test='${page == currentPage}'> active </c:if>">
					<a class="page-link" href="?type=${type}&page=${Page}&size=${size}">${page}</a></li> &nbsp
				</c:forEach>
				<%--next page link--%>
				<li class="page-item <c:if test='${currentPage == totalPages}'> disabled</c:if>">
				<a class="page-link" href="?type=${type}&page=${currentPage+1}&size=${size}">next</a></li>&nbsp
			</ul>
		</div>
		
	</div>
	
</div>
<!-- end of col-sm-8  -->
</div>
</div>

<!-- footer.jsp  -->
<%@ include file="/WEB-INF/view/layout/footer.jsp"%>

 

 

2. history.xml / repository 

-type(입출금/입금/출금...)별 페이지 개수를 도출하기 위한 count(*) 쿼리

<select id="findByAccountIdAndType" resultType="int">
		<if test="type == 'all'">
			select count(*)
			from history_tb as h 
			where h.w_account_id = 1 OR h.d_account_id = 1
		</if>
		<if test="type == 'deposit'">
			select count(*)
			from history_tb as h 
			where h.d_account_id = 1
		</if>
		
		<if test="type == 'withdrawal'">
			select count(*) 
			from history_tb as h 
			where h.w_account_id = 1
		</if>
	</select>

 

	public int countByAccountIdAndType(String type, Integer accountId);

 

 

3. 페이징 메서드

(1) AccountService- readHistoryByAccountId

	/**
	 * 단일 계좌 거래 내역 조회
	 * @param type = [all, deposit, withdrawal]
	 * @param accountId (pk)
	 * @return 전체, 입금, 출금 거래 내역(3가지 타입 반환)
	 */
	@Transactional
	public List<HistoryAccount> readHistoryByAccountId(String type, Integer accountId,int page, int size){
		List<HistoryAccount> list=new ArrayList<>();
		int limit = size;
		int offset = (page-1) * size;
		list=historyRepository.findByAccountIdAndTypeOfHistory(type, accountId,limit,offset);
		return list;
	}

 

 

(2) HistoryRepository / History.xml

public List<HistoryAccount> findByAccountIdAndTypeOfHistory(@Param("type") String type, 
																@Param("accountId") Integer accountId,
																@Param("limit") Integer limit,
																@Param("offset") Integer offset);
	//코드 추가 예정 - 모델을 반드시  1:1 엔터티에 매핑을 시킬 필요는 없다. 
	// 조인 쿼리, 서브쿼리, 동적 쿼리, type=all, de...,accountId...
	public int countByAccountIdAndType(@Param("type")String type, @Param("accountId")Integer accountId);

<select id="findByAccountIdAndTypeOfHistory" resultType="com.tenco.bank.repository.model.HistoryAccount">
		<if test="type == 'all'">
			select h.id, h.amount,
				case
					when h.w_account_id = #{accountId} then (h.w_balance) 
			        when h.d_account_id = #{accountId} then (h.d_balance)
			    end  as balance,
			    coalesce(cast(wa.number as char(10)), 'ATM') as sender, 
			    coalesce(cast(da.number as char(10)), 'ATM') as receiver,
			    h.created_at
			from history_tb as h 
			left join account_tb as wa on h.w_account_id = wa.id
			left join account_tb as da on h.d_account_id = da.id  
			where h.w_account_id = #{accountId} OR h.d_account_id = #{accountId}
			limit #{limit} offset #{offset}
		</if>
		<if test="type == 'deposit'">
			select h.id, h.amount, h.d_balance as balance, h.created_at, 
				coalesce(CAST(wa.number as CHAR(10)) , 'ATM') as sender, 
			    da.number as receiver
			from history_tb as h 
			left join account_tb as wa on wa.id = h.w_account_id
			left join account_tb as da on da.id = h.d_account_id 
			where h.d_account_id = #{accountId}
			limit #{limit} offset #{offset}
		</if>
		
		<if test="type == 'withdrawal'">
			select h.id, h.amount, h.w_balance AS balance, h.created_at, 
				   coalesce(cast(da.number as CHAR(10)), 'ATM')  as receiver, 
			       wa.number as sender 
			from history_tb as h 
			left join account_tb as wa on wa.id = h.w_account_id
			left join account_tb as da on da.id = h.d_account_id 
			where h.w_account_id = #{accountId}
			limit #{limit} offset #{offset}
		</if>
	</select>
	
	<select id="countByAccountIdAndType" resultType="int">
		<if test="type == 'all'">
			select count(*)
			from history_tb as h 
			where h.w_account_id = 1 OR h.d_account_id = 1
		</if>
		<if test="type == 'deposit'">
			select count(*)
			from history_tb as h 
			where h.d_account_id = 1
		</if>
		
		<if test="type == 'withdrawal'">
			select count(*) 
			from history_tb as h 
			where h.w_account_id = 1
		</if>
	</select>

 

 

JSTL <c:if> 태그를 사용하여, 가장 첫 페이지/마지막 페이지에서는 이전/다음으로 넘어갈 수 없게 구현했다.