[12.03] 내일배움캠프[Spring] TIL-25

[12.03] 내일배움캠프[Spring] TIL-25

1. 스프링 입문

DB SQL 을 이해하기 위해 기본 쿼리문 배워보기

• Create Table

CREATE TABLE IF NOT EXISTS MAJOR
(        
        major_code varchar(100) primary key comment '주특기코드', 
        major_name varchar(100) not null comment '주특기명',
        tutor_name varchar(100) not null comment '튜터'
)

CREATE TABLE IF NOT EXISTS STUDENT
(
        student_code varchar(100) primary key comment '수강생코드', 
        name varchar(100) not null comment '이름',
        birth varchar(8) null comment '생년월일',
        gender varchar(1) not null comment '성별',
        phone varchar(11) null comment '전화번호',
        major_code varchar(100) not null comment '주특기코드',
        foreign key(major_code) references major(major_code)
)

CREATE TABLE IF NOT EXISTS EXAM
(
        student_code varchar(100) not null comment '수강생코드', 
        exam_seq int not null comment '시험주차', 
        score decimal(10,2) not null comment '시험점수',
        result varchar(1) not null comment '합불'
)

CREATE TABLE MANAGER(
    id bigint PRIMARY KEY comment '관리테이블 키',
    name varchar(3) not null comment '회원 이름',
    student_code varchar(100) not null comment '회원 코드',
    CONSTRAINT manager_fk_student_code foreign key(student_code) references STUDENT(STUDENT_CODE)
)

• Diagram
  

• Alter Table

ALTER TABLE MANAGER ALTER COLUMN id bigint auto_increment;
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's1');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's2');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's3');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's4');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's5');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's6');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's7');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's8');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's9');

• Join

SELECT s.name, e.exam_seq, e.score
FROM MANAGER m JOIN STUDENT S on m.student_code  = s.student_code 
JOIN EXAM e on m.student_code  = e.student_code WHERE m.name = 'managerA';

• Constraint( 제약 조건 )

LTER TABLE EXAM DROP CONSTRAINT exam_fk_student_code;
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
ALTER TABLE MANAGER DROP CONSTRAINT manager_fk_student_code;
ALTER TABLE MANAGER ADD CONSTRAINT manager_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
//ON DELETE CASCADE -> 지울 때 같이 지울 것!

• 중간에 AUTO_INCREMENT 값이 1부터 시작하지 않아서 다시 속성 바꾸고 싶었을 떄 사용했던 것

ALTER TABLE [TABLE명] AUTO_INCREMENT = [시작할 값];

2. Spring 써보기

• 전반적인 구조 파악하기
  
  
  1) 요청이 들어온다.( URL , GET? POST? PUT? DELETE? PARAM? )
  2) 들어온 요청을 바탕으로 DispatcherServlet이 Handlermapping에 적절한 Controller를 찾음
  3) 해당 URL을 바탕으로 호출해야 할 함수 로직을 시행 한 후 Model에 파싱할 Data, 호출해야 할 View를 DispatcherServlet를 넘겨주고, ViewResoler를 거쳐 해당 View로 랜더링 됨.

• 주로 사용했던 라이브러리 정리
  1) Spring Web
  2) Lombok -> @Getter,Setter,@RequiedArgsConstructor 등 제공.
  3) Dev Tool -> 소스 수정시 자동 컴파일 다시 재 기동 할 필요성 없어짐.
  4) H2 -> DB

• 프로젝트는 Repository,DTO,Entity,Service,Controller로 구성

• Controller/MemoController

package com.sparta.hanghaememo.controller;

import com.sparta.hanghaememo.dto.MemoRequestDTO;
import com.sparta.hanghaememo.entity.Memo;
import com.sparta.hanghaememo.service.MemoService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

import java.util.List;

@RestController // ResponseBody을 따로 해주지 않아도 먹게끔 하는 어노테이션
@RequiredArgsConstructor
public class MemoController {

    private final MemoService memoService;

    @GetMapping("/")
    public ModelAndView home() {
        return new ModelAndView("index"); // templates에 반환할 html파일이름을 명시하는 것
    }

    @PostMapping("/api/memos")
    public Memo createMemo(@RequestBody MemoRequestDTO requestDto){

        return memoService.createMemo(requestDto);

    }

    @GetMapping("/api/memos")
    public List<Memo> getMemos(){
        return memoService.getMemos();
    }

    @PutMapping("/api/memos/{id}")
    public Long updateMemo(@PathVariable Long id,@RequestBody MemoRequestDTO requestDto){
        return memoService.update(id,requestDto);
    }

    @DeleteMapping("/api/memos/{id}")
    public Long deleteMemo(@PathVariable Long id){
        return memoService.deleteMemo(id);


}

• dto/MemoDTO

package com.sparta.hanghaememo.dto;

import lombok.Getter;

@Getter
public class MemoRequestDTO {

    private String username;
    private String contents;

}

• Entity를 사용하지 않고 DTO를 따로 만들어서 사용하는 이유 참조 블로그:
  https://wildeveloperetrain.tistory.com/101

• entity/Memo

package com.sparta.hanghaememo.entity;

//import com.sparta.hanghaememo.dto.MemoRequestDto;
import com.sparta.hanghaememo.dto.MemoRequestDTO;
import com.sparta.hanghaememo.repository.MemoRepository;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.NoArgsConstructor;



@Getter
@Entity
@NoArgsConstructor
public class Memo extends Timestamped {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(nullable = false)
    private String username;

    @Column(nullable = false)
    private String contents;

    public Memo(MemoRequestDTO requestDto) {
        this.username = requestDto.getUsername();
        this.contents = requestDto.getContents();
    }

    public void update(MemoRequestDTO requestDto) {
        this.username = requestDto.getUsername();
        this.contents = requestDto.getContents();
    }

//    public Memo(MemoRequestDto requestDto) {
//        this.username = requestDto.getUsername();
//        this.contents = requestDto.getContents();
//    }
//

}

• 의문: Update쿼리 사용시, Repository에 접근하지 않고, Entity에서 직접 작업한 점
  👉 아마 Remote Data 가 아닌 in - Memory구조의 H2를 사용하고 있어서, 여기가 DB역할을 하는 것 같다..!

• entity/TimeStamped
  👉 DB추가시 생성날짜를 자동으로 기록하기 위한 class

package com.sparta.hanghaememo.entity;

import jakarta.persistence.EntityListeners;
import jakarta.persistence.MappedSuperclass;
import lombok.Getter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import java.time.LocalDateTime;

@Getter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public class Timestamped {

    @CreatedDate
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime modifiedAt;
}

• 생성일자등 자동생성 어노테이션 참조 블로그 : https://webcoding-start.tistory.com/53

• repository/MemoRepository

package com.sparta.hanghaememo.repository;


import com.sparta.hanghaememo.entity.Memo;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface MemoRepository extends JpaRepository<Memo, Long> {
    List<Memo> findAllByOrderByModifiedAtDesc();//기본 메서드에서 추가 기능을 오름차순 정렬을 위한 추가 메서드
}

• service/MemoService

package com.sparta.hanghaememo.service;

import com.sparta.hanghaememo.dto.MemoRequestDTO;
import com.sparta.hanghaememo.entity.Memo;
import com.sparta.hanghaememo.repository.MemoRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@RequiredArgsConstructor
public class MemoService {

    private final MemoRepository memoRepository;

    @Transactional
    public Memo createMemo(MemoRequestDTO requestDto) {
        Memo memo = new Memo(requestDto);
        memoRepository.save(memo);
        return memo;
    }

   @Transactional(readOnly = true)
    public List<Memo> getMemos() {
       return memoRepository.findAllByOrderByModifiedAtDesc();
    }
    @Transactional
    public Long update(Long id, MemoRequestDTO requestDto) {
        Memo memo = memoRepository.findById(id).orElseThrow(
                () -> new IllegalArgumentException("아이디가 존재하지 않습니다!")
        );
        memo.update(requestDto); //이 부분이 좀 특이함!
        return memo.getId();
    }

    @Transactional
    public Long deleteMemo(Long id) {

        memoRepository.deleteById(id);
        return id;
    }
}

• Main(구동)

package com.sparta.hanghaememo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing // 생성시간등을 자동 생성하는 어노테이션

public class HanghaememoApplication  {

    public static void main(String[] args) {
        SpringApplication.run(HanghaememoApplication.class, args);
    }

}