Spring JPA order by query

1. Overview

This article is about Spring JPA order by the query, we will explain here soring data with ascending and descending order using spring JPA.  While working with relational database Order By is clause using that we can perform sorting the records, the order can be either ascending or descending based on our application requirement we can apply the order, another important is that we can apply to order in a single column or multiple columns in multiple columns both columns might be in the same order or each column has different order, in the below examples we have tried to explain the possibilities of sorting data:

I like to explain that,  If we do not specify any order then database by default will not apply for any order. It might be possible that if we execute the same query without order it may return the result in different seqence both the times, Database may return records in any sequence it will not give any guaranty so we working with paging that time we must specify the order, When we are working with paging and do not specify the order then it might be possible that when we move to another page we may get the same records which already been seen in previous pages.

2. Sorting/Order by query examples

2.1 Static sorting

Here are examples of static order that we can write in a query or using method names, Order with method name as per spring boot JPA standards :

Order with the query as like normal SQL standers.

@Repository
@Transactional
public interface EmployeeRepository extends PagingAndSortingRepository<Employee,Long>, JpaSpecificationExecutor<Employee> {
    List<Employee> findAllByOrderByEmployeeNameAsc();    // list of all employee ascending order by name
    List<Employee> findAllByOrderByEmployeeNameDesc();    // list of all employee descending order by name
    @Query(value = "SELECT e from Employee e order by e.employeeName asc")
    List<Employee> findEmployeeByAsc();    // list of all employee ascending order by name
    @Query(value = "SELECT e from Employee e order by e.employeeName desc")
    List<Employee> findEmployeeByDesc();    // list of all employee descending order by name
}

2.2 Dynamic sorting based on parameters

Here we have passed that sort as parameters, no need to write order clause in the query. Just pass Sort in as argument other things will be handled by spring JPA. Here we have tried to explain all the possible combinations related to sorting like: single column, multiple columns sorting with ascending and descending orders. We have also given an example related to order by length or we can any aggregate function as per our needs.

@Repository
@Transactional
public interface EmployeeRepository extends PagingAndSortingRepository<Employee,Long>, JpaSpecificationExecutor<Employee> {
    @Query(value = "SELECT e from Employee e where e.employeeName like ?1%")
    List<Employee> findByNameAndSort(String name, Sort sort);
}

Usage:

List<Employee> employeesAsc = employeeRepository.findEmployeeByAsc();
employeeRepository.findByNameAndSort("Harry",new Sort("employeeName"));      // single field asc order
employeeRepository.findByNameAndSort("Harry",new Sort(Sort.Direction.DESC,"employeeName"));      // single field desc order
employeeRepository.findByNameAndSort("Harry",new Sort(Sort.Direction.DESC,"employeeName","joinedDate"));      // multiple fields desc order
employeeRepository.findByNameAndSort("Harry",JpaSort.unsafe("LENGTH(employeeName)"));      // order by length

List<Sort.Order> sorts=new ArrayList<>();
sorts.add(new Sort.Order(Sort.Direction.ASC,"employeeName"));
sorts.add(new Sort.Order(Sort.Direction.DESC,"joinedDate"));
List<Employee> employeesMultiSorts = employeeRepository.findByNameAndSort("Harry",new Sort(sorts));      // order by multiple field with multiple order

2.3 Order By with JpaSpecificationExecutor

Here is an example related to how to pass an order with the dynamic query. Using JpaSpecificationExecutor we can generate a dynamic query, Here we have explained dynamic query with more details.

public List<Employee> findByCriteria(String employeeName,String fieldName){
        return employeeRepository.findAll(new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if(employeeName!=null) {
                    predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName)));
                }
                query.orderBy(criteriaBuilder.asc(root.get(fieldName)));           // specify order here
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        });
    }

findByCriteria("Harry","joinedDate");

SQL Query: select * from employee where employeeName=? order by joinedDate desc

3. Conclusion

In the above examples, we learn different-different examples spring JPA Sorting/Order static as well as dynamic options as well. Ordering may also reduce the query performance we should only apply for the order when we need to show the records in particular order.

4. References

• Spring JPA documentation