

Table of Contents
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.