BankApp - 상세 보기 페이지 -2

💡 학습 목표

   list.jsp --> a태그로 상세보기 이동

AccountController.java

	// 상세 보기 페이지
	// http://localhost:80/account/detail/1?type=all,deposit,withdraw
	@GetMapping("/detail/{id}")
	public String detail(@PathVariable Integer id,
			@RequestParam(name = "type", defaultValue = "all", required = false) String type, Model model) {
		// Todo - 주소 설계 추가하기
		
		// 1. 인증 여부 확인
		User user = (User)session.getAttribute(Define.PRINCIPAL);
		if(user == null) {
			throw new UnAuthorizedException("로그인을 먼저 해주세요.", HttpStatus.UNAUTHORIZED);
		}
		
		// 서비스 호출
		Account account = accountService.readAccount(id);
		List<HistoryDto> historyList = accountService.readHistoryListByAccount(id, type);
		model.addAttribute("principal", user);
		model.addAttribute("account", account);
		model.addAttribute("historyList", historyList);
		System.out.println(historyList);
        
		return "account/detail";
	}

AccountService.java

	public Account readAccount(Integer id) {
		// 계좌 존재 여부 확인
		Account accountEntity = accountRepository.findById(id);
		if(accountEntity == null) {
			throw new CustomRestfulException("해당 계좌를 찾을 수 없습니다.", HttpStatus.BAD_REQUEST);
		}
		return accountEntity;
	}

	/**
	 * 단일 계좌에 대한 거래 내역 검색
	 * @param type = [all, deposit, withdraw]
	 * @param id(account pk)
	 * @return History 거래 내역
	 */
	public List<HistoryDto> readHistoryListByAccount(Integer id, String type) {
		
		List<HistoryDto> historyList = historyRepository.findByHistoryType(id, type);
		
		return historyList;
	}

완성 쿼리

-- 1 번계좌 출금 내역 
select h.id, h.amount, h.w_balance,
	   wa.number as sender,
       ifnull(da.number, 'ATM') as receiver 
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 = 1; 

-- 1 번 계좌 입금 내역 
select h.id, h.amount, h.d_balance, h.created_at,  
	   da.number as recevier,
	   ifnull(wa.number, 'ATM') as sender	
from history_tb as h 
left join account_tb as da
on h.d_account_id = da.id
left join account_tb as wa
on h.w_account_id = wa.id 
where h.d_account_id = 1;

select * from history_tb;


-- 1 번 계좌 입 출금 내역 쿼리 
select h.id, h.amount,
case when h.w_account_id = 1 then (h.w_balance)
	 when h.d_account_id = 1 then (h.d_balance)
     end as balance,
     ifnull(wa.number, 'ATM') as sender, 
     ifnull(da.number, 'ATM') as receiver
from history_tb as h
left join account_tb as da
on h.d_account_id = da.id
left join account_tb as wa 
on h.w_account_id = wa.id
where h.d_account_id = 1 or h.w_account_id = 1;

history.xml

		<select id="findByHistoryType" resultType="com.tencoding.bank.dto.HistoryDto">
		<if test="type == 'deposit'">
			select h.id, h.amount, h.d_balance as balance, h.created_at, da.number as sender,
				ifnull(wa.number, 'ATM') as receiver
			from history_tb as h
			left join account_tb as da
			on h.d_account_id = da.id
			left join account_tb as wa
			on h.w_account_id = wa.id
			where h.d_account_id = #{id}
		</if>
		
		<if test="type == 'withdraw'">
			select h.id, h.amount, h.w_balance as balance, h.created_at, wa.number as sender,
				ifnull(da.number, 'ATM') as receiver
			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 = #{id}
		</if>
		
		<if test="type == 'all'">
			select h.id, h.amount, h.created_at,
			case when h.w_account_id = #{id} then (h.w_balance)
				 when h.d_account_id = #{id} then (h.d_balance)
			     end as balance,
			     ifnull(wa.number, 'ATM') as sender,
			     ifnull(da.number, 'ATM') as receiver
			from history_tb as h
			left join account_tb as da
			on h.d_account_id = da.id 
			left join account_tb as wa
			on h.w_account_id = wa.id
			where h.d_account_id = #{id} or h.w_account_id = #{id}
		</if>
	</select>

detail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ include file="/WEB-INF/view/layout/header.jsp"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<div class="col-sm-8">
	<h2>계좌 상세 보기(인증)</h2>
	<h5>어서오세요 환영합니다.</h5>
	<div class="bg-light p-md-5 h-75">
		<div class="bg-light p-md-5 h-75">
			${principal.username} 님의 계좌 <br>
			계좌 번호 : ${account.number} <br>
			잔액 : ${account.balance} 원
		</div>
		<br>
		
		<div>
			<a href="/account/detail/${account.id}">전체</a>&nbsp;
			<a href="/account/detail/${account.id}?type=deposit">입금</a>&nbsp;
			<a href="/account/detail/${account.id}?type=withdraw">출금</a>&nbsp;
		</div>
		<table class="table">
			<thead>
				<tr>
					<th>날짜</th>
					<th>보낸이</th>
					<th>받은이</th>
					<th>입출금 금액</th>
					<th>계좌 잔액</th>
				</tr>
			</thead>
			<tbody>
				<c:forEach var="history" items="${historyList}">
					<tr>
						<td>${history.formatCreatedAt()}</td>
						<td>${history.sender}</td>
						<td>${history.receiver}</td>
						<td>${history.amount}</td>
						<td>${history.formatBalance()}</td>
					</tr>
				</c:forEach>
			</tbody>
		</table>
	</div>
	
	
	
	</div>
	</div>
</div>

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

package com.tencoding.bank.util;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class TimestampUtil {
	public static String timestampToString(Timestamp timestamp) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return sdf.format(timestamp);
	}
}

package com.tencoding.bank.dto;

import java.sql.Timestamp;
import java.text.DecimalFormat;

import com.tencoding.bank.util.TimestampUtil;

import lombok.Data;

@Data
public class HistoryDto {
	private Integer id;
	private Long amount;
	private Long balance;
	private String sender;
	private String receiver;
	private Timestamp createdAt;
	
	// 시간 가공
	public String formatCreatedAt() {
		return TimestampUtil.timestampToString(createdAt);
	}
	
	// 금액 가공
	public String formatBalance() {
		DecimalFormat df = new DecimalFormat("#,###");
		return df.format(balance);
	}
}