Spring JPA Specification with Sort

NickJ picture NickJ · Mar 26, 2017 · Viewed 22.9k times · Source

I am using Spring JPA.

To be more precise I am using a Repository which extends JpaRepository and JpaSpecificationExecutor because I require pagination, filtering and sorting.

Now I have the pagination and filtering all working just fine, but I cannot get sorting to work as well.

I notice with some disappointment that JpaSpecificationExecutor has findAll() methods:

findAll(Specification, Pageable);
findAll(Specification, Sort);

But the one I need:

findAll(Specification, Pageable, Sort); //or something like this

does not exist!

So, Plan B, include Sorting in the Specification.

With the help of the Accepted Answer to this question: JpaSpecificationExecutor JOIN + ORDER BY in Specification I put together the following:

private Specification<MainEntity> matches() {
    return new Specification<MainEntity>() {

        public Predicate toPredicate(Root<MainEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

            List<Predicate> predicates = new ArrayList<Predicate>();

            //Attempt to sort by Surname, has no effect
            query.orderBy(cb.asc(root.get("surname")));

            //add string filters
            for (String field : stringFilterMap.keySet()) {
                String valuePattern = stringFilterMap.get(field);
                predicates.add(cb.like(root.get(field), "%"+valuePattern+"%"));
            }

            //...snip...

            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    };      
}

Where springFilterMap is an instance field, Map<String,String> whose keys are field names and values are filters values.

Above you will see my attempt to order by Surname, but this seems to have no effect.

What am I doing wrong; & how can I achieve Sorting along with Pagination and Filtering?

Answer

Robert Niestroj picture Robert Niestroj · Mar 26, 2017

Use PageRequest, which is an implementation of Pageable, and implements paging and sorting at once, like you want it. For example through this constructor:

public PageRequest(int page, int size, Sort sort)

UPDATE: Since Spring Data JPA 2.0 the above constructor is deprecated and you should use the static factory method of:

public static PageRequest of(int page, int size, Sort sort)