[v3] SpringBoot 블로그 만들기 -2

1. 테이블 쪼개기

2. 연관관계 설정

3. 엔티티

@Setter
@Getter
@Table(name = "reply_tb")
@NoArgsConstructor
@Entity
public class Reply {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String comment; //댓글 내용

    @ManyToOne(fetch = FetchType.LAZY)
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    private Board board;

    @CreationTimestamp
    private Timestamp createdAt;
}

3.1 게시글 조회 쿼리

    @Query("select b from Board b join fetch b.user u where b.id=:id")
    Optional<Board> mfindById(@Param("id") Integer id);

4. 게시글 엔티티 수정(양방향 매핑)

package org.example.springv3.board;

import jakarta.persistence.*;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import org.example.springv3.reply.Reply;
import org.example.springv3.user.User;
import org.hibernate.annotations.CreationTimestamp;

import java.sql.Timestamp;
import java.util.List;

@NoArgsConstructor
@Setter
@Getter
@Table(name = "board_tb")
@Entity
public class Board {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id // PK 설정
    private Integer id;
    @Column(nullable = false)
    private String title;
    @Column(nullable = false)
    private String content;
    @CreationTimestamp
    private Timestamp createdAt;

    // fk
    @ManyToOne(fetch = FetchType.LAZY)
    private User user;

    @OneToMany(mappedBy = "board")
    private List<Reply> replies;

    @Builder
    public Board(Integer id, String title, String content, Timestamp createdAt, User user) {
        this.id = id;
        this.title = title;
        this.content = content;
        this.createdAt = createdAt;
        this.user = user;
    }
}

SQL 쿼리와 서브쿼리 활용

1. 게시글, 작성자 정보 조회

SELECT * 
FROM board_tb bt
inner join user_tb ut on bt.user_id = ut.id; 

1. 특정 게시글 조회

SELECT * 
FROM board_tb bt
inner join user_tb ut on bt.user_id = ut.id
where bt.id=5;

SELECT bt.id, bt.title, bt.content, ut.id user_id, ut.username 
FROM board_tb bt
INNER JOIN user_tb ut ON bt.user_id = ut.id
where bt.id = 5

1. 인라인 뷰 활용
서브쿼리를 활용하여 필터링 합니다.

SELECT *
FROM
(
	SELECT bt.id, bt.title, bt.content, ut.username 
	FROM board_tb bt
	inner join user_tb ut on bt.user_id = ut.id
	where bt.id=5
);

select *
from
(
	SELECT bt.id, bt.title, bt.content, ut.id u_id, ut.username 
	FROM board_tb bt
	INNER JOIN user_tb ut ON bt.user_id = ut.id
);

select *
from
(
	SELECT bt.id, bt.title, bt.content, ut.id u_id, ut.username 
	FROM board_tb bt
	INNER JOIN user_tb ut ON bt.user_id = ut.id
)
where u_id=2;

이미 선택이 완료되었기 때문에 where 절에서 u_id 라는 별칭 사용이 가능하다.

1. 서브쿼리 결과를 다시 조인하기
게시글 정보와 댓글을 함께 조회하며, 댓글을 작성한 작성자 정보를 함께 조회

SELECT *
FROM
	(
		SELECT bt.id, bt.title, bt.content, ut.username 
		FROM board_tb bt
		inner join user_tb ut on bt.user_id = ut.id
		where bt.id=5
	)t1
inner join reply_tb t2 on t1.id = t2.board_id;

1. 다중 조인 및 댓글 정보 조회

    SELECT * FROM board_tb bt 
        INNER JOIN user_tb ut ON bt.user_id = ut.id 
        INNER JOIN reply_tb rt on bt.id = rt.board_id
        INNER JOIN user_tb rut on rut.id = rt.user_id
        where bt.id = 5;

SELECT * FROM board_tb bt 
INNER JOIN user_tb ut ON bt.user_id = ut.id 
LEFT OUTER JOIN reply_tb rt on bt.id = rt.board_id
LEFT OUTER JOIN user_tb rut on rut.id = rt.user_id
where bt.id = 2;