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

1. AccountService에서 type 전달

public AccountResponse.DetailDTO 계좌상세보기(int number, String type, User sessionUser) {
        // 1. 계좌 존재 확인 (부가로직)
        Account account = accountRepository.findByNumber(number);
        if (account == null) throw new RuntimeException("출금 계좌가 존재하지 않습니다");

        // 2. 계좌 주인 확인 (부가로직)
        if (!(account.getUserId().equals(sessionUser.getId()))) {  // Integer는 125이하까지만 값을 비교해주기 때문에 equals로 비교하는게 좋다
            throw new RuntimeException("해당 계좌의 권한이 없습니다");
        }

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

2. AccountRepository 동적 쿼리

   public List<AccountResponse.DetailDTO> findAllByNumber(int number, String type) {
        String allSql = """
                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 = ?;
                """;

        String withdrawSql = """
                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,
                withdraw_balance balance,
                '출금' 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 withdraw_number = ?;
                """;

        String depositSql = """
                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,
                deposit_balance balance,
                '입금' 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 = ?;
                """;

        Query query = null;
        if (type.equals("입금")) {
            query = em.createNativeQuery(depositSql);
            query.setParameter(1, number);
            query.setParameter(2, number);
        } else if (type.equals("출금")) {
            query = em.createNativeQuery(withdrawSql);
            query.setParameter(1, number);
            query.setParameter(2, number);
        } else {
            query = em.createNativeQuery(allSql);
            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;
    }

✅ 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() {
        // given data
        int number = 1111;
        String type = "전체";

        // when
        List<AccountResponse.DetailDTO> detailList = accountRepository.findAllByNumber(number, type);

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

3. 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">
        <a href="/account/{{models.0.accountNumber}}?type=전체" class="btn btn-outline-primary">전체</a>
        <a href="/account/{{models.0.accountNumber}}?type=입금" class="btn btn-outline-primary">입금</a>
        <a href="/account/{{models.0.accountNumber}}?type=출금" class="btn btn-outline-primary">출금</a>
    </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}}