[Spring Boot] 21. 스프링부트 뱅크 v1 (3) 테이블 설계 및 더미데이터 세팅

폴더 구조

1. 테이블 설계

1. 명사 TB 설계

1. User TB

2. Account TB

2. 동사 TB 설계

1. History TB

2. 테이블 매핑 클래스 만들기

1. User class

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 된 시간)
}

2. Account class

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 String userId; // FK
    private Timestamp createdAt; // 계좌 생성일
}

3. History class

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

3. application.properties 설정

1. application.properties

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

2. table create 확인

1. spring.sql.init.data-locations=classpath:db/data.sql는 잠깐 주석 처리 후 실행해서 테이블이 잘 만들어지는지 확인한다.

4. 더미데이터 만들기

1. 더미데이터 세팅

1. 일관성 주의해서 세팅하기

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

2. h2-console로 더미데이터 확인