[Spring Boot] 7. Spring Boot Project (Bank v1)_3-7.Fuctions Design_Account detail (한방쿼리)

1. Query

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 = 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.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 = 1111) dt on 1=1
where deposit_number = 1111 or withdraw_number = 1111;

2. DB에서 조회한 Data 담을 Model(Object_AccountResponse) 만들기

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. AccountRepository 함수 만들기

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. Test

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;   // given Data
        List<AccountResponse.DetailDTO> detailList = accountRepository.findAllByNumber(number);

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

5. AccountService

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))) {  // Integer는 125이하까지만 값을 비교해주기 때문에 equals로 비교하는게 좋다
            throw new RuntimeException("해당 계좌의 권한이 없습니다");
        }

        // 3. 조회 (핵심로직)
        List<AccountResponse.DetailDTO> detailList = accountRepository.findAllByNumber(number);
        return detailList;


    }
}

6. AccountController

// /account/1111?type=전체
    @GetMapping("/account/{number}")
    public String detail(@PathVariable("number") int number, @RequestParam(value = "type", required = false, defaultValue = "전체") String type, HttpServletRequest request) {
//        System.out.println("number = " + number);
//        System.out.println("type = " + type);

        // 인증체크 (반복되는 공통부가로직)
        User sessionUser = (User) session.getAttribute("sessionUser");
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해주세요");

        List<AccountResponse.DetailDTO> datailList = accountService.계좌상세보기(number, type, sessionUser.getId());  // sessionUser.getId()로 권한체크 필요
        request.setAttribute("models", datailList);
        return "account/detail";
    }

7. detail

{{>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.accountBalance}}원</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}}