[Springboot] 25. 계좌 상세 보기 기능(3단계-모델, 레포지토리, 쿼리문 작성)

[Springboot] 25. 계좌 상세 보기 기능(3단계-모델, 레포지토리, 쿼리문 작성)

 

 

1. historyRepository.java

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

 

 

2. HistoryAccount.java (HistoryAccountDTO)

package com.tenco.bank.repository.model;

import java.sql.Timestamp;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
@Builder
public class HistoryAccount {
	
	private Integer id;
	private Long amount;
	private Long balance;
	private String sender;
	private String receiver;
	private Timestamp createdAt;

}

 

 

3. xml 쿼리문 작성 (Account, History)

(1) Account.xml

	<select id="findByAccountId"  resultType="com.tenco.bank.repository.model.Account">
		select * from account_tb where number = #{accountId}
	</select>

 

(2) History.xml

<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}
		</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}
		</if>
		<if test="type=='withdraw'">
			select h.id, h.amount, h.w_balance as balance, h.created_at,
			coalesce (cast(da.number as char(10)),'ATM') as reciever,
			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}
		</if>
	</select>

 

*SQL상에서 작성한 쿼리문

select h.id, h.amount, h.w_balance as balance, h.created_at,
	coalesce (cast(da.number as char(10)),'ATM') as reciever,
    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=1;

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=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,
        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=1 or h.d_account_id=1;

 

 

4. AccountService.java (작성한 레포지토리-xml을 사용해 메서드 만들기

(1) AccountId(통장 primary key)로 계좌 찾기(Account 반환)

	/**
	 * 단일 계좌 조회 기능
	 * @param account (pk)
	 * @return 계좌 정보(Account)
	 */
	// 단일 계좌 조회 기능 (accountId 기준)
	// 거래내역은 빈번하게 수정,추가,삭제될 수 있기 때문에 Transaction 처리
	// = 팬텀리드 현상
	@Transactional
	public Account readAccountById(Integer account) {
		Account accountEntity = accountRepository.findByAccountId(account);
		if(accountEntity==null) {
			new DataDeliveryException(Define.NOT_EXIST_ACCOUNT, HttpStatus.INTERNAL_SERVER_ERROR);
		}
		return accountEntity;
	}

 

 

(2) AccountId(통장 primary key)로 입출금/입금/출금 내역 조회하기

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

 

 

5. list.jsp (계좌 상세보기 페이지-유효성, 인증 검사 추가)

	/**
	 * 계좌 상세 보기 페이지
	 * 주소 설계 : http://localhost:800/account/detail/${1}?type=all, deposit, withdraw
	 * @return
	 */
	@GetMapping("/detail/{accountId}")
	public String detail(@PathVariable(name="accountId") Integer accountId, 
			@RequestParam(required=false, name="type") String type, Model model) {
		// 인증 검사
		User principal=(User)session.getAttribute("principal");
		if(principal==null) {
			throw new UnAuthorizedException(Define.NOT_AN_AUTHENTICATED_USER, HttpStatus.UNAUTHORIZED);
		}
		
		Account account = accountService.readAccountById(accountId);
		List<HistoryAccount> historyList= accountService.readHistoryAccountId(type, accountId);
		
		// 유효성 검사
		// arrays.asList() -> array 선언과 동시에 초기화해주는 메서드
		// 사용자가 선택한 타입(입출금/입금/출금 검색)을 검사한다.
		List<String> validTypes=Arrays.asList("all","deposit","withdrawal");
		if(!validTypes.contains(type)) {
			throw new DataDeliveryException("유효하지 않은 접근입니다.", HttpStatus.BAD_REQUEST);
		}
		
		System.out.println("@PathVariable : "+accountId); 
		System.out.println("@RequestParam : "+type);
		
		model.addAttribute("account",account);
		model.addAttribute("historyList", historyList);
		return "account/detail";
	}

 

 

6. detail.jsp (controller/service의 메서드를 사용해 데이터 받아오기)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<!-- 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> 잔액 : ${account.balance} 원
		</div>
		<br>
		
		<div>
			<a href="/account/detail/${account.id}?type=all" class="btn btn-outline-primary">전체</a>&nbsp;
			<a href="/account/detail/${account.id}?type=deposit" class="btn btn-outline-primary">입금</a>&nbsp;
			<a href="/account/detail/${account.id}?type=withdrawal" class="btn btn-outline-primary">출금</a>&nbsp;
		</div>
		
		<table class="table table-striped">
			<thead>
				<tr>
					<th>날짜</th>
					<th>보낸 이</th> <%--sender --%>
					<th>받은 이</th> <%--receiver --%>
					<th>입출금 금액</th> <%--amount --%>
					<th>계좌 잔액</th> <%--balance --%>
				</tr>
			</thead>
			<tbody>
			<c:forEach items="${historyList}" var="history">
				<tr>
					<th>${history.createdAt}</th> <%--연-월-일 시:분:초 --%>
					<th>${history.sender}</th> <%--보낸 이 --%>
					<th>${history.receiver}</th> <%--받은 이 --%>
					<th>${history.amount}</th> <%--입출금 금액 --%>
					<th>${history.balance}</th> <%--현재 계좌 잔액 --%>
				</tr>
			</c:forEach>
			</tbody>
		</table>
		
	</div>
	
</div>
<!-- end of col-sm-8  -->
</div>
</div>

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