[Spring Boot] 7. Spring Boot Project (Bank v1)_2.Table Design

user_tb

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)  //username은 중복될 수 없다
    private String username;

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

    @Column(nullable = false)
    private String fullname;

    private Timestamp createdAt;  //생성날짜 (insert 된 시간)
}

account_tb

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;  //생성날짜 (insert 된 시간)

}

history_tb

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;  //생성날짜 (insert 된 시간)

}

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

README.md

# BANK Table 설계

```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 account_tb
(
    balance    integer,
    number     integer not null,
    user_id    integer,
    created_at timestamp(6),
    password   varchar(255),
    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)
);
```

Dummy Data

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

h2-console

user_tb

account_tb

history_tb