JPA CriteriaQuery implements Spring Data Pageable.getSort()

Question

I used JPA CriteriaQuery to build my dynamic Query and pass in a Spring Data Pageable object:

sort=name,desc

At the backend I have a method in my Repository to support dynamic query:

public Page<User> findByCriteria(String username, Pageable page) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> iRoot = cq.from(User.class);
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (StringUtils.isNotEmpty(username)) {
        predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + username.toLowerCase() + "%"));
    }

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

    cq.where(predArray);

    TypedQuery<User> query = em.createQuery(cq);

    int totalRows = query.getResultList().size();

    query.setFirstResult(page.getPageNumber() * page.getPageSize());
    query.setMaxResults(page.getPageSize());

    Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

    return result;
}

Note: I put only one param for demo purpose.

However the returned data is unsorted therefore I would like to ask is that any way to implement Pageable in CriteriaQuery.

Getting the full result set only to fetch its size seems wasteful - transfer all that data and just discard it. Instead, what method calls should be made in JPA to tell the query to fetch the count only? — chrisinmtown, Nov 26 '18 at 14:00

score 11 · Answer 1 · answered May 20 '20 at 06:55

11

You can add sorting using QueryUtils from spring: query.orderBy(QueryUtils.toOrders(pageable.getSort(), root, builder));

answered May 20 '20 at 06:55

Konrad Początek

174
2
9

what is builder ? – Thx Xth Apr 12 '21 at 13:31
@ThxXth It is CriteriaBuilder. – Javatar Apr 13 '21 at 01:38

score 6 · Answer 2 · edited Jan 17 '17 at 09:52

6

In your criteria query i see no sort information I'd write in this way:

     CriteriaBuilder cb = em.getCriteriaBuilder();
     CriteriaQuery<User> cq = cb.createQuery(User.class);
     Root<User> iRoot = cq.from(User.class);
     List<Predicate> predicates = new ArrayList<Predicate>();

     if (StringUtils.isNotEmpty(username)) {
         predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + username.toLowerCase() + "%"));
     }

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

     cq.where(predArray);

     List<Order> orders = new ArrayList<Order>(2);
     orders.add(cb.asc(iRoot.get("name")));
     orders.add(cb.asc(iRoot.get("desc")));

     cq.orderBy(orders);
     TypedQuery<User> query = em.createQuery(cq);
     Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

     return result;

I didn't test it but it should work

Angelo

edited Jan 17 '17 at 09:52

Abdullah Khan

10,786
6
60
71

answered Jan 17 '17 at 08:47

Angelo Immediata

6,195
4
31
57

But how to make it dynamic? It is hardcoded in your codes and I suppose that the Pageable will do sorting automatically? – Javatar Jan 17 '17 at 08:55
Pageable will not do the sorting automatically; it must be the query in doing it. If you want it dinamically you should pass the Order (or an equivalent DTO) to the method findByCriteria params – Angelo Immediata Jan 17 '17 at 09:05
Thanks, I have found a solution by manually do the sort by using Pageable,getSort(). – Javatar Jan 23 '17 at 01:45
@Javatar I'm curious on how you succeded to use the Pageable.getSort() to do something dynamic. I tried `cq.where( cb.and(predicates) ).orderBy( pageable.getSort().stream().collect(Collectors.toList()) );` where cq is my CriteriaQuery. It won't work as Orders list from Pageable is a springframework object, and CriteriaQuery API is waiting an Hibernate object :( (EDIT : ok I read again your comment and you said 'manually' nvm) – Alex Nov 22 '18 at 11:03
1

That two-value string "name,desc" in Spring-speak means "sort by name in descending order." The code above attempts to sort on a column named "desc" which I expect will break. – chrisinmtown Nov 26 '18 at 17:02
@chrisinmtown The value "name,desc" is actually asking Spring to sort the resultset by using the name AND the desc. – Javatar Nov 28 '18 at 03:00
@javatar thanks for the reply but please check your facts, for example https://stackoverflow.com/questions/33018127/spring-data-rest-sort-by-multiple-properties – chrisinmtown Nov 28 '18 at 13:13

score 2 · Answer 3 · answered Nov 16 '21 at 21:10

Both Op question and Angelo's answer won't work in a real-life scenario. If that table/query returns thousand of records this approach will perform slowly and you could also could create memory issues.

Why?

int totalRows = query.getResultList().size();
new PageImpl(query.getResultList(), page, totalRows);

both resultsets will run same query twice and mantain a full table/query data in those colections, just to be counted or sliced, which makes no sense when you need to show few records in your front-end.

My suggestion for Spring Data is to extend JpaSpecificationExecutor interface in your repository

@Repository
public interface UserRepository extends JpaRepository<User, Integer>,
        JpaSpecificationExecutor {
}

This interface will enable your repository to use findAll(Specification, Pageable) method.

After that, all is quite easy. You only need to create a Java closure to inject predicates into a dynamic jpa query.

public Page<User> listUser( String name, int pageNumber, int pageSize ) {

Sort sort = Sort.by("id").ascending();
Pageable pageable = PageRequest.of(pageNumber, pageSize, sort);


return this.reservationRepository.findAll((root, query, builder) -> {
            List<Predicate> predicates = new ArrayList<>();
            if name!= null ) {
                predicates.add(builder.equal(root.get("name"), name));
            }
            // More simple/complex conditions can be added to 
            // predicates collection if needed.

            return builder.and(predicates.toArray(new Predicate[]{}));

        }, pageable);

}

As you can see here, this solution supports Sorting/Filtering and database side pagination.

score 0 · Answer 4 · answered Jul 16 '20 at 05:23

 CriteriaBuilder cb = em.getCriteriaBuilder();
 CriteriaQuery<User> cq = cb.createQuery(User.class);
 Root<User> iRoot = cq.from(User.class);
 List<Predicate> predicates = new ArrayList<Predicate>();

 if (StringUtils.isNotEmpty(username)) {
     predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + 
     username.toLowerCase() + "%"));
 }


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

 cq.where(predArray);

 cq.orderBy(cb.desc(user.get("name")));

 TypedQuery<User> query = em.createQuery(cq);
 Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

 return result;

JPA CriteriaQuery implements Spring Data Pageable.getSort()

4 Answers4

Linked