day61 TIL

Database

Entity Relations

 

오늘의 실습

 

ERD에 맞게  entity 생성 

 

N:1 관계 표현하기 Lecture - Instructor

-> Lecture와 Instructor 테이블은 N:1 관계를 맺고 있습니다.

 

instructor와 lecture 만들기

1. instructor entity 생성

@Entity
public class instructor {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
}

2. lecture entity 생성

@Entity
public class lecture {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String day;
    private Integer startTime;
    private Integer endTime;
}

lecture와 instructor는 N:1 관계를 가진다.

 

만약 FK 컬럼의 이름을 바꾸고 싶다면 @JoinColumn 어노테이션을 사용하면 된다. 

@ManyToOne
@JoinColumn(name = "instructor_id")
private Instructor instructor;

 

@ManyToOne 어노테이션 추가 

@ManyToOne
    private Instructor instructor;

 

application.yaml 코드 추가

spring:
  datasource:
    url: jdbc:sqlite:db.sqlite
    driver-class-name: org.sqlite.JDBC
  jpa:
    hibernate:
      ddl-auto: create
    show-sql: true
    database-platform: org.hibernate.community.dialect.SQLiteDialect
    defer-datasource-initialization: true

  sql:
    init:
      mode: always

build.gradle 의존성 추가 

    // sqlite
    runtimeOnly 'org.xerial:sqlite-jdbc:3.41.2.2'
    runtimeOnly 'org.hibernate.orm:hibernate-community-dialects:6.2.4.Final'

 

프로그램 실행

db가 생성된 걸 확인할 수 있다.

lecture와 instructor에 @Data 어노테이션 추가 하기 

 

InstructorRepository 생성

public interface InstructorRepository extends JpaRepository<Instructor, Long> {
}

LectureRepostiory생성

public interface LectureRepository extends JpaRepository<Lecture, Long> {
    
}

 

resources-> data.sql 생성 후 코드 입력

insert into instructor (first_name, last_name)
values ('jeeho', 'park');

insert into lecture(day, name, start_time, end_time)
values ('tue', 'Database', 11, 16), ('tue', 'Algorithm', 9, 11), ('wed', 'Spring', 11, 16);

 

LectureController 생성 

package com.example.relations.school.controller;

import com.example.relations.school.entity.Instructor;
import com.example.relations.school.entity.Lecture;
import com.example.relations.school.repo.InstructorRepository;
import com.example.relations.school.repo.LectureRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.server.ResponseStatusException;

import java.util.Optional;

// 로거 설정
@Slf4j
// Spring Boot 요청 URL 엔드포인트
@RestController
@RequestMapping("lectures")
// 의존성 주입 위한 생성자 자동 생성
@RequiredArgsConstructor
public class LectureController {
    private final LectureRepository lectureRepository;
    private final InstructorRepository instructorRepository;

    // 강의에 강사를 배정
    @PutMapping("{id}/instructor/{instructorId}")
    // 응답 Body 가 없을 것이라고 선언해주는 것.
    @ResponseStatus(HttpStatus.NO_CONTENT)
    public void updateLectureInstructor(
            @PathVariable("id") Long id,
            @PathVariable("instructorId") Long instructorId
    ) {
        Optional<Lecture> optionalLecture
                = lectureRepository.findById(id);
        if (optionalLecture.isEmpty()) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND);
        }

        Optional<Instructor> optionalInstructor
                = instructorRepository.findById(instructorId);
            if (optionalInstructor.isEmpty()) {
                throw new ResponseStatusException(HttpStatus.NOT_FOUND);
            }
        Lecture lecture = optionalLecture.get();
        Instructor instructor = optionalInstructor.get();

        // 그냥 자바객체 쓰듯이 instructor 를 설정해줌(넣어줌)
        lecture.setInstructor(instructor);
        lectureRepository.save(lecture);
    }

    public void readLectureInstructor(Long id){
        Optional<Lecture> optionalLecture
                = lectureRepository.findById(id);
        if (optionalLecture.isEmpty())
            throw new ResponseStatusException(HttpStatus.NOT_FOUND);

        Lecture lecture = optionalLecture.get();
        Instructor instructor = lecture.getInstructor();
        log.info(instructor.toString());
    }
}

InstructorController 생성

package com.example.relations.school.controller;

import com.example.relations.school.entity.Instructor;
import com.example.relations.school.entity.Lecture;
import com.example.relations.school.repo.InstructorRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Optional;

@Slf4j
@RestController
@RequestMapping("instructor")
@RequiredArgsConstructor
public class InstructorController {
    private final InstructorRepository instructorRepository;

    @GetMapping("{id}/lectures")
    public void readInstuctorLectures(
            @PathVariable("id") Long id
    ){
        Optional<Instructor> optionalInstructor
                = instructorRepository.findById(id);

        Instructor instructor = optionalInstructor.get();
        for (Lecture lecture : instructor.getLectures()){
            log.info(lecture.getName());
        }
    }
}

POSTMAN 실행

lecture db

포스트맨 http://localhost:8080/lectures/3/instructor/1 

lectureId를 1~3번까지 순서대로 넣어본다.

Lecturedb에 Id가 생성된걸 확인할 수 있다.

OneToMany

-> 반대로 Instructor와 lecture의 정보를 알고싶다면, 1:N 관계를 표현해줄 수 있다.

instructor에 lectures 필드를 만들고 @OneToMany 어노테이션으로 관계를 설정하면 된다. 

 

 

M:N 관계 표현하기 Student - Lecture

Student 생성

먼저 Student entity생성

@Entity
@Data
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;
    private String lastName;

    @ManyToMany
    private List<Lecture> attending;
}

studentRepository 생성

public interface StudentRepository extends JpaRepository<Student, Long> {
}

lecture entity 코드 수정

package com.example.relations.school.entity;

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

import java.util.List;

@Data
@Entity
public class Lecture {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String day;
    private Integer startTime;
    private Integer endTime;

    @ManyToOne
//    @JoinColumn(name = "instructor")
    private Instructor instructor;

    @ManyToMany(mappedBy = "attending")
    private List<Student> students;
}

data.sql 'student' 코드 추가

insert into student(first_name, last_name)
values ('alex', 'a'), ('brad', 'b'), ('chad', 'c');

실행해보면 database에 'student' 와 'student_attending' 항목이 생성된걸 볼 수 있다. 

db도 잘 들어온걸 확인할 수 있다.

마지막으로 student_attending db가 들어갈 수 있도록  StudentController생성

package com.example.relations.school.controller;

import com.example.relations.school.entity.Lecture;
import com.example.relations.school.entity.Student;
import com.example.relations.school.repo.LectureRepository;
import com.example.relations.school.repo.StudentRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.server.ResponseStatusException;

import java.util.Optional;

@RestController
@RequestMapping("students")
@RequiredArgsConstructor
public class StudentController {
    private final StudentRepository studentRepository;
    private final LectureRepository lectureRepository;

    @PutMapping("{id}/lectures/{lecturesId}")
    public void updateStudentLectures(
            @PathVariable("id") Long id,
            @PathVariable("lectureId") Long lecturesId
    ){
        Optional<Student> optionalStudent
        = studentRepository.findById(id);
        if (optionalStudent.isEmpty())
            throw new ResponseStatusException(HttpStatus.NOT_FOUND);

        Optional<Lecture> optionalLecture
                = lectureRepository.findById(lecturesId);
        if (optionalLecture.isEmpty())
            throw new ResponseStatusException(HttpStatus.NOT_FOUND);

        Student student = optionalStudent.get();
        Lecture lecture = optionalLecture.get();

        student.getAttending().add(lecture);
        studentRepository.save(student);

    }
}

실행 결과