[스프링부트] 25. 스프링부트 뱅크 v1 테이블 설계

USER_TB

ACCOUNT_TB

HISTORY_TB

User

package com.metacoding.bankv1.user;

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

import java.sql.Timestamp;

@NoArgsConstructor
@Getter
@Table(name = "user_tb")
@Entity
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 createdAt; // 생성 날짜 (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;

@NoArgsConstructor
@Getter
@Table(name = "account_tb")
@Entity
public class Account {

    @Id
    private Integer number; // 계좌번호 PK
    private String 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;

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

application.properties

# utf-8
server.servlet.encoding.charset=utf-8
server.servlet.encoding.force=true
# DB
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
# JPA table create or none (create : 실행할 때 테이블 생성o, none : 실행할 때 테이블 생성x)
spring.jpa.hibernate.ddl-auto=create
# query log
spring.jpa.show-sql=true
# dummy data
spring.sql.init.data-locations=classpath:db/data.sql
# create dummy data  after ddl-auto create
spring.jpa.defer-datasource-initialization=true
# mustache request expose
spring.mustache.servlet.expose-request-attributes=true
# sql formatter
spring.jpa.properties.hibernate.format_sql=true

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());

README.md

# 뱅크 테이블 설계

```sql
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)
);

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)
);
```