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.
