[bootBoard] N:1(다대일) 연관관계: 11-6. JPQLQuery로 Page<Object[]> 처리: sort 처리 / count 처리

• pageable의 Sort 객체는 JPQLQuery의 orderBy()의 파라미터로 전달되어야 하지만 JPQL에서는 Sort 객체를 지원하지 않는다
• 따라서 orderBy()의 경우 OrderSpecifier<T extends Comparable>을 파라미터로 처리해야 한다
• OrderSpecifier(Order order, Expression<T> target)
• OrderSpecifier의 Order는 com.querydsl.coreTypes.Order 타입이다.
• Expression은 com.querydsl.core.Expression이다.

 

1. SearchBoardRepository 수정: sort / count 처리를 위해

 

추가되는 코드

 

//SearchBoardRepositoryImpl.java        
        
        
        ///...
        
        //Order by : sort
        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)));
        });
        
        
        ///...

 

아래는 추가된 코드를 비롯한 전체 코드. import에 주의할 것.

 

//SearchBoardRepository.java

package com.example.bootboard.repository.search;

import com.example.bootboard.entity.*;
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 lombok.extern.log4j.Log4j2;
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;

import static com.example.bootboard.entity.QBoard.board;

@Log4j2
public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport
        implements SearchBoardRepository {

    //생성자
    public SearchBoardRepositoryImpl(){
        super(Board.class); //여기서 super는 querydslRepositorySupport이다.
    }

    @Override
    public Board search1() {
        log.info("search1............");
        //JPQL 사용
        //이제 로그가 찍힐 뿐만 아니라 아래의 JPQL도 실행된다.

        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));

        //집합함수 처리 : groupBy()

        //튜플 사용!!!!!
        JPQLQuery<Tuple> tuple = jpqlQuery.select(board, member.email, reply.count());
        tuple.groupBy(board);

//        jpqlQuery.select(board, member.email, reply.count())
//                .groupBy(board);

        log.info("------------------------------");
        log.info(tuple); //jpqlQuery -> tuple
        log.info("------------------------------");

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

        log.info(result);

        return null;
    }



//        조인 처리
//        QBoard board = QBoard.board;
//        QReply reply = QReply.reply;
//
//        JPQLQuery<Board> jpqlQuery = from(board);
//        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));
//
//        List<Board> result = jpqlQuery.fetch();
//        return null;
//    }



//        //Q도메인의 QBoard
//        QBoard board = QBoard.board;

//        JPQLQuery<Board> jpqlQuery = from(board);
//        jpqlQuery.select(board).where(board.bno.eq(3L)); //bno가 3번인 데이터를 select
//
//        log.info("------------------------------");
//        log.info(jpqlQuery);
//        log.info("------------------------------");
//
//        List<Board> result = jpqlQuery.fetch();
//        return null;
//    }

    @Override
    public Page<Object[]> searchPage(String type, String keyword, Pageable pageable) {
        log.info("searchPage............................");

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

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

        //SELECT b, w, count(r) FROM Board b
        //LEFT JOIN b.writer w LEFT JOIN Reply r ON r.board = b
        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 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();

        log.info(result);
        
        //count얻기
        long count = tuple.fetchCount();
        log.info("COUNT: " + count);

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

    }
}

• org.springframework.data.domain.Sort는 내부적으로 여러 개의 Sort 객체를 연결할 수 있기 때문에 forEach()를 이용해서 처리한다.
• OrderSpecifier에는 정렬이 필요하므로 Sort 객체의 정렬 관련 정보를 com.querydsl.core.types.Order 타입으로 처리하고 Sort 객체에 속성(bno, title) 등은 PathBuilder를 사용해서 처리한다.
• PathBuilder를 생성할 때 문자열로 된 이름은 JPQLQuery를 생성할 때 이용하는 변수명과 동일해야 한다.

 

• JPQLQuery를 이용해 동적으로 검색조건을 처리하는 과정을 복잡하다. 그러나 한 번의 개발만으로 count쿼리도 같이 처리할 수 있는 것이 장점이다. 
• count를 얻는 방법은 fetchCount()를 이용하면 된다.

//count얻기
        long count = tuple.fetchCount();
        log.info("COUNT: " + count);

 

Pageable을 파라미터로 전달받은 이유는 JPQLQuery의 offset()과 limit()를 사용해서 페이지 처리를 진행하기 위해서이다. ("//Page처리" 부분)

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

 

• SearchBoardRepositoryImpl 클래스의 searchPage()의 리턴 타입은 Page<Object[]>타입이다. 따라서 메서드 내부에서 Page타입의 객체를 생성해야 한다.
• Page는 인터페이스 타입이므로 실제 객체는 org.springframework.data.domain.PageImpl 클래스를 사용하여 생성한다.
• PageImpl 클래스의 생성자에는 Pageable과 long 값을 이용하는 생성자가 존재한다.

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

 

 

 

 

 

2. 테스트

 

Page<Object[]> result = boardRepository.searchPage("t", "3", pageable); //책에선 1, 난 3을 넣었음

//BoardRepositoryTests.java



@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    private BoardRepository boardRepository;

///...

	//여기서 테스트
    @Test
    public void testSearchPage2(){
        Pageable pageable =
                PageRequest.of(0, 10,
                        Sort.by("bno").descending()
                                .and(Sort.by("title").ascending()));
        Page<Object[]> result = boardRepository.searchPage("t", "3", pageable); //책에선 1, 난 3을 넣었음
    }

///...

}

/*
* testInsert()는 한 명의 사용자가 하나의 게시물을 등록하도록 작성되었다.
* */

 

 

결과

 

콘솔 로그

 

 

테스트는 고의적으로 중첩되는 Sort 조건을 만들어서 추가하였다.

order by 조건이 만들어진 것과, 목록을 위한 SQL과 count처리를 위한 SQL이 실행되는 것을 확인할 수 있다.

2022-10-12 09:07:09.157  INFO 9560 --- [    Test worker] c.e.b.repository.BoardRepositoryTests    : Started BoardRepositoryTests in 3.978 seconds (JVM running for 5.442)
2022-10-12 09:07:09.331  INFO 9560 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : searchPage............................
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_.reg_date as reg_date3_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_.reg_date as reg_date3_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
        board0_.bno desc,
        board0_.title asc limit ?
2022-10-12 09:07:09.448  INFO 9560 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : [[Board(bno=95, title=Title...93, content=Content...93), Member(email=user93@aaa.com, password=11111, name=USER93), 0], [Board(bno=85, title=Title...83, content=Content...83), Member(email=user83@aaa.com, password=11111, name=USER83), 0], [Board(bno=75, title=Title...73, content=Content...73), Member(email=user73@aaa.com, password=11111, name=USER73), 1], [Board(bno=65, title=Title...63, content=Content...63), Member(email=user63@aaa.com, password=11111, name=USER63), 0], [Board(bno=55, title=Title...53, content=Content...53), Member(email=user53@aaa.com, password=11111, name=USER53), 0], [Board(bno=45, title=Title...43, content=Content...43), Member(email=user43@aaa.com, password=11111, name=USER43), 0], [Board(bno=41, title=Title...39, content=Content...39), Member(email=user39@aaa.com, password=11111, name=USER39), 0], [Board(bno=40, title=Title...38, content=Content...38), Member(email=user38@aaa.com, password=11111, name=USER38), 1], [Board(bno=39, title=Title...37, content=Content...37), Member(email=user37@aaa.com, password=11111, name=USER37), 0], [Board(bno=38, title=Title...36, content=Content...36), Member(email=user36@aaa.com, password=11111, name=USER36), 0]]
Hibernate: 
    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 '!'
        )
2022-10-12 09:07:09.458  INFO 9560 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : COUNT: 19
2022-10-12 09:07:09.493  INFO 9560 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-10-12 09:07:09.496  INFO 9560 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-10-12 09:07:09.504  INFO 9560 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Deprecated Gradle features were used in this build, making it incompatible with Gradle 8.0.

You can use '--warning-mode all' to show the individual deprecation warnings and determine if they come from your own scripts or plugins.

See https://docs.gradle.org/7.5/userguide/command_line_interface.html#sec:command_line_warnings
BUILD SUCCESSFUL in 7s
6 actionable tasks: 3 executed, 3 up-to-date
오전 9:07:09: Task execution finished ':test --tests "com.example.bootboard.repository.BoardRepositoryTests.testSearchPage2"'.