728x90
반응형
[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>
<a href="/account/detail/${account.id}?type=deposit" class="btn btn-outline-primary">입금</a>
<a href="/account/detail/${account.id}?type=withdrawal" class="btn btn-outline-primary">출금</a>
</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"%>
728x90
반응형
'Springboot' 카테고리의 다른 글
[Springboot] 27. 계좌 상세 보기 기능(5단계-JSTL 사용 및 페이징 기능) (0) | 2024.08.12 |
---|---|
[Springboot] 26. 계좌 상세 보기 기능(4단계-단위별 포맷) (0) | 2024.08.12 |
[Spring] **24. 개념 보충 공부(3) (0) | 2024.08.12 |
[Spring] **23. 개념 보충 공부(2) (0) | 2024.08.12 |
[Springboot] 22. 계좌 상세 보기 기능(2단계-화면 생성) (0) | 2024.08.12 |