[Bank App] 13강 계좌 상세보기

 

주소 설계 계좌 상세보기

/account/1
/account/1?gubun=all
/account/1?gubun=withdraw
/account/1?gubun=deposit

스프링 컨트롤러 쿼리스트링 디폴트 값 받는법

---

쿼리 만들기

<< account id 로 조회

1. 계좌 번호
2. 계좌 잔액

select * from ACCOUNT_TB where id = 1;

select at.id, at.number, at.balance, at.user_id, (select fullname from user_tb ut where ut.id=at.user_id) fullname
from account_tb at
where at.id=1;

3. 전체 거래 조회

select * from history_tb where w_account_id = 1 or d_account_id=1;

4. 입금 거래 조회

select * from history_tb where d_account_id=1;

5. 출금 거래 조회

select * from history_tb where w_account_id=1;

---

출금 내역 보기

select ht.amount, ht.w_balance, ht.d_balance, wat.number sender, 'ATM' receiver, ht.created_at
from history_tb ht
inner join account_tb wat
on ht.w_account_id = wat.id
where ht.w_account_id=1;

입금 내역 보기

select ht.amount, ht.w_balance, ht.d_balance, dat.number sender , 'ATM' receiver, ht.created_at
from history_tb ht
inner join account_tb dat
on ht.d_account_id = dat.id
where ht.d_account_id=1;

입출금 내역 보기

select ht.amount, ht.w_balance, ht.d_balance, nvl(wat.number, 'ATM') sender, nvl (dat.number,  'ATM') receiver, ht.created_at

from history_tb ht
left outer join account_tb wat
on ht.w_account_id=wat.id
left outer join account_tb dat
on ht.d_account_id = dat.id

where ht.w_account_id =1 or ht.d_account_id=1;

NVL 함수는 Oracle 데이터베이스에서 사용되는 함수로, 첫 번째 매개변수가 NULL인 경우 두 번째 매개변수로 대체값을 반환

---

MyBatis 매퍼 만들기

account.xml

   <select id="findByIdWithUser" resultType="shop.mtcoding.bankapp.dto.account.AccountDetailResDto">
            select at.id, at.number, at.balance, at.user_id, (select fullname from user_tb ut where ut.id=at.user_id) fullname
            from account_tb at
            where at.id=#{id};
        </select>

 

history.xml

 <select id="findByGubun" resultType="shop.mtcoding.bankapp.dto.history.HistoryResDto">

            <if test = "gubun == 'withdraw'">
                select ht.amount, 
                    case when ht.w_account_id = #{accountId} then ht.w_balance
                    when ht.d_account_id = #{accountId} then ht.d_balance
                    end as balance,
                wat.number sender, 'ATM' receiver, ht.created_at
                from history_tb ht
                inner join account_tb wat
                on ht.w_account_id = wat.id
                where ht.w_account_id=#{accountId};
            </if>
                
            <if test = "gubun == 'deposit'">
                select ht.amount, 
                    case when ht.w_account_id = #{accountId} then ht.w_balance
                    when ht.d_account_id = #{accountId} then ht.d_balance
                    end as balance,
                dat.number sender , 'ATM' receiver, ht.created_at
                from history_tb ht
                inner join account_tb dat
                on ht.d_account_id = dat.id
                where ht.d_account_id=#{accountId};
            </if>

            <if test = "gubun == 'all'">
                select ht.amount, 
                    case when ht.w_account_id = #{accountId} then ht.w_balance
                    when ht.d_account_id = #{accountId} then ht.d_balance
                    end as balance,
                nvl(wat.number, 'ATM') sender, nvl (dat.number,  'ATM') receiver, ht.created_at
                from history_tb ht
                left outer join account_tb wat
                on ht.w_account_id=wat.id
                left outer join account_tb dat
                on ht.d_account_id = dat.id
                where ht.w_account_id =#{accountId} or ht.d_account_id=#{accountId};
            </if>
            
        </select>

if 문을 사용해서 gubun 에 따라, withdraw / deposit / all 으로 나눠준다.

case 문을 사용해, 조건을 걸어준다. 입출금 id 와, 계좌 id 가 같은 경우 잔액으로 나타냄

 

---

응답 Dto 만들기

package shop.mtcoding.bankapp.dto.account;

import java.security.Principal;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class AccountDetailResDto {
    private Integer id;
    private String number;
    private Long balance;
    private Integer userId;
    private String fullname;
}

package shop.mtcoding.bankapp.dto.history;

import java.sql.Timestamp;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class HistoryResDto {
    private Integer id;
    private Long amount;
    private Long balance;
    private String sender;
    private String receiver;
    private Timestamp createdAt;
}

 

---

Repository

    public AccountDetailResDto findByIdWithUser(int id);

    public List<HistoryResDto> findByGubun(@Param("gubun") String gubun, @Param("accountId") int accountId);

---

Controller

 @GetMapping("/account/{id}")
    public String detail(@PathVariable int id, @RequestParam(name = "gubun", defaultValue = "all") String gubun,
            Model model) {
        // 1. 인증체크
        User principal = (User) session.getAttribute("principal");
        if (principal == null) {
            return "redirect:/loginForm";
        }
        // 2. 레파지토리 호출
        AccountDetailResDto aDto = accountRepository.findByIdWithUser(id);
        if (aDto.getUserId() != principal.getId()) {
            throw new CustomException("해당계좌를 볼 권한이 없습니다", HttpStatus.BAD_REQUEST);
        }

        List<HistoryResDto> hDtoList = historyRepository.findByGubun(gubun, id);

        model.addAttribute("aDto", aDto);
        model.addAttribute("hDtoList", hDtoList);

        return "account/detail";
    }

---

detail.jsp 수정

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@include file="../layout/header.jsp" %>

  <div class="m-4">
        <p class="text-center fs-4" style="font-weight: bolder;">계좌 상세보기</p>
        <div class="my_form">
            <hr />
            <div class="border border-success p-2 mb-2 border-opacity-25 text-center">
                ${aDto.fullname}님 계좌<br />
                계좌번호 : ${aDto.number}<br />
                잔액 : ${aDto.balance}원
            </div>
            <hr />
            <div>
                <ul class="nav justify-content-center">
                    <li class="nav-item">
                        <a class="nav-link" href="/account/${aDto.id}?gubun=all">전체</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="/account/${aDto.id}?gubun=deposit">입금</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="/account/${aDto.id}?gubun=withdraw">출금</a>
                    </li>
                </ul>
            </div>
            <table class="table">
                <thead class="text-center">
                    <tr>
                        <th scope="col" style="font-weight: bolder;">날짜</th>
                        <th scope="col" style="font-weight: bolder;">보낸이</th>
                        <th scope="col" style="font-weight: bolder;">받은이</th>
                        <th scope="col" style="font-weight: bolder;">입출금 금액</th>
                        <th scope="col" style="font-weight: bolder;">계좌 잔액</th>
                    </tr>

                <tbody class="table-group-divider text-center">
                    <c:forEach items="${hDtoList}" var="history">
                        <tr>
                            <td scope="row">${history.createdAt}</td>
                            <td scope="row">${history.sender}</td>
                            <td scope="row">${history.receiver}</td>
                            <td scope="row">${history.amount}</td>
                            <td scope="row">${history.balance}</td>
                        </tr>
                    </c:forEach>
                </tbody>
            </table>
        </div>
</body>

</html>

---

결과

---