[SB] 10. 스프링부트 뱅크 v1

Mar 24, 2025

Contents

1. 뱅크 그림 그리기 2. 그림 연결 (mustache)🧔layout/header 🧔layout/footer 🧔home 🧔account/detail 🧔account/list 🧔account/save-form 🧔account/transfer-form 🧔user/join-form 🧔user/login-form ☕HelloController 3. 테이블 설계 User table Account table History table ☕User ☕Account ☕History 데이터베이스 설정 table - DDL resources/db/data.sql 4. 기능 설계 5. 회원가입 ☕UserRequest ☕UserController ☕UserService ☕UserRepository 🧔user/join-form 6. 로그인&로그아웃 ☕UserContoller ☕UserService 🧔layout/header 🧔user/login-form 🧔home 7. 계좌 생성 ☕AccountRequest ☕AccountController ☕AccountService ☕AccountRepository 8. 계좌 목록 ☕AccountRepository ☕AccountService ☕AccountController 🧔account/list 9. 계좌 이체 🧔layout/header 🧔account/transfer-form ☕AccountController ☕AccountRequest ☕AccountService ☕AccountService ☕HistoryRepository 10. 계좌 상세(쿼리 예쁘게)☕AccountController History_tb 만 쿼리해서 처리하는 방법 ⬇️☕AccountService ☕HistoryRepository ☕AccountResponse ☕HistoryRepositoryTest 🧔account/detail 11. 계좌 상세(쿼리 한번에)☕AccountRepository ☕AccountResponse ☕AccountRepositoryTest ☕AccountService ☕AccountController 🧔account/detail 12. 계좌 상세(동적쿼리-전체/입금/출금)🧔account/detail ☕AccountService ☕AccountRepository ☕AccountRepositoryTest 테이블 쿼리 결과 확인 history 테이블에 account 테이블 그냥 붙여서 조인 history 테이블에 account 테이블 그냥 붙여서 조인 [출금]계좌 상세 페이지에서 윗부분에 해당하는 테이블 계좌 상세 페이지에서 아랫부분에 해당하는 테이블

1. 뱅크 그림 그리기


https://github.com/codingspecialist/bankv1-view

git clone 주소
git remote rm origin
git remote -v
git remote add origin 내주소
git push origin master

1. home

2. join

3. login

4. save-form

5. transfer-form

6. list

7. detail(이미지 수정 필)

2. 그림 연결 (mustache)

🧔layout/header


<!DOCTYPE html>
<html lang="en">
<head>
	<title>Bank</title>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>

<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
	<div class="container-fluid">
		<a class="navbar-brand" href="/">Home</a>
		<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#collapsibleNavbar">
			<span class="navbar-toggler-icon"></span>
		</button>
		<div class="collapse navbar-collapse" id="collapsibleNavbar">
			<ul class="navbar-nav">
				<li class="nav-item">
					<a class="nav-link" href="#">회원가입</a>
				</li>
				<li class="nav-item">
					<a class="nav-link" href="#">로그인</a>
				</li>
				<li class="nav-item">
					<a class="nav-link" href="#">계좌목록</a>
				</li>
				<li class="nav-item">
					<a class="nav-link" href="#">계좌생성</a>
				</li>
				<li class="nav-item">
					<a class="nav-link" href="#">이체하기</a>
				</li>
			</ul>
		</div>
	</div>
</nav>
<!--네브바종료-->

🧔layout/footer


<footer class="mt-5 p-4 bg-dark text-white text-center d-flex justify-content-around align-items-center">
	<div>
		<p>Created by Cos</p>
		<p>🚩 겟인데어</p>
	</div>
	<div>
		<p>🏴 부산 수영구 XX동</p>
		<p>📞 010-2222-7777</p>
	</div>
</footer>

</body>
</html>

🧔home


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	안녕하세요
</div>
{{> layout/footer}}

🧔account/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>쌀님 계좌</p>
		<p>계좌번호 : 1111</p>
		<p>계좌잔액 : 1000원</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>
		<tr>
			<td>2025.03.24 : 10:00</td>
			<td>1111</td>
			<td>2222</td>
			<td>100원</td>
			<td>1000원</td>
			<td>출금</td>
		</tr>
		<tr>
			<td>2025.03.23 : 10:00</td>
			<td>2222</td>
			<td>1111</td>
			<td>100원</td>
			<td>1100원</td>
			<td>입금</td>
		</tr>
		</tbody>
	</table>
</div>

{{> layout/footer}}

🧔account/list


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<table class="table table-bordered table-hover">
		<thead>
		<tr>
			<th>계좌번호</th>
			<th>잔액</th>
		</tr>
		</thead>
		<tbody>
		<tr>
			<td>1111</td>
			<td>1000원</td>
		</tr>
		<tr>
			<td>2222</td>
			<td>1000원</td>
		</tr>
		</tbody>
	</table>
</div>
{{> layout/footer}}

🧔account/save-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>계좌 생성 페이지</h1>
		<form action="/account/save" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter number" name="number">
			</div>
			<div class="mb-3">
				<input type="password" class="form-control" placeholder="Enter password" name="password">
			</div>
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter balance" name="balance">
			</div>
			<button type="submit" class="btn btn-primary">계좌 생성</button>
		</form>
	</div>
</div>
{{> layout/footer}}

🧔account/transfer-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>계좌 이체 페이지</h1>
		<form action="#" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter amount" name="amount">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter withdrawNumber" name="withdrawNumber">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter depositNumber" name="depositNumber">
			</div>
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter withdrawPassword" name="withdrawPassword">
			</div>
			<button type="submit" class="btn btn-primary">이체 하기</button>
		</form>
	</div>
</div>
{{> layout/footer}}

🧔user/join-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>회원가입 페이지</h1>
		<form action="/join" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter username" name="username">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter fullname" name="fullname">
			</div>
			<div class="mb-3">
				<input type="password" class="form-control" placeholder="Enter password" name="password">
			</div>
			<button type="submit" class="btn btn-primary">회원가입</button>
		</form>
	</div>
</div>
{{> layout/footer}}

🧔user/login-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>로그인 페이지</h1>
		<form action="/login" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter username" name="username">
			</div>
			<div class="mb-3">
				<input type="password" class="form-control" placeholder="Enter password" name="password">
			</div>
			<button type="submit" class="btn btn-primary">로그인</button>
		</form>
	</div>
</div>
{{> layout/footer}}

☕HelloController


package com.metacoding.bankv1;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class HelloController {

    @GetMapping("/t1")
    public String t1() {
        return "/home";
    }

    @GetMapping("/t2")
    public String t2() {
        return "/user/join-form";
    }

    @GetMapping("/t3")
    public String t3() {
        return "/user/login-form";
    }

    @GetMapping("/t4")
    public String t4() {
        return "/account/detail";
    }

    @GetMapping("/t5")
    public String t5() {
        return "/account/list";
    }

    @GetMapping("/t6")
    public String t6() {
        return "/account/save-form";
    }

    @GetMapping("/t7")
    public String t7() {
        return "/account/transfer-form";
    }
    
}

3. 테이블 설계

User table

user table

Account table

account table

History table

history table

☕User


package com.metacoding.bankv1.user;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.NoArgsConstructor;

import java.sql.Timestamp;

@Entity
@Table(name = "user_tb")
@Getter
@NoArgsConstructor
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(unique = true, nullable = false, length = 12) // 제약조건 거는 법
    private String username; // 유저 아이디

    @Column(nullable = false, length = 12)
    private String password;

    @Column(nullable = false)
    private String fullname;

    private Timestamp created; // 생성 날짜(insert 시점)
}

☕Account


package com.metacoding.bankv1.account;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.NoArgsConstructor;

import java.sql.Timestamp;

@Entity
@Table(name = "account_tb")
@Getter
@NoArgsConstructor
public class Account {
    @Id
    private Integer number; // 계좌번호 pk
    private Integer password;
    private Integer balance; // 잔액
    private Integer userId; // fk
    private Timestamp createdAt;
}

☕History


package com.metacoding.bankv1.history;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.NoArgsConstructor;

import java.sql.Timestamp;

@Entity
@Table(name = "history_tb")
@Getter
@NoArgsConstructor
public class History {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private Integer withdrawNumber; // 1111 fk
    private Integer depositNumber; // 2222 fk
    private Integer amount; // 이체 금액
    private Integer withdrawBalance; // 그 시점의 잔액
    private Timestamp createdAt;
}

데이터베이스 설정

application.properties

table - DDL


create table account_tb
(
    balance    integer,
    number     integer not null,
    password   integer,
    user_id    integer,
    created_at timestamp(6),
    primary key (number)
);

create table history_tb
(
    amount           integer,
    withdraw_balance integer,
    deposit_number   integer,
    id               integer generated by default as identity,
    withdraw_number  integer,
    created_at       timestamp(6),
    primary key (id)
);

create table user_tb
(
    id         integer generated by default as identity,
    created_at timestamp(6),
    password   varchar(12)  not null,
    username   varchar(12)  not null unique,
    fullname   varchar(255) not null,
    primary key (id)
);

resources/db/data.sql


insert into user_tb (username, password, fullname, created_at)
values ('ssar', '1234', '쌀', now());
insert into user_tb (username, password, fullname, created_at)
values ('cos', '1234', '코스', now());

insert into account_tb (number, password, balance, user_id, created_at)
values (1111, '1234', 900, 1, now());
insert into account_tb (number, password, balance, user_id, created_at)
values (2222, '1234', 1100, 1, now());
insert into account_tb (number, password, balance, user_id, created_at)
values (3333, '1234', 1000, 2, now());

insert into history_tb (withdraw_number, deposit_number, amount, withdraw_balance, created_at)
values (1111, 2222, 100, 900, now());
insert into history_tb (withdraw_number, deposit_number, amount, withdraw_balance, created_at)
values (1111, 3333, 100, 800, now());
insert into history_tb (withdraw_number, deposit_number, amount, withdraw_balance, created_at)
values (3333, 1111, 100, 1000, now());

4. 기능 설계

회원가입

로그인

계좌목록

계좌생성

계좌이체

계좌상세

5. 회원가입

☕UserRequest


package com.metacoding.bankv1.user;

import lombok.Data;

public class UserRequest {

    @Data
    public static class JoinDTO {
        private String username;
        private String password;
        private String fullname;
    }

    @Data
    public static class LoginDTO {
        private String username;
        private String password;
    }

}

DTO를 만들어서 Controller 의 파라미터에 넣어주면 spring이 알아서 클래스에 데이터 맵핑을 해준다

☕UserController


package com.metacoding.bankv1.user;

import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

@Controller
@RequiredArgsConstructor
public class UserController {
    private final UserService userService;


    @GetMapping("/join-form")
    public String joinForm() {
        return "/user/join-form";
    }


    @PostMapping("/join")
    public String join(UserRequest.JoinDTO joinDTO) {
        userService.회원가입(joinDTO);
        return "redirect:/login-form";
    }
}

☕UserService


package com.metacoding.bankv1.user;


import org.springframework.transaction.annotation.Transactional;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    @Transactional
    public void 회원가입(UserRequest.JoinDTO joinDTO) {
        // 1. 동일 유저네임 확인
        User user = userRepository.findByUsername(joinDTO.getUsername());
        if (user != null) {
            // 2. 있으면, exception !오류는 exception으로 처리한다
            throw new RuntimeException("동일한 username이 있습니다");
        }
        // 3. 없으면 회원가입
        userRepository.save(joinDTO.getUsername(), joinDTO.getPassword(), joinDTO.getFullname());
    }
}

☕UserRepository


package com.metacoding.bankv1.user;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

@Repository
@RequiredArgsConstructor
public class UserRepository {
    private final EntityManager em;

    public void save(String username, String password, String fullname) {
        Query query = em.createNativeQuery("insert into user_tb (username, password, fullname, created_at) values (?, ?, ?, now())");
        query.setParameter(1, username);
        query.setParameter(2, password);
        query.setParameter(3, fullname);
        query.executeUpdate();
    }

    public User findByUsername(String username) {
        Query query = em.createNativeQuery("select * from user_tb where username = ?", User.class);
        query.setParameter(1, username);
        try {
            return (User) query.getSingleResult();
        } catch (Exception e) {
            return null;
        }
    }
}

🧔user/join-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>회원가입 페이지</h1>
		<form action="/join" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter username" name="username">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter fullname" name="fullname">
			</div>
			<div class="mb-3">
				<input type="password" class="form-control" placeholder="Enter password" name="password">
			</div>
			<button type="submit" class="btn btn-primary">회원가입</button>
		</form>
	</div>
</div>
{{> layout/footer}}

6. 로그인&로그아웃

☕UserContoller



    @GetMapping("/login-form")
    public String loginForm() {
        return "/user/login-form";
    }

    // request와 session의 스코프 = 얼마동안 살아있는가? session이 더 오래 살아 있다
    // PostMapping인 이유(예외) 비번을 body로 받아야 하니까
    @PostMapping("/login")
    public String login(UserRequest.LoginDTO loginDTO) {
        User sessionUser = userService.로그인(loginDTO);
        session.setAttribute("sessionUser", sessionUser); // stateful 서버에 상태를 저장
        return "redirect:/";
    }

    @GetMapping("/logout")
    public String logout() {
        session.invalidate(); // 세션에 있는 정보 전부 제거
        return "redirect:/";
    }

☕UserService


    public User 로그인(UserRequest.LoginDTO loginDTO) {
        // 1. 해당 username이 있는가?
        User user = userRepository.findByUsername(loginDTO.getUsername());

        // 2. 필터링(username 유무 확인, password 동일 확인)
        if (user == null) {
            throw new RuntimeException("해당 username이 없습니다");
        }
        if (!(user.getPassword().equals(loginDTO.getPassword()))) {
            throw new RuntimeException("해당 password가 틀렸습니다");
        }

        // 3. 인증
        return user;
    }

🧔layout/header


<!DOCTYPE html>
<html lang="en">
<head>
	<title>Bank</title>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>

<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
	<div class="container-fluid">
		<a class="navbar-brand" href="/">Home</a>
		<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#collapsibleNavbar">
			<span class="navbar-toggler-icon"></span>
		</button>
		<div class="collapse navbar-collapse" id="collapsibleNavbar">
			<ul class="navbar-nav">
				{{#sessionUser}}
					<li class="nav-item">
						<a class="nav-link" href="#">계좌목록</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="#">계좌생성</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="#">이체하기</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/logout">로그아웃</a>
					</li>
				{{/sessionUser}}
				{{^sessionUser}}
					<li class="nav-item">
						<a class="nav-link" href="/join-form">회원가입</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/login-form">로그인</a>
					</li>
				{{/sessionUser}}
			</ul>
		</div>
	</div>
</nav>
<!--네브바종료-->

🧔user/login-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>로그인 페이지</h1>
		<form action="/login" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter username" name="username" value="ssar">
			</div>
			<div class="mb-3">
				<input type="password" class="form-control" placeholder="Enter password" name="password" value="1234">
			</div>
			<button type="submit" class="btn btn-primary">로그인</button>
		</form>
	</div>
</div>
{{> layout/footer}}

🧔home


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<h1>메타코딩 은행에 오신것을 환영합니다</h1>
	<h3>
		{{#sessionUser}}
			{{sessionUser.fullname}}님
		{{/sessionUser}}
	</h3>
</div>
{{> layout/footer}}

7. 계좌 생성

☕AccountRequest


package com.metacoding.bankv1.account;

import lombok.Data;

public class AccountRequest {

    @Data
    public static class SaveDTO {
        private Integer number;
        private String password;
        private Integer balance;
    }
}

사용자에게 계좌의 정보만 받아오는 DTO 여기서 로그인된 session의 데이터를 추가해 db에 저장한다

☕AccountController


    @GetMapping("/account/save-form")
    public String saveForm() {
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");

        return "/account/save-form";
    }

    @PostMapping("/account/save")
    public String save(AccountRequest.SaveDTO saveDTO) {
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");

        accountService.계좌생성(saveDTO, sessionUser.getId());

        return "redirect:/";
    }

☕AccountService


package com.metacoding.bankv1.account;

import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


@Service
@RequiredArgsConstructor
public class AccountService {
    private final AccountRepository accountRepository;

    @Transactional
    public void 계좌생성(AccountRequest.SaveDTO saveDTO, Integer userId) {
        accountRepository.save(saveDTO.getNumber(), saveDTO.getPassword(), saveDTO.getBalance(), userId);
    }
}

☕AccountRepository


package com.metacoding.bankv1.account;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@RequiredArgsConstructor
public class AccountRepository {
    private final EntityManager em;

    public void save(Integer number, String password, Integer balance, Integer userId) {
        Query query = em.createNativeQuery("insert into account_tb (number, password, balance, user_id, created_at) values (?, ?, ?, ?, now())");
        query.setParameter(1, number);
        query.setParameter(2, password);
        query.setParameter(3, balance);
        query.setParameter(4, userId);
        query.executeUpdate();
    }
}

8. 계좌 목록

☕AccountRepository


    public List<Account> findAllBySessionUser(Integer userId) {
        Query query = em.createNativeQuery("select * from account_tb where user_id = ?", Account.class);
        query.setParameter(1, userId);
        return query.getResultList();
    }

☕AccountService


    public List<Account> 계좌목록(Integer sessionUserId) {
        return accountRepository.findAllBySessionUser(sessionUserId);
    }

☕AccountController


    @GetMapping("/account")
    public String list(HttpServletRequest request) {
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");

        List<Account> accountList = accountService.계좌목록(sessionUser.getId());

        request.setAttribute("models", accountList);

        return "/account/list";
    }

🧔account/list


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<table class="table table-bordered table-hover">
		<thead>
		<tr>
			<th>계좌번호</th>
			<th>잔액</th>
		</tr>
		</thead>
		<tbody>
		{{#models}}
			<tr>
				<td>{{number}}</td>
				<td>{{balance}}원</td>
			</tr>
		{{/models}}
		</tbody>
	</table>
</div>
{{> layout/footer}}

9. 계좌 이체

🧔layout/header


<!DOCTYPE html>
<html lang="en">
<head>
	<title>Bank</title>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>

<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
	<div class="container-fluid">
		<a class="navbar-brand" href="/">Home</a>
		<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#collapsibleNavbar">
			<span class="navbar-toggler-icon"></span>
		</button>
		<div class="collapse navbar-collapse" id="collapsibleNavbar">
			<ul class="navbar-nav">
				{{#sessionUser}}
					<li class="nav-item">
						<a class="nav-link" href="/account">계좌목록</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/account/save-form">계좌생성</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/account/transfer-form">이체하기</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/logout">로그아웃</a>
					</li>
				{{/sessionUser}}
				{{^sessionUser}}
					<li class="nav-item">
						<a class="nav-link" href="/join-form">회원가입</a>
					</li>
					<li class="nav-item">
						<a class="nav-link" href="/login-form">로그인</a>
					</li>
				{{/sessionUser}}
			</ul>
		</div>
	</div>
</nav>
<!--네브바종료-->

🧔account/transfer-form


{{> layout/header}}
<!--마진 : mt, mr, ml, mb (1~5) ex) mb-5 -->
<div class="container mt-2">
	<div class="mt-4 p-5 bg-light text-dark rounded">
		<h1>계좌 이체 페이지</h1>
		<form action="/account/transfer" method="post">
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter amount" name="amount">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter withdrawNumber" name="withdrawNumber">
			</div>
			<div class="mb-3">
				<input type="text" class="form-control" placeholder="Enter depositNumber" name="depositNumber">
			</div>
			<div class="mb-3 mt-3">
				<input type="text" class="form-control" placeholder="Enter withdrawPassword" name="withdrawPassword">
			</div>
			<button type="submit" class="btn btn-primary">이체 하기</button>
		</form>
	</div>
</div>
{{> layout/footer}}

☕AccountController


    @GetMapping("/account/transfer-form")
    public String transferForm() {
        // 로그인 인증 -> 공통 부가 로직
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");
        return "/account/transfer-form";
    }

    @PostMapping("/account/transfer")
    public String transfer(AccountRequest.TransferDTO transferDTO) {
        // 로그인 인증 -> 공통 부가 로직
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");
        accountService.계좌이체(transferDTO, sessionUser.getId());
        return "redirect:/"; // TODO : 리다이렉트 주소 변경
    }

☕AccountRequest


    @Data
    public static class TransferDTO {
        private Integer amount;
        private Integer withdrawNumber;
        private Integer depositNumber;
        private String withdrawPassword;
    }

☕AccountService


    @Transactional
    public void 계좌이체(AccountRequest.TransferDTO transferDTO, Integer userId) {
        // 1. 출금 계좌 조회
        Account withdrawAccount = accountRepository.findByNumber(transferDTO.getWithdrawNumber());
        // 1-1. 출금 계좌 없으면 예외
        if (withdrawAccount == null)
            throw new RuntimeException("해당 출금 계좌가 없습니다");

        // 2. 입금 계좌 조회
        Account depositAccount = accountRepository.findByNumber(transferDTO.getDepositNumber());
        // 2-1. 입급 계좌 없으면 예외
        if (depositAccount == null)
            throw new RuntimeException("해당 입금 계좌가 없습니다");

        // 3. 출금 계좌의 잔액 조회
        if (withdrawAccount.getBalance() < transferDTO.getAmount())
            throw new RuntimeException("출금 계좌 잔액: " + transferDTO.getAmount() + ", 이체 하려는 금액: " + transferDTO.getAmount());

        // 4. 출금 비밀번호 확인
        if (!(withdrawAccount.getPassword().equals(transferDTO.getWithdrawPassword())))
            throw new RuntimeException("출금 계좌 비밀번호가 틀렸습니다");

        // 5. 출금 계좌와 로그인 유저 동일성 확인(권한 체크)
        if (!(withdrawAccount.getUserId().equals(userId)))
            throw new RuntimeException("출금계좌의 권한이 없습니다");

        // 6. 출금 계좌 & 입금 계좌 업데이트
        // 6-1. 출금 계좌 업데이트
        int withdrawBalance = withdrawAccount.getBalance();
        withdrawBalance -= transferDTO.getAmount();
        accountRepository.updateByNumber(
                withdrawBalance,
                withdrawAccount.getPassword(),
                withdrawAccount.getNumber());
        // 6-2. 입금 계좌 업데이트
        int depositBalance = depositAccount.getBalance();
        depositBalance += transferDTO.getAmount();
        accountRepository.updateByNumber(
                depositBalance,
                depositAccount.getPassword(),
                depositAccount.getNumber());

        // 7. history table 저장
        historyRepository.save(
                transferDTO.getWithdrawNumber(),
                transferDTO.getDepositNumber(),
                transferDTO.getAmount(),
                withdrawBalance);
    }
}

☕AccountService


    public Account findByNumber(Integer number) {
        Query query = em.createNativeQuery("select * from account_tb where number = ?", Account.class);
        query.setParameter(1, number);

        try {
            return (Account) query.getSingleResult();
        } catch (Exception e) {
            return null;
        }
    }

    public void updateByNumber(Integer balance, String password, Integer number) {
        Query query = em.createNativeQuery("update account_tb set balance = ?, password = ? where number = ?");
        query.setParameter(1, balance);
        query.setParameter(2, password);
        query.setParameter(3, number);
        query.executeUpdate();
    }

☕HistoryRepository


package com.metacoding.bankv1.account.history;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

@Repository
@RequiredArgsConstructor
public class HistoryRepository {
    private final EntityManager em;

    public void save(Integer withdrawNumber, Integer depositNumber, Integer amount, Integer withdrawBalance) {
        Query query = em.createNativeQuery("insert into history_tb (withdraw_number, deposit_number, amount, withdraw_balance, create_at) values (?, ?, ?, ?, now())");
        query.setParameter(1, withdrawNumber);
        query.setParameter(2, depositNumber);
        query.setParameter(3, amount);
        query.setParameter(4, withdrawBalance);
        query.executeUpdate();
    }
}

10. 계좌 상세(쿼리 예쁘게)

☕AccountController

사용자로부터 계좌번호와 type을 받는다

pk, uk가 아닌 where절에 사용되는 값은 ?key=value 쿼리스트링으로 받는다


    // /account/1111?type=입금,출금,전체
    // pk나 유니크 값은 uri 주소로 받는게 약속이다
    @GetMapping("/account/{number}")
    public String detail(@PathVariable Integer number,
                         @RequestParam(value = "type", required = false, defaultValue = "전체") String type,
                         HttpServletRequest request) {
        // 로그인 인증 -> 공통 부가 로직
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");

        // request.getParam("값") -> 쿼리스트링, xxx-formdata
        AccountResponse.DetailDTO detailDTO = accountService.계좌상세보기(number, type, sessionUser);
        request.setAttribute("model", detailDTO);
        return "/account/detail";
    }

History_tb 만 쿼리해서 처리하는 방법 ⬇️

☕AccountService

history 테이블 데이터를 가져와 sessionUser 정보와 이전에 쿼리를 사용한 account 정보를 합친 DTO를 만든다


    public AccountResponse.DetailDTO 계좌상세보기(Integer number, String type, User sessionUser) {
        // 1. 계좌 존재 확인
        Account account = accountRepository.findByNumber(number);
        if (account == null)
            throw new RuntimeException("해당 계좌가 없습니다");
        // 2. 계좌 주인 확인
        if (!(account.getUserId().equals(sessionUser.getId())))
            throw new RuntimeException("해당 계좌의 권한이 없습니다");
        // 3. 조회
        List<AccountResponse.HistoryDTO> historyList = historyRepository.findAllByNumber(account.getNumber());

        AccountResponse.DetailDTO detailDTO = new AccountResponse.DetailDTO(sessionUser, account, historyList);

        System.out.println(detailDTO);

        return detailDTO;
    }

☕HistoryRepository

여러건의 테이블 데이터는 getResultList()로 받아온다 받아온 데이터의 형태는 [object[계좌번호, 이름, 잔액], object[계좌번호, 이름, 잔액], object[계좌번호, 이름, 잔액]…] 형태다


    public List<AccountResponse.HistoryDTO> findAllByNumber(Integer number) {
        Query query = em.createNativeQuery("select substr(created_at, 1, 16) created_at, withdraw_number, deposit_number, amount, case when withdraw_number = ? then withdraw_balance else deposit_balance end as \"balance\", case when withdraw_number = ? then '출금' else '입금' end as \"type\" from history_tb where withdraw_number = ? or deposit_number = ?");
        query.setParameter(1, number);
        query.setParameter(2, number);
        query.setParameter(3, number);
        query.setParameter(4, number);

        List<Object[]> objectList = query.getResultList();

        List<AccountResponse.HistoryDTO> historyList = new ArrayList<>();

        for (Object[] objects : objectList) {
            AccountResponse.HistoryDTO history = new AccountResponse.HistoryDTO(
                    (String) objects[0],
                    (Integer) objects[1],
                    (Integer) objects[2],
                    (Integer) objects[3],
                    (Integer) objects[4],
                    (String) objects[5]);
            historyList.add(history);
        }

        return historyList;
    }

☕AccountResponse

account 테이블에서 가져온 정보를 DTO로 만들기 위한 클래스


package com.metacoding.bankv1.account;

import com.metacoding.bankv1.user.User;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.List;

public class AccountResponse {

    // 화면이랑 똑같이 생긴 오브젝트
    @Data
    public static class DetailDTO {
        private String fullname;
        private Integer number;
        private Integer balance;
        private List<HistoryDTO> histories;

        public DetailDTO(User sessionUser, Account account, List<HistoryDTO> histories) {
            this.fullname = sessionUser.getFullname();
            this.number = account.getNumber();
            this.balance = account.getBalance();
            this.histories = histories;
        }
    }

    @Data
    @AllArgsConstructor
    public static class HistoryDTO {
        private String createdAt;
        private Integer withdrawNumber;
        private Integer depositNumber;
        private Integer amount;
        private Integer balance;
        private String type;
    }
}

☕HistoryRepositoryTest

repository에서 쿼리를 한 결과를 확인하기 위한 test 클래스


package com.metacoding.bankv1.account.history;

import com.metacoding.bankv1.account.history.HistoryRepository;
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(HistoryRepository.class)
@DataJpaTest
public class HistoryRepositoryTest {

    @Autowired
    HistoryRepository historyRepository;

    @Test
    public void findAllByNumber_test() {
        System.out.println(historyRepository.findAllByNumber(1111));
    }
}

🧔account/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>{{model.fullname}}님 계좌</p>
		<p>계좌번호 : {{model.number}}</p>
		<p>계좌잔액 : {{model.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>
		{{#model.histories}}
			<tr>
				<td>{{createdAt}}</td>
				<td>{{withdrawNumber}}</td>
				<td>{{depositNumber}}</td>
				<td>{{amount}}원</td>
				<td>{{balance}}원</td>
				<td>{{type}}</td>
			</tr>
		{{/model.histories}}
		</tbody>
	</table>
</div>
{{> layout/footer}}

11. 계좌 상세(쿼리 한번에)

☕AccountRepository

쿼리 만들기

쿼리에서 각 쿼리 부분에 끝에 스페이스를 넣어주자 다른 언어에서는 스페이스가 없을 때 오류가 발생할 수 있다


    public List<AccountResponse.DetailDTO> findAllByNumber(Integer number) {
        String q = """
                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(q);
        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(
                            (Integer) obs[0],
                            (Integer) obs[1],
                            (String) obs[2],
                            (String) obs[3],
                            (Integer) obs[4],
                            (Integer) obs[5],
                            (Integer) obs[6],
                            (Integer) obs[7],
                            (String) obs[8]
                    );
            detailList.add(detail);
        }
        return detailList;
    }

☕AccountResponse

쿼리 데이터를 담을 DTO 만들기


package com.metacoding.bankv1.account;

import lombok.AllArgsConstructor;
import lombok.Data;

public class AccountResponse {

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

☕AccountRepositoryTest

DTO 맵핑 결과 확인


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
        int number = 1111;

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

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

☕AccountService

조회 부분 구현하기


    public List<AccountResponse.DetailDTO> 계좌상세보기(Integer 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. 조회
        List<AccountResponse.DetailDTO> detailList = accountRepository.findAllByNumber(number);
        return detailList;
    }

☕AccountController

DTO 데이터를 request에 넣기


    // /account/1111?type=입금,출금,전체
    // pk나 유니크 값은 uri 주소로 받는게 약속이다
    @GetMapping("/account/{number}")
    public String detail(@PathVariable Integer number,
                         @RequestParam(value = "type", required = false, defaultValue = "전체") String type,
                         HttpServletRequest request) {
        // 로그인 인증 -> 공통 부가 로직
        User sessionUser = (User) session.getAttribute("sessionUser");
        // 인증 체크
        if (sessionUser == null) throw new RuntimeException("로그인 후 사용해 주세요");

        // request.getParam("값") -> 쿼리스트링, xxx-formdata
        List<AccountResponse.DetailDTO> detailList = accountService.계좌상세보기(number, type, sessionUser.getId());
        request.setAttribute("models", detailList);
        return "/account/detail";
    }

🧔account/detail

request에 있는 models를 화면에 뿌리기


{{> 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}}

12. 계좌 상세(동적쿼리-전체/입금/출금)

🧔account/detail

button → a. 버튼을 a 링크로 변경


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

☕AccountService

findAllByNumber() 에 type변수 넣어주기


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

☕AccountRepository

전체/입금/출금 에 대한 쿼리 각각 만들기


    public List<AccountResponse.DetailDTO> findAllByNumber(Integer 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(
                            (Integer) obs[0],
                            (Integer) obs[1],
                            (String) obs[2],
                            (String) obs[3],
                            (Integer) obs[4],
                            (Integer) obs[5],
                            (Integer) obs[6],
                            (Integer) obs[7],
                            (String) obs[8]
                    );
            detailList.add(detail);
        }
        return detailList;
    }

☕AccountRepositoryTest

type 바꿔가며 데이터 확인하기


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
        int number = 1111;
        String type = "전체";

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

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

전체

입금

출금

테이블 쿼리 결과 확인

history 테이블에 account 테이블 그냥 붙여서 조인


select 
at.number account_number, at.balance account_balance, at.fullname,
substr(ht.created_at,1,16) created_at, ht.withdraw_number, ht.deposit_number, ht.amount, 
case when ht.withdraw_number = 1111 then ht.withdraw_balance
else ht.deposit_balance
end as "BALANCE",
case when ht.withdraw_number = 1111 then '출금'
else '입금'
end as "TYPE"
from history_tb ht
inner join (
    SELECT att.number, att.balance, utt.fullname
    FROM account_tb att inner join user_tb utt on att.user_id = utt.id
    WHERE att.number = 1111
) at ON 1 = 1
where ht.withdraw_number = 1111 or ht.deposit_number = 1111;

history 테이블에 account 테이블 그냥 붙여서 조인 [출금]


select 
at.number account_number, at.balance account_balance, ut.fullname,
substr(ht.created_at,1,16) created_at, ht.withdraw_number, ht.deposit_number, ht.amount, 
case when ht.withdraw_number = 1111 then ht.withdraw_balance
else ht.deposit_balance
end as "BALANCE",
case when ht.withdraw_number = 1111 then '출금'
else '입금'
end as "TYPE"
from history_tb ht
inner join account_tb at on ht.withdraw_number = at.number
inner join user_tb ut on at.user_id = ut.id
where ht.withdraw_number = 1111;

계좌 상세 페이지에서 윗부분에 해당하는 테이블


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

계좌 상세 페이지에서 아랫부분에 해당하는 테이블

출금


select 
substr(created_at,1,16) created_at,
withdraw_number w_number,
deposit_number d_number,
amount,
withdraw_balance balance
from
history_tb
where withdraw_number = 1111

입금


select 
substr(created_at,1,16) created_at,
withdraw_number w_number,
deposit_number d_number,
amount,
deposit_balance balance
from
history_tb
where deposit_number = 1111

입금과 출금 정보를 붙인 테이블


select 
substr(created_at,1,16) created_at,
withdraw_number w_number,
deposit_number d_number,
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 deposit_number = 1111 or
withdraw_number = 1111

history 입출금 테이블에 유저의 account 정보를 붙인 테이블


select 
substr(created_at,1,16) created_at,
withdraw_number w_number,
deposit_number d_number,
amount,
case
when withdraw_number = 1111 then withdraw_balance
else deposit_balance
end balance,
case
when withdraw_number = 1111 then '출금'
else '입금'
end type,
dt.*
from
history_tb ht
inner join (
select 
ut.fullname account_owner, 
at.number account_number, 
at.balance account_balance
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

See more posts