Spring Data JPA Pagination (Pageable) with Dynamic Queries

Question

I have a simple query as follows "select * from USERS". I also use Pageable to enable pagination.

This query may have optional predicates based on the given parameters being null or not.

For example if "code" parameter is given and not null, then the query becomes "select * from USERS where code = :code";

As far as I know I cannot implement this using @Query annotation. I can implement a custom repository and use EntityManager to create a dynamic query. However, I am not sure how I can integrate "Pageable" with that to get back paginated results.

How can I achieve this?

score 16 · Answer 1 · answered Oct 31 '13 at 19:22

This is very easy to do in Spring Data using QueryDSL (as alternative to the criteria API). It is supported out of the box with the following method of QueryDSLPredicateExecutor where you can just pass null as the Predicate if no restrictions are to be applied:

Page<T> findAll(com.mysema.query.types.Predicate predicate,
                Pageable pageable)

Using QueryDSL may not be an option for you however if you look at the following series of tutorials you might get some ideas.

http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-nine-conclusions/

The scenario you have is actually discussed by the author in the comments to part 9 of his guide.

and now when using the QueryDslRepositorySupport, you can still access this functionality, since it expose the underlying querydsl instance with #getQuerydsl() — chrismarx, Jun 15 '16 at 13:05

score 4 · Answer 2 · answered Dec 13 '14 at 01:08

Getting page results for querydsl queries is somehow complicated since you need two queries: one for the total number of entries, and one for the list of entries you need in the page. You could use the following superclass:

public class QueryDslSupport<E, Q extends EntityPathBase<E>> extends QueryDslRepositorySupport {

  public QueryDslSupport(Class<E> clazz) {
    super(clazz);
  }

  protected Page<E> readPage(JPAQuery query, Q qEntity, Pageable pageable) {
    if (pageable == null) {
      return readPage(query, qEntity, new QPageRequest(0, Integer.MAX_VALUE));
    }
    long total = query.clone(super.getEntityManager()).count(); // need to clone to have a second query, otherwise all items would be in the list
    JPQLQuery pagedQuery = getQuerydsl().applyPagination(pageable, query);
    List<E> content = total > pageable.getOffset() ? pagedQuery.list(qEntity) : Collections.<E> emptyList();
    return new PageImpl<>(content, pageable, total);
  }

}

Won't this execute the query twice -- once to get results, once to get the count? That'd not be good. — Josh M., Mar 30 '20 at 18:40

score 1 · Answer 3 · edited May 23 '17 at 12:32

You have to use querydsl and build your where depending on not null parameter for example

BooleanBuilder where = new BooleanBuilder();
...
    if(code  != null){
        where.and(YOURENTITY.code.eq(code));
    }

and after execute the query

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

and use your own page

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

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

    page.totalResult = query.count();

I create MyPage like that

public class MaPage<T> {

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

it works but if in your query you got a fetch then you gonna have 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!

and it will slow down your request So the solution is to get ride of the fetch and define a @BatchSize(size=10) and use Hibernate.initialize(....) to fetch data in collections and other object type.

Display data from related entities to avoid the lazy initialization exception with setting up @BatchSize

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

score 0 · Answer 4 · edited Mar 14 '17 at 16:12

0

The information here is obsolete. Have your Repository implement the QueryDslPredicateExecutor and paging comes for free.

edited Mar 14 '17 at 16:12

Oskar Austegard

4,599
4
36
50

answered Mar 14 '17 at 15:23

Steve Edgar

41
1
1
4

Looks like your link is obsolete – jediwompa Mar 16 '21 at 20:13

Spring Data JPA Pagination (Pageable) with Dynamic Queries

4 Answers4

Linked