📚 공부한 책 : 코드로배우는 스프링 부트 웹프로젝트
❤️ github 주소 : https://github.com/qkralswl689/LearnFromCode/tree/main/board2022

1.sort/count 처리

Pageable의 Sort 객체는 JPQLQuery의 orderBy()의 파라미터로 전달되어야 하지만 JPQL에서는 Sort 객체를 지원하지 않기 때문에 orderBy()의 경우 OrderSpecifier을 파라미터로 처리해야 한다.

• OrderSpecifier 에는 정렬이 힐요하므로 Sort 객체의 정렬 관련 정보를 Order 타입으로 처리하고,Sort 객체의 속성(bno,title)등은 PathBuilder 로 처리한다
• PathBuilder 를 생성할 때 문자열로 된 이름은 JPQLQuery를 생성할 때 이용하는 변수명과 동일해야 한다

1-1.RepositoryImpl 작성

• count를 얻는 방법 : fetchCount() 이용
  -Pageable을 파라미터로 전달받은 이유 : JPQLQuery의 offset()와 limit()를 이용해 페이지 처리를 하기 위해
• searchPage()의 리턴 타입은 Page<Object[]>타입으로 메서드 내부에서 Page 타입의 객체를 생성해야 한다

import com.example.board2022.entity.Board;
import com.example.board2022.entity.QBoard;
import com.example.board2022.entity.QMember;
import com.example.board2022.entity.QReply;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import java.util.List;
import java.util.stream.Collectors;


public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {
	
  	//...생략

    @Override
    public Page<Object[]> searchPage(String type, String keyword, Pageable pageable) {

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;
        QMember member = QMember.member;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(member).on(board.writer.eq(member));
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        JPQLQuery<Tuple> tuple = jpqlQuery.select(board,member,reply.count());

        BooleanBuilder booleanBuilder = new BooleanBuilder();
        BooleanExpression expression = board.bno.gt(0L);

        booleanBuilder.and(expression);

        if(type != null) {
            String[] typeArr = type.split("");

            // 검색 조건 작성
            BooleanBuilder conditionBuilder = new BooleanBuilder();

            for(String t : typeArr) {
                switch (t){
                    case "t" :
                        conditionBuilder.or(board.title.contains(keyword));
                        break;
                    case "w" :
                        conditionBuilder.or(member.email.contains(keyword));
                        break;
                    case "c" :
                        conditionBuilder.or(board.content.contains(keyword));
                        break;
                }
            }
            booleanBuilder.and(conditionBuilder);
        }

        tuple.where(booleanBuilder);

        // order by
        Sort sort = pageable.getSort();

        //tuple.orderBy(board.bno.desc());
        sort.stream().forEach(order -> {
            Order direction = order.isAscending()? Order.ASC:Order.DESC;

            String prop = order.getProperty();

            PathBuilder orderByExpression = new PathBuilder(Board.class,"board");

            tuple.orderBy(new OrderSpecifier(direction,orderByExpression.get(prop)));

        });

        tuple.groupBy(board);

        //page 처리
        tuple.offset(pageable.getOffset());
        tuple.limit(pageable.getPageSize());

        List<Tuple> result = tuple.fetch();

        long count = tuple.fetchCount(); //count를 얻는 방법

        return new PageImpl<Object[]>(result.stream().map(t -> t.toArray()).collect(Collectors.toList()),pageable,count);
    }
}

1-2.테스트 코드 작성

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import java.util.Optional;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

   // ... 생략
    @Test	
    public void testSearchPage(){

        Pageable pageable = PageRequest.of(0,10,Sort.by("bno").descending());

        Page<Object[]> result = boardRepository.searchPage("t","1",pageable);
    }
}

• 실행 쿼리

  고의적으로 중첩되는 Sort 조건을 만들어 추가했다
  -> order by 조건이 만들어진 것과 목록을 위한 SQL과 count 처리를 위한 SQL이 실행되는 것을 확인할 수 있다

Hibernate: 
    select
        board0_.bno as col_0_0_,
        member1_.email as col_1_0_,
        count(reply2_.rno) as col_2_0_,
        board0_.bno as bno1_0_0_,
        member1_.email as email1_1_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        member1_.moddate as moddate2_1_1_,
        member1_.regdate as regdate3_1_1_,
        member1_.name as name4_1_1_,
        member1_.password as password5_1_1_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on (
                board0_.writer_email=member1_.email
            ) 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    where
        board0_.bno>? 
        and (
            board0_.title like ? escape '!' // 검색 조건 처리
        ) 
    group by
        board0_.bno 
    order by	//order by 추가된것 확인
        board0_.bno desc limit ?
  
Hibernate: // count를 처리하는 쿼리
    select
        count(distinct board0_.bno) as col_0_0_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on (
                board0_.writer_email=member1_.email
            ) 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    where
        board0_.bno>? 
        and (
            board0_.title like ? escape '!'
        )

2.목록 화면에서 검색처리

2-1.화면(html) 작성

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<th:block th:replace="~{/layout/basic :: setContent(~{this::content} )}">

    <th:block th:fragment="content">

        <h1 class="mt-4">Board List Page
            <span>
                <a th:href="@{/board/register}">
                    <button type="button" class="btn btn-outline-primary">REGISTER
                    </button>
                </a>
            </span>
        </h1>
      <!-- 추가 -->
        <form action="/board/list" method="get" id="searchForm">
            <div class="input-group">
                <input type="hidden" name="page" value="1">
                <div class="input-group-prepend">
                    <select class="custom-select" name="type">
                        <option th:selected="${pageRequestDTO.type == null}">-------</option>
                        <option value="t" th:selected="${pageRequestDTO.type =='t'}">제목</option>
                        <option value="t" th:selected="${pageRequestDTO.type =='c'}">내용</option>
                        <option value="t" th:selected="${pageRequestDTO.type =='w'}">작성자</option>
                        <option value="tc" th:selected="${pageRequestDTO.type =='tc'}">제목 + 내용</option>
                        <option value="tcw" th:selected="${pageRequestDTO.type =='tcw'}">제목 + 내용 + 작성자</option>
                    </select>
                </div>
                <input class="form-control" name="keyword" th:value="${pageRequestDTO.keyword}">
                <div class="input-group-append" id="button-addon4">
                    <button class="btn btn-outline-secondary btn-search" type="button">Search</button>
                    <button class="btn btn-outline-secondary btn-clear" type="button">Clear</button>
                </div>
            </div>
        </form>
     <!-- 추가 끝-->
      
        <table class="table table-striped">
            <thead>
            <tr>
                <th scope="col">#</th>
                <th scope="col">Title</th>
                <th scope="col">Writer</th>
                <th scope="col">Regdate</th>
            </tr>
            </thead>
            <tbody>

            <tr th:each="dto : ${result.dtoList}">
                <th scope="row">
                    <a th:href="@{/board/read(bno = ${dto.bno},
                    page= ${result.page},
                    type=${pageRequestDTO.type} ,
                    keyword = ${pageRequestDTO.keyword})}">
                        [[${dto.bno}]]
                    </a>
                </th>
                <td>[[${dto.title}]] ---------------- [<b th:text="${dto.replyCount}"></b>]</td>
                <td>[[${dto.writerName}]] <small>[[${dto.writerEmail}]]</small></td>
                <td>[[${#temporals.format(dto.regDate, 'yyyy/MM/dd')}]]</td>
            </tr>


            </tbody>
        </table>

        <ul class="pagination h-100 justify-content-center align-items-center">

            <li class="page-item " th:if="${result.prev}">
                <a class="page-link" th:href="@{/board/list(page= ${result.start -1},
                    type=${pageRequestDTO.type} ,
                    keyword = ${pageRequestDTO.keyword} ) }" tabindex="-1">Previous</a>
            </li>

            <li th:class=" 'page-item ' + ${result.page == page?'active':''} " th:each="page: ${result.pageList}">
                <a class="page-link" th:href="@{/board/list(page = ${page} ,
                   type=${pageRequestDTO.type} ,
                   keyword = ${pageRequestDTO.keyword}  )}">
                    [[${page}]]
                </a>
            </li>

            <li class="page-item" th:if="${result.next}">
                <a class="page-link" th:href="@{/board/list(page= ${result.end + 1} ,
                    type=${pageRequestDTO.type} ,
                    keyword = ${pageRequestDTO.keyword} )}">Next</a>
            </li>

        </ul>

    </th:block>

</th:block>

2-2.ServiceImpl 작성

PageResultDTO 를 아래와 같이 수정해준다

import com.example.board2022.dto.BoardDTO;
import com.example.board2022.dto.PageRequestDTO;
import com.example.board2022.dto.PageResultDTO;
import com.example.board2022.entity.Board;
import com.example.board2022.entity.Member;
import com.example.board2022.repository.BoardRepository;
import com.example.board2022.repository.ReplyRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.util.function.Function;

@Service
@RequiredArgsConstructor
public class BoardServiceImpl implements BoardService {

    @Autowired
    private final BoardRepository repository; //자동주입 final

    @Override
    public PageResultDTO<BoardDTO, Object[]> getList(PageRequestDTO pageRequestDTO) {

        Function<Object[], BoardDTO> fn = (en -> entityToDTO((Board) en[0], (Member) en[1], (Long) en[2]));
		
        // Page<Object[]> result = repository.getBoardWithReplyCount(pageRequestDTO.getPageable(Sort.by("bno").descending()));

        Page<Object[]> result = repository.searchPage(
                pageRequestDTO.getType(),
                pageRequestDTO.getKeyword(),
                pageRequestDTO.getPageable(Sort.by("bno").descending()));
        
        return new PageResultDTO<>(result, fn);
    }
}

• 실행 결과