How to handle pagination in JPA (Criteria and Predicates)

Question

I'm fetching the results from DB using criteria and predicates and I got my result list , and I'm trying to apply pagination and sorting but it's not working. Please help me where I'm missing, Here is my code:

private Page<Books> getFiltereBooks(Params params,
            PageRequest sortOrder) {
        CriteriaBuilder builder =  em.getCriteriaBuilder();
        CriteriaQuery<Books> criteria = builder.createQuery(Books.class);
        Root<Books> booksRoot = criteria.from(Books.class);
        List<Predicate> predicates = new ArrayList<Predicate>();


            predicates.add(builder.equal(booksRoot.get("id"), params.getRequestId()));


            predicates.add(builder.like(builder.lower(booksRoot.get("name")), 
                    "%" + params.getName().toLowerCase() + "%"));


        criteria.where(builder.and(predicates.toArray( new Predicate[predicates.size()])));

            criteria.orderBy(builder.desc(booksRoot.get("id")));

        List<Books> result = em.createQuery(criteria).getResultList();
        int total = result.size();
        Page<Books> result1 = new PageImpl<>(result, sortOrder, total);
        return result1;
    }

when I use this code :

Page<Books> result1 = new PageImpl<>(result, sortOrder, total);

it's not working, I want to return a Page Object. any help is appreciated.

score 29 · Answer 1 · edited Sep 27 '18 at 23:40

29

You can try this

private Page<Books> getFiltereBooks(Params params,
            Pageable pageable) {

        CriteriaBuilder builder =  em.getCriteriaBuilder();
        CriteriaQuery<Books> criteria = builder.createQuery(Books.class);
        Root<Books> booksRoot = criteria.from(Books.class);
        List<Predicate> predicates = new ArrayList<Predicate>();

        predicates.add(builder.equal(booksRoot.get("id"), params.getRequestId()));

        predicates.add(builder.like(builder.lower(booksRoot.get("name")), 
                    "%" + params.getName().toLowerCase() + "%"));

        criteria.where(builder.and(predicates.toArray( new Predicate[predicates.size()])));

        criteria.orderBy(builder.desc(booksRoot.get("id")));

        // This query fetches the Books as per the Page Limit
        List<Books> result = em.createQuery(criteria).setFirstResult((int) pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList();

        // Create Count Query
        CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
        Root<Books> booksRootCount = countQuery.from(Books.class);
        countQuery.select(builder.count(booksRootCount)).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

        // Fetches the count of all Books as per given criteria
        Long count = em.createQuery(countQuery).getSingleResult();

        Page<Books> result1 = new PageImpl<>(result, pageable, count);
        return result1;
    }

edited Sep 27 '18 at 23:40

Developer

2,389
4
11
17

answered Sep 27 '18 at 15:42

Phenomenal One

2,501
4
19
29

1

Thanks this worked and for getting the count I did result.count() instead of querying one more time. – Developer Sep 27 '18 at 17:39
@Kumar You will have a performance impact between default MYSQL count Query and fetching entire result and then calculating its size. Anyways, you can accept the answer if you found it useful. – Phenomenal One Sep 28 '18 at 00:32
I'm getting "Query argument pattern not found in the list of parameters provided during query execution." when I tried with count query.. – Developer Oct 04 '18 at 19:41
Can you provide your SQL Query? Try debugging it line by line, I think you can figure it out yourself – Phenomenal One Oct 05 '18 at 04:28
I don't have any SQL query , I'm using criteria and predicates to build the query, which is listed above. – Developer Oct 05 '18 at 07:07
I have tested this and it works for me. To debug, add `spring.jpa.show-sql = true` in application.properties. Now, execute this query. You will now be able to the SQL Query in the Application Logger. Can you print the query here? – Phenomenal One Oct 05 '18 at 07:20
Good solution, but:: assume that in the data query you have more roots, joins or fetches; in the count query you can't consider only Book root because some predicates of data query could involve others entities. How should be the count query? I have to re-define the joins also for the count query?...double work....any idea to solve? – Andrea Bevilacqua Aug 03 '19 at 08:28
2

@AndreaBevilacqua Yes. You have to redefine the joins for the count query. – Phenomenal One Aug 03 '19 at 13:25

score 0 · Answer 2 · answered Apr 02 '21 at 23:24

Predicate[] getPredicate(Root<Entity> root, CriteriaBuilder criteriaBuilder, filter params) {
    Predicate[] predicatesArr = null;
    List<Predicate> predicates = new ArrayList<>();
    
    // fill your predicates list here

    predicatesArr = predicates.toArray(new Predicate[predicates.size()]);

    return predicatesArr;
}

You need to add a method for creating predicates for each query, Note here you are using the same predicates list "which drived from different query" for both queries [pagination and count]

    Root<Books> booksRoot = criteria.from(Books.class);
    predicates.add(builder.equal(booksRoot.get("id"), params.getRequestId()));

How to handle pagination in JPA (Criteria and Predicates)

2 Answers2