게시판 목록보기 (in query 만들기)

1. 조회 전략

• 우선 Lazy 전략으로 Board만 조회

• Lazy loading을 통해 user들을 in query로 조회 (쿼리 2번만)

2. 조회 쿼리 작성

• 아래와 같이 조회를 두번한다.

• 두 개의 결과를 2중 for문을 돌면서 filter로 매칭시킨다.

package shop.mtcoding.blog.board;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;
import shop.mtcoding.blog.user.User;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

@RequiredArgsConstructor
@Repository
public class BoardRepository {
    private final EntityManager em;

    public List<Board> findAllV3(){
        String q1 = "select b from Board b order by b.id desc";
        List<Board> boardList = em.createQuery(q1 , Board.class).getResultList();

        int[] userIds = boardList.stream().mapToInt(board -> board.getUser().getId()).distinct().toArray();

        String q2 = "select u from User u where u.id in (";
        for (int i = 0; i < userIds.length ; i++) {
            if (i == userIds.length - 1){
                q2 = q2 + userIds[i] + ")";
            }else {
                q2 = q2 + userIds[i] + ",";
            }
        }
        List<User> userList = em.createQuery(q2 , User.class).getResultList();

        for (Board board : boardList){
            for (User user : userList) {
                if (user.getId() == board.getUser().getId()){
                    board.setUser(user);
                }
            }
        }
        return boardList; // user가 채워져 있어야함.
    }

    public List<Board> findAllV2() {
        Query q1 = em.createQuery("select b from Board b order by b.id desc", Board.class);
        List<Board> boardList = q1.getResultList();

        Set<Integer> userIds = new HashSet<>();
        for (Board board : boardList){
            userIds.add(board.getUser().getId());
        }

        Query q2 = em.createQuery("select u from User u where u.id in :userIds", User.class);
        q2.setParameter("userIds", userIds);
        List<User> userList = q2.getResultList();

        for (Board board : boardList){
            for (User user : userList) {
                if (user.getId() == board.getUser().getId()){
                    board.setUser(user);
                }
            }
        }
        return boardList;
    }

    public List<Board> findAll() { // 다른 정보가 추가로 필요하면 조인하면 됨 - outer join일 때 outer는 생략 가능
        Query query = em.createQuery("select b from Board b order by b.id desc", Board.class);
        return query.getResultList();
    }

    public Board findByIdJoinUser(int id) { // on생략 가능, 알아서 pk연결해줌
        Query query = em.createQuery("select b from Board b join fetch b.user u where b.id = :id", Board.class);
        query.setParameter("id", id);
        return (Board) query.getSingleResult();
    }

    public Board findById(int id) {
        //id, title, content, user_id(이질감), created_at
        Board board = em.find(Board.class, id);
        return board;
    }
}

3. 조회 쿼리 테스트

• V2 테스트하기

@Test
public void findAllV2_test(){
    List<Board> boardList = boardRepository.findAllV2();
    System.out.println("findAllV2_test : 조회완료 쿼리 2번");
    boardList.forEach(board -> {
        System.out.println(board);
    });
}

• V3 테스트하기

@Test
    public void findAllV3_test(){
        List<Board> boardList = boardRepository.findAllV2();
        System.out.println("findAllV3_test : 조회완료 쿼리 2번");
        boardList.forEach(board -> {
            System.out.println(board);
        });
    }