hibernate - Spring boot JPA Specification API with Pagination and Sorting

Question

Im trying to implement a search in spring boot. Since the search params is dynamic, i had to go for specification api. My requirement is to search for orders given certain params and sort those orders by creation date. Since data can be large, the api should also support for pagination. Below is the code snipped of the predicates.

public search(OrderSearchCriteria searchCriteria, Integer pageNo, Integer pageSize) {
      Pageable pageRequest = (!ObjectUtils.isEmpty(pageNo) && !ObjectUtils.isEmpty(pageSize))
                            ? PageRequest.of(pageNo, pageSize)
                            : Pageable.unpaged();

    Page<Order> ordersPage = this.dao.findAll(((Specification<Order>)(root, query, criteriaBuilder) -> {         
        
        // my search params, its more than what is shown here
        Long id = searchCriteria.getId();
        Priority priority = searchCriteria.getPriority();
      
        List<Predicate> predicates = new ArrayList<>();
        if(!ObjectUtils.isEmpty(id))
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("id"), id)));
         if(!ObjectUtils.isEmpty(priority))
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("priority"), priority)));
        
        // This line is causing the issue
        query.orderBy(criteriaBuilder.desc(root.get("creationDate")));
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    }), pageRequest);

The issue is, if i add this sort by line query.orderBy(criteriaBuilder.desc(root.get("creationDate"))) Pagination is not working. page 0 and page 1 both shows same result(same order) given size as 1. But page 2 shows different result as expected. But if i remove the sort by line shown above, the code is working as expected. How do i support both pagination and sorting without having these issues? i tried applying sort in pagerequest as well. But same issue PageRequest.of(pageNo, size, sort.by(DESC, "creationDate")). Appreciate help.

question from:https://stackoverflow.com/questions/65942898/spring-boot-jpa-specification-api-with-pagination-and-sorting

Answer 1

What do you mean by "it shows same result"? There might be multiple entries that have the same creation date and if you omit a sort, Spring Data will by default sort by id to provide a consistent result.