20

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?

Community
  • 1
  • 1
NickJ
  • 9,380
  • 9
  • 51
  • 74
  • do you mind using spring query dsl . you dont need to write any code for sorting. follow spring doc or i can give some direction. i have used it and it avoids extra coding explicitly. you just pass in your parameters and asc/desc , it gives you appropriate result – surya Mar 26 '17 at 14:12
  • 1
    I encountered your question when looking for a similar problem. I'm using the repository in my Controller, and when using a Controller you can use `@PageableDefault(size = 10, sort = "sortColumn", direction = Sort.Direction.DESC) Pageable pageable` in your method signature to get an automatically filled Pageable object (just like a @PathVariable for instance). – evandongen Sep 12 '17 at 08:29

1 Answers1

31

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)
Robert Niestroj
  • 15,299
  • 14
  • 76
  • 119