How to execute a JPAQuery with pagination using Spring Data and QueryDSL

Question

I have this request working good with queryDSL :

 Iterable<AO> query_result = new JPAQuery(entityManager).from(ao)
            .leftJoin( ao.lots , lot )
            .leftJoin( ao.acs , ac )
              .where(where).distinct()
                .list(ao);

But what is its equivalent if we use it with spring data jpa

ao_respository.findAll(Predicate arg0, Pageable arg1);

Because i want to return a Page and just with querydsl it doesn't implement Page without spring data jpa.

I try to put my where in Predicate arg0 but i got this exception

Undeclared path 'lot '. Add this path as a source to the query to be able to reference it

where lot is declared as QLot lot = QLot.lot;

I'm also looking for an answer. How about rephrasing your question to something like "How to execute a JPAQuery with pagination using Spring Data and QueryDSL".... I know you have tags, but the title isn't helping for getting answers. — pakman
– pakman, Commented Nov 21, 2014 at 1:43

anat0lius · Accepted Answer · 2018-10-31 10:41:21Z

7

Returning a Page:

JPAQuery query = 
    ...
    .orderBy(getOrderSpecifiers(pageable, MyEntity.class))
    .limit(pageable.getPageSize())
    .offset(pageable.getOffset());

long total = query.fetchCount();
List<MyEntity> content = query.fetch();
return new PageImpl<>(content, pageable, total);

And I created this function to get OrderSpecifier:

private OrderSpecifier[] getOrderSpecifiers(@NotNull Pageable pageable, @NotNull Class klass) {

    // orderVariable must match the variable of FROM
    String className = klass.getSimpleName();
    final String orderVariable = String.valueOf(Character.toLowerCase(className.charAt(0))).concat(className.substring(1));

    return pageable.getSort().stream()
            .map(order -> new OrderSpecifier(
                    Order.valueOf(order.getDirection().toString()),
                    new PathBuilder(klass, orderVariable).get(order.getProperty()))
            )
            .toArray(OrderSpecifier[]::new);
}

edited Oct 31, 2018 at 10:41

answered Oct 30, 2018 at 13:21

anat0lius

2,2857 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

9900kf Over a year ago

fetchCount is deprecated now

Massimiliano Kraus · Accepted Answer · 2017-08-30 08:08:20Z

5

I created my own Page class and executed the query like this:

    JPAQuery query = new JPAQuery(entityManager).from(ao)               
            .leftJoin( .. ).fetch()
            .leftJoin( .. ).fetch()
            ...
            .where(where)




    MaPage<AO> page = new MaPage<AO>();
    page.number = pageNumber+1;

    page.content = query.offset(pageNumber*pageSize).limit(pageSize).list(ao);

    page.totalResult = query.count();

My Page class:

public class MaPage<T> {

    public List<T> content;
    public int number;
    public Long totalResult;
    public Long totalPages;
    ...
}

It works but I got this warning

nov. 21, 2014 6:48:54 AM org.hibernate.hql.internal.ast.QueryTranslatorImpl list WARN: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

edited Aug 30, 2017 at 8:08

Massimiliano Kraus

3,8925 gold badges32 silver badges51 bronze badges

answered Nov 21, 2014 at 6:54

Hayi

6,37630 gold badges87 silver badges154 bronze badges

3 Comments

Timo Westkämper Over a year ago

When you join fetch a collection property pagination can't be applied on the SQL level.

Hayi Over a year ago

Yes but there is not a altenative to avoid this warning ? Because i need yo use fetch to display data on my page.

Timo Westkämper Over a year ago

You can avoid this warning if you get rid of left join fetch and use for example batch fetching for the collection members.

Stefan Hüttemann · Accepted Answer · 2021-02-05 12:09:33Z

1

If you have a working, complex query in querydsl and you want to use springdata pagination, you have to:

make your querydsl/repository method return Page<T>

 Page<YourEntity> yourSelect(Pageable aPageable)

use querydsl offset and limit to page your result set

List<YourEntity>  theResultList = jpaQueryFactory
                .select(<whatever complext jpaquery you like>)
                .offset(aPageable.getOffset())
                .limit(aPageable.getPageSize())
                .fetch();

provide a LongSuplier counting all available results with respect to your query and use PageableExecutionUtils to return the result as Page

final long theCount = jpaQueryFactory
      .selectFrom(<your select to count all results>)
      .fetchCount();

return PageableExecutionUtils.getPage(theResultList, aPageable, () -> theCount);

edited Feb 5, 2021 at 12:09

answered Feb 5, 2021 at 12:02

Stefan Hüttemann

2223 silver badges7 bronze badges

1 Comment

Marcello DeSales Over a year ago

it would be nice to have a reference with examples

Collectives™ on Stack Overflow

How to execute a JPAQuery with pagination using Spring Data and QueryDSL

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related