[프로젝트] ERD & Docker 적용

1. TODO

1. JPA를 포함한 MVC 구조의 프로젝트 생성

spring boot initializer를 통해서 generate하였다.

그리고 프로젝트 하위에 controller, dto, entity, service, enums, repository 패키지를 생성하였다.

 

2. ERD 그리기

3. Entity 클래스 작성 (+ DTO)

a) User Entity

import jakarta.persistence.*;
import lombok.*;

import java.time.LocalDateTime;
import java.util.Date;

@Entity
@Table(name = "user")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class UserEntity {

    @Id
    @Column(name = "user_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer userId;

    private String name;

    @Temporal(TemporalType.DATE)
    private Date birth;

    private String nickname;

    private LocalDateTime createdAt;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "oauth_id", referencedColumnName = "oauth_id")
    private OauthEntity oauth;

    @PrePersist
    public void prePersist() {
        this.createdAt = (this.createdAt == null) ? LocalDateTime.now() : this.createdAt;
    }
}

@Id : PK 설정

@Column : 컬럼 이름 명시

@GeneratedValue : 채번 로직 설정 auto increment로 설정함

@Temporal : TemporalType.DATE로 날짜까지만 받도록 함

@PrePersist : DB에 해당 테이블의 insert 연산을 실행 할 때 같이 실행

@OneToOne : 1대1 관계로 User와 Oauth를 설정함

@JoinColumn : oauth_id와 join 명시

import lombok.*;

import java.time.LocalDateTime;
import java.util.Date;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class UserDto {
    private Integer userId;
    private Integer oauthId;
    private String name;
    private Date birth;
    private String nickname;
    private LocalDateTime createdAt;
}

 

b) OauthEntity

import jakarta.persistence.*;
import lombok.*;

import java.time.LocalDateTime;

@Entity
@Table(name = "oauth")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class OauthEntity {

    @Id
    @Column(name = "oauth_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer oauthId;

    private String provider;
    private String providerUid;
    @Lob
    private String accessToken;
    @Lob
    private String refreshToken;

    private LocalDateTime createdAt;

    @PrePersist
    public void prePersist() {
        this.createdAt = (this.createdAt == null) ? LocalDateTime.now() : this.createdAt;
    }
}

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class OauthDto {
    private Integer oauthId;
    private String provider;
    private String providerUid;
    private String accessToken;
    private String refreshToken;
}

c) CardLogEntity

import jakarta.persistence.*;
import lombok.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "card_log")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardLogEntity {

    @Id
    @Column(name = "card_log_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer cardLogId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "card_interpretation_id")
    private CardInterpretationEntity cardInterpretation;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private UserEntity user;

    private LocalDateTime createdAt;

    @PrePersist
    public void prePersist() {
        this.createdAt = (this.createdAt == null) ? LocalDateTime.now() : this.createdAt;
    }
}

CardLogEntity는 CardInterpretationEntity와 N:1(한 카드가 여러 결과에 사용될 수 있음), UserEntity에 N:1(한 사람이 여러 번 타로를 볼 수 있음)로 연결되어 있음.

import lombok.*;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardLogDto {
    private Integer cardLogId;
    private Integer cardInterpretationId;
    private Integer userId;
}

 

d) CardEntity

import com.fortuneApp.fortune_app_backend.enums.Orientation;
import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(name = "card")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardEntity {

    @Id
    @Column(name = "card_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer cardId;

    private String name;

    @Enumerated(EnumType.STRING)
    private Orientation orientation;

}

orientation의 경우 Enum 타입으로 Orientation을 따로 파일로 생성하여 관리하도록  함.

import com.fortuneApp.fortune_app_backend.enums.Orientation;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardDto {
    private Integer cardId;
    private String name;
    private Orientation orientation;
}

 

e) FortuneEntity

import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(name = "fortune")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class FortuneEntity {
    @Id
    @Column(name = "fortune_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer fortuneId;

    @Column(name = "type")
    private String type;
}

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class FortuneDto {
    private Integer fortuneId;
    private String type;
}

f) CardInterpretationEntity

import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(name = "card_interpretation")
@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardInterpretationEntity {

    @Id
    @Column(name = "interpretation_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer interpretationId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "card_id")
    private CardEntity card;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fortune_id")
    private FortuneEntity fortune;

    @Lob
    private String interpretationContent;
}

import lombok.*;

@Getter
@Setter
@AllArgsConstructor
@Builder
public class CardInterpretationDto {
    private Integer interpretationId;
    private Integer cardId;
    private Integer fortuneId;
    private String interpretationContent;
}

그 외 필요한 Repository는 아래 형식으로 구현하였다.

import com.fortuneApp.fortune_app_backend.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<UserEntity, Integer> {
}

User Service 예시와 Controller 예시이다.

import com.fortuneApp.fortune_app_backend.dto.OauthDto;
import com.fortuneApp.fortune_app_backend.dto.UserDto;
import com.fortuneApp.fortune_app_backend.entity.OauthEntity;
import com.fortuneApp.fortune_app_backend.entity.UserEntity;
import com.fortuneApp.fortune_app_backend.repository.OauthRepository;
import com.fortuneApp.fortune_app_backend.repository.UserRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@RequiredArgsConstructor
@Transactional
public class UserService {

    private final UserRepository userRepository;
    private final OauthRepository oauthRepository;
    private final OauthService oAuthService;
    /*
      @Autowired
     public UserService(UserRepository userRepository) {
         this.userRepository = userRepository;
     }
     */

    public UserDto createUser(UserDto userDto, OauthDto oauthDto) {
        OauthDto savedOauthDto = oAuthService.createOauth(oauthDto);
        OauthEntity savedOauth = oauthRepository.findById(savedOauthDto.getOauthId())
                .orElseThrow(() -> new RuntimeException("OAuth not found after creation"));

        UserEntity user = UserEntity.builder()
                .name(userDto.getName())
                .birth(userDto.getBirth())
                .nickname(userDto.getNickname())
                .oauth(savedOauth)
                .build();
        UserEntity savedUser = userRepository.save(user);
        return mapToDto(savedUser);
    }

    public UserDto getUser(Integer userId) {
        UserEntity user = userRepository.findById(userId)
                .orElseThrow(() -> new RuntimeException("User not found"));
        return mapToDto(user);
    }

    public UserDto updateUser(Integer userId, UserDto dto) {
        UserEntity user = userRepository.findById(userId)
                .orElseThrow(() -> new RuntimeException("User not found"));

        if (dto.getName() != null) {
            user.setName(dto.getName());
        }
        if (dto.getBirth() != null) {
            user.setBirth(dto.getBirth());
        }
        if (dto.getNickname() != null) {
            user.setNickname(dto.getNickname());
        }

        if (dto.getOauthId() != null) {
            OauthEntity oauth = oauthRepository.findById(dto.getOauthId())
                    .orElseThrow(() -> new RuntimeException("Oauth not found"));
            user.setOauth(oauth);
        }

        UserEntity saved = userRepository.save(user);
        return mapToDto(saved);
    }

    public void deleteUser(Integer userId) {
        UserEntity user = userRepository.findById(userId)
                .orElseThrow(() -> new RuntimeException("User not found"));
        userRepository.delete(user);
    }

    // Entity -> Dto (repository -> controller)
    private UserDto mapToDto(UserEntity entity) {
        return UserDto.builder()
                .userId(entity.getUserId())
                .oauthId(entity.getOauth().getOauthId())
                .name(entity.getName())
                .birth(entity.getBirth())
                .nickname(entity.getNickname())
                .createdAt(entity.getCreatedAt())
                .build();
    }
}

import com.fortuneApp.fortune_app_backend.dto.OauthDto;
import com.fortuneApp.fortune_app_backend.dto.UserDto;
import com.fortuneApp.fortune_app_backend.service.UserService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/users")
@RequiredArgsConstructor
public class UserController {

    private final UserService userService;

    @PostMapping
    public UserDto createUser(@RequestBody UserDto dto, OauthDto oauthDto) {
        return userService.createUser(dto, oauthDto);
    }

    @GetMapping("/{id}")
    public UserDto getUser(@PathVariable Integer id) {
        return userService.getUser(id);
    }

    @PatchMapping("/{id}")
    public UserDto updateUser(@PathVariable Integer id, @RequestBody UserDto dto) {
        return userService.updateUser(id, dto);
    }


    @DeleteMapping("/{id}")
    public void deleteUser(@PathVariable Integer id) {
        userService.deleteUser(id);
    }
}

4. Docker로 MySQL 컨테이너 띄우기

윈도우 11 Home 기준 Docker를 설치하려고 한다.

윈도우 11에는 기본적으로 wsl 명령어가 포함되어 있다.

아래 커맨드를 관리자 모드로 shell을 켠 후 수행한다.

wsl --install
wsl --set-default-version 2

wsl 설치 후 default version을 2로 설정하고 version을 확인해준다.

은근 오래 걸린다

wsl --version

이후 dockerdocs에서 윈도우용 docker를 설치한다.

이후 설치된 Docker Desktop Installer.exe 클릭해서 설치한다.

shortcut 추가해줘 도커도커야

close and restart를 눌러서 다시 실행한다.

도커 버전을 확인한다.

docker pull mysql:8.0

도커 데스크탑 Images에 mysql이 잘 생성된 것을 확인할 수 있다(왜 3 months ago인지는 모르겠다)

 

mySQL 컨테이너 실행 명령어는 아래와 같다.

docker run --name fortune-app-db -e MYSQL_ROOT_PASSWORD=1234 -p 3306:3306 -d mysql:8.0

이후에 컨테이너에 띄워진 mysql을 접속해보자.

docker exec -it fortune-app-db mysql -u root -p
Enter password: 1234

컨테이너 종료 명령어는 아래와 같다.

docker stop fortune-app-db

기존에 켜져있던 3306 포트 종료 후 docker 포트로 설정하였다.

5. Spring 설정(yml/properties)에서 DB 연결 정보 등록

자동으로 들어가야하는 데이터를 data.sql로 설정하였다.

INSERT INTO fortune (name) VALUES ('운세');
INSERT INTO fortune (name) VALUES ('사랑');
INSERT INTO fortune (name) VALUES ('커리어');
INSERT INTO fortune (name) VALUES ('금전');
INSERT INTO fortune (name) VALUES ('건강');

나머지 데이터들은 insert문으로 하기에 많아서 루프를 돌면서 insert하도록 하였다.

import com.fortuneApp.fortune_app_backend.dto.CardInterpretationDto;
import com.fortuneApp.fortune_app_backend.entity.CardEntity;
import com.fortuneApp.fortune_app_backend.entity.CardInterpretationEntity;
import com.fortuneApp.fortune_app_backend.entity.FortuneEntity;
import com.fortuneApp.fortune_app_backend.enums.Orientation;
import com.fortuneApp.fortune_app_backend.repository.CardInterpretationRepository;
import com.fortuneApp.fortune_app_backend.repository.CardRepository;
import com.fortuneApp.fortune_app_backend.repository.FortuneRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@RequiredArgsConstructor
public class DataInitializer implements CommandLineRunner {

    private final FortuneRepository fortuneRepository;
    private final CardRepository cardRepository;
    private final CardInterpretationRepository cardInterpretationRepository;

    @Override
    public void run(String... args) throws Exception {
        if (cardRepository.count() <= 0) {
            String[] majorArcana = {
                    "The Fool", "The Magician", "The High Priestess", "The Empress", "The Emperor",
                    "The Hierophant", "The Lovers", "The Chariot", "Strength", "The Hermit",
                    "Wheel of Fortune", "Justice", "The Hanged Man", "Death", "Temperance",
                    "The Devil", "The Tower", "The Star", "The Moon", "The Sun", "Judgment", "The World"
            };

            for (String name : majorArcana) {
                CardEntity upright = CardEntity.builder()
                        .name(name)
                        .orientation(Orientation.UPRIGHT)
                        .build();
                CardEntity reversed = CardEntity.builder()
                        .name(name)
                        .orientation(Orientation.REVERSED)
                        .build();

                cardRepository.save(upright);
                cardRepository.save(reversed);
            }

            String[] suits = {"Cups", "Wands", "Swords", "Pentacles"};
            String[] values = {
                    "Ace", "2", "3", "4", "5", "6", "7", "8", "9", "10",
                    "Page", "Knight", "Queen", "King"
            };

            for (String suit : suits) {
                for (String value : values) {
                    String cardName = value + " of " + suit;
                    CardEntity upright = CardEntity.builder()
                            .name(cardName)
                            .orientation(Orientation.UPRIGHT)
                            .build();
                    CardEntity reversed = CardEntity.builder()
                            .name(cardName)
                            .orientation(Orientation.REVERSED)
                            .build();

                    cardRepository.save(upright);
                    cardRepository.save(reversed);
                }
            }
        }

        if (cardInterpretationRepository.count() <= 0) {
            List<FortuneEntity> fortuneEntities = fortuneRepository.findAll();
            for (CardEntity card : cardRepository.findAll()) {
                for (FortuneEntity fortune : fortuneEntities) {
                    String interpretationContent = generateInterpretation(card.getName(), fortune.getType());

                    CardInterpretationEntity interpretation = CardInterpretationEntity.builder()
                            .card(card)
                            .fortune(fortune)
                            .interpretationContent(interpretationContent)
                            .build();

                    cardInterpretationRepository.save(interpretation);
                }
            }
        }
    }

    private String generateInterpretation(String cardName, String fortuneName) {
        return cardName + "의 " + fortuneName + " 해석";
    }
}

CommandLineRunner를 implements를 받고 있으면 실행 시 자동으로 해당 내용이 실행된다.

순서는 schema.sql -> data.sql -> CommandLineRunner이다.

 

또한 application.properties에 DB 설정과 JPA 설정을 추가하였다.

spring.application.name=fortune-app-backend
spring.datasource.url=jdbc:mysql://localhost:3306/fortune-app-db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=1234
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.defer-datasource-initialization=true

 

그리고 build.gradle에 mysql도 추가해준다.

runtimeOnly 'mysql:mysql-connector-java:8.0.33'

최종적으로 구현한 서비스와 DB가 잘 연동되었나 확인하기 위해 PostMan으로 request를 날려보자.

날짜는 UTC+9를 안해서 9시간 전으로 들어간다.

6. 추가 Error 보완

1) Entity에 @NoArgsConstructor 추가

NoArgsConstructor가 없을 경우 JPA 실행이 불가능해서 추가하였다.

2) 한글 insert 시 ??로 뜨는 문제

application.properties 아래와 같이 수정

spring.datasource.url=jdbc:mysql://localhost:3306/fortune-app-db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC

echo -e "[mysqld]\ncharacter-set-server=utf8mb4\ncollation-server=utf8mb4_unicode_ci\n\n[client]\ndefault-character-set=utf8mb4" > /etc/mysql/conf.d/my.cnf
cat /etc/mysql/conf.d/my.cnf
docker restart fortune-app-db
docker exec -it fortune-app-db bash
mysql -uroot -p
SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
+--------------------------+---------+

 

3) not null로 설정하기

Entity에서는 @Column(nullable = false) 로 컬럼 설정하고 DTO에서는 @NotNull(message = "Provider는 null일 수 없습니다.") 설정하고 Service에서는 @RequestBody 앞에 @Valid를 추가하자.

근데 NotNull이 deprecated되어서 일단은 @Column만 설정하였다.

implementation 'org.antlr:antlr4-runtime:4.11.1'

4) 삭제하고 다시 만든다면

CREATE DATABASE `fortune-app-db`;