Querydsl - paging functionality

Question

It seems that in Jpa QueryDsl I can use paging like:

return new JPAQueryFactory(getEntityManager())
    .selectFrom(entity)
    .where(where_clause)
    .orderBy(order_by_clause)
    .offset(pageNumber * 20)
    .limit(20)
    .fetchResults();

Questions are:

• Is it optimal approach? Does fetchResults load only 20 elements from DB and make count query to get information about total number of entities which are in db?
• Or maybe there is some option like .page(2).limit(20)?

Yes I know that Spring-Data has already Paging and interface for QueryDsl but because of the complicated "order by" clause which is not supported by Spring-Data I cannot use it :(

Answer 1

Too late here but someone may find it helpful.

Is it optimal approach? Does fetchResults load only 20 elements from DB and make count query to get information about total number of entities which are in db?

Yes - it will issue 2 queries. One for count with the where clause and the other for fetching results. This is desired when you are interested in knowing the number of records which meets the criteria (where clause) along with fetching the data as per the page size and offset. With using .fetchResults(), you should use the following methods to get the total count and the rows returned as following.

QueryResults<Tuple> result = query.fetchResults();
int totalCount = result.getTotal();
List<Tuple> rows = result.getResults();

Or maybe there is some option like .page(2).limit(20)?

Yes - if you only want to fetch the results for offset and page size, you should use

List<Tuple> rows = query.limit(20).offset(2*20).fetch();

fetch() method will only issue 1 query to fetch the results 'limited' by the page size and offset specified.

Answer 2

Querydsl.applyPagination() can also be used.

org.springframework.data.domain.PageImpl;
org.springframework.data.domain.Pageable;

Querydsl querydsl = new Querydsl(entityManager, (new PathBuilderFactory()).create(<EntityClass>.class));
JPQLQuery<?> query = new JPAQuery<>(entityManager);

//TODO: prepare your query here 

//Get the count
Long totalElements = query.fetchCount();

//Apply the pagination
List<?> result = querydsl.applyPagination(pageable, query).fetch();

//return a paged response
return new PageImpl<>(result, pageable, totalElements);