[Spring Boot] 29. 스프링부트 뱅크 v1 (11) 계좌 상세보기2

1. 한 방 쿼리

1. User Account

select at.balance account_balance, at.number account_number, ut.fullname account_owner
from account_tb at 
inner join user_tb ut on at.user_id = ut.id
where at.number = 1111;

2. 이체 출금 내역

select substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
WITHDRAW_BALANCE balance
from history_tb
where WITHDRAW_NUMBER=1111;

3. 이체 입금 내역

select substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
DEPOSIT_BALANCE balance
from history_tb
where DEPOSIT_NUMBER  =1111;

4. 입출금 전체

1. 입출금 전체 조회 그러나 남의 잔액도 뜬다

select substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
WITHDRAW_BALANCE balance,
DEPOSIT_BALANCE balance
from history_tb
where WITHDRAW_NUMBER=1111 or DEPOSIT_NUMBER  =1111;

1. 나의 잔액만 노출되도록

select substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
case when  WITHDRAW_NUMBER=1111 then WITHDRAW_BALANCE
else DEPOSIT_BALANCE
end balance
from history_tb
where WITHDRAW_NUMBER=1111 or DEPOSIT_NUMBER =1111;

1. 입출금 type까지

select substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
case when  WITHDRAW_NUMBER=1111 then WITHDRAW_BALANCE
else DEPOSIT_BALANCE
end balance,
case when  WITHDRAW_NUMBER=1111 then '출금'
else '입금'
end type
from history_tb
where WITHDRAW_NUMBER=1111 or DEPOSIT_NUMBER =1111;

5. trick join

select 
dt.*,
substr(CREATED_AT,1,16) CREATED_AT,
WITHDRAW_NUMBER w_number,
DEPOSIT_NUMBER  d_number,
AMOUNT amount,
case when  WITHDRAW_NUMBER=1111 then WITHDRAW_BALANCE
else DEPOSIT_BALANCE
end balance,
case when  WITHDRAW_NUMBER=1111 then '출금'
else '입금'
end type
from history_tb ht
inner join (select at.balance account_balance, at.number account_number, ut.fullname account_owner
from account_tb at 
inner join user_tb ut on at.user_id = ut.id
where at.number = 1111) dt on 1=1
where WITHDRAW_NUMBER=1111 or DEPOSIT_NUMBER =1111;

2. DB세상 데이터를 담은 오브젝트 만들기

package com.metacoding.bankv1.account;

import lombok.AllArgsConstructor;
import lombok.Data;

public class AccountResponse {

    @AllArgsConstructor
    @Data
    public static class DetailDTO {
        private int accountNumber;
        private int accountBalance;
        private String accountOwner;
        private String createdAt;
        private int wNumber;
        private int dNumber;
        private int amount;
        private int balance;
        private String type;
    }
}

3. Repository 함수 만들기

public List<AccountResponse.DetailDTO> findAllByNumber(int number) {
    String sql = """
            select 
            dt.account_number,
            dt.account_balance,
            dt.account_owner,
            substr(created_at, 1, 16) created_at,
            withdraw_number w_number,
            deposit_number d_number,
            amount amount,
            case when withdraw_number = ? then withdraw_balance 
            else deposit_balance 
            end balance,
            case when withdraw_number = ? then '출금' 
            else '입금' 
            end type 
            from history_tb ht 
            inner join (select at.number account_number, at.balance account_balance, ut.fullname account_owner 
            from account_tb at 
            inner join user_tb ut on at.user_id = ut.id 
            where at.number = ?) dt on 1=1 
            where deposit_number = ? or withdraw_number = ?;
            """;
    Query query = em.createNativeQuery(sql);
    query.setParameter(1, number);
    query.setParameter(2, number);
    query.setParameter(3, number);
    query.setParameter(4, number);
    query.setParameter(5, number);

    List<Object[]> obsList = query.getResultList();
    List<AccountResponse.DetailDTO> detailList = new ArrayList<>();

    for (Object[] obs : obsList) {
        AccountResponse.DetailDTO detail =
                new AccountResponse.DetailDTO(
                        (int) obs[0],
                        (int) obs[1],
                        (String) obs[2],
                        (String) obs[3],
                        (int) obs[4],
                        (int) obs[5],
                        (int) obs[6],
                        (int) obs[7],
                        (String) obs[8]
                );
        detailList.add(detail);
    }
    return detailList;
}

4. 복잡한 쿼리 테스트 하기

package com.metacoding.bankv1.account;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.context.annotation.Import;

import java.util.List;

@Import(AccountRepository.class)
@DataJpaTest
public class AccountRepositoryTest {
    @Autowired
    private AccountRepository accountRepository;

    @Test
    public void findAllByNumber_test() {
        int number = 1111;
        List<AccountResponse.DetailDTO> detailList = accountRepository.findAllByNumber(number);

        for (AccountResponse.DetailDTO detail : detailList) {
            System.out.println(detail);
        }
    }
}

5. Service 구현

public List<AccountResponse.DetailDTO> 계좌상세보기(int number, String type, Integer sessionUserId) {
        // 1. 계좌 존재 확인
        Account account = accountRepository.findByNumber(number);
        if (account == null) throw new RuntimeException("계좌가 존재하지 않습니다.");

        // 2. 계좌 주인 확인
        if (!(account.getUserId().equals(sessionUserId))) {
            throw new RuntimeException("해당 계좌에 대한 권한이 없습니다.");
        }

        // 3. 조회해서 주기
        return accountRepository.findAllByNumber(number);
    }

6. Controller 구현

@GetMapping("/account/{number}")
    public String detail(@PathVariable("number") int number, @RequestParam(value = "type", required = false, defaultValue = "전체") String type,
                         HttpServletRequest request) {
        // 인증
        User sessionUser = (User) session.getAttribute("sessionUser");
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해주세요"); // 로그인X시 아예 못들어오도록

        List<AccountResponse.DetailDTO> detailList = accountService.계좌상세보기(number, type, sessionUser.getId()); // sessionUser.getId() : 권한 체크
        request.setAttribute("models", detailList);
        // System.out.println("number = " + number + ", type = " + type);
        return "account/detail";
    }

7. 가방에 있는 것을 화면에 출력하기

{{>layout/header}}

<!--마진 : mt, mr, ml, mb (1~5) ex) mt-5-->
<div class="container mt-2">

    <div class="mt-4 p-5 bg-light text-dark rounded-4">
        <p>{{models.0.accountOwner}}님 계좌</p>
        <p>계좌번호 : {{models.0.accountNumber}}</p>
        <p>계좌잔액 : {{models.0.balance}}원</p>
    </div>

    <div class="mt-3 mb-3">
        <button type="button" class="btn btn-outline-primary">전체</button>
        <button type="button" class="btn btn-outline-primary">입금</button>
        <button type="button" class="btn btn-outline-primary">출금</button>
    </div>

    <table class="table table-hover">
        <thead>
        <tr>
            <th>날짜</th>
            <th>출금계좌</th>
            <th>입금계좌</th>
            <th>금액</th>
            <th>계좌잔액</th>
            <th>입금/출금</th>
        </tr>
        </thead>
        <tbody>
        {{#models}}
            <tr>
                <td>{{createdAt}}</td>
                <td>{{wNumber}}</td>
                <td>{{dNumber}}</td>
                <td>{{amount}}원</td>
                <td>{{balance}}원</td>
                <td>{{type}}</td>
            </tr>
        {{/models}}
        </tbody>
    </table>
</div>

{{>layout/footer}}