In previous articles, I have talked about creating and using derived, custom, and named queries in Spring Data JPA to interact with the database. I also explained how to apply simple ordering to query results while retrieving data.

In this article, you will learn how to sort the query results with Spring Data JPA. There are two ways to achieve this in Spring Data JPA:

  1. Static Sorting — Add an ORDER BY clause to your JPQL or native SQL query
  2. Dynamic Sorting — Add the special parameter Sort to your repository method

Both of these approaches work fine. The only difference is static sorting is fixed and defined beforehand with attribute names and sorting directions. On the other hand, dynamic sorting enables you to dynamically choose any attribute or even a combination of multiple attributes for sorting the query results.

Create an Application

To demonstrate Spring Data JPA sorting capabilities, let us first create a new Spring Boot application that uses the H2 in-memory database for storing and retrieving data. If you want to use the MySQL database as a data store, follow this tutorial.

For the sake of simplicity, our example application has just one entity — Employee:

Employee.java

package com.attacomsian.jpa.domains;

import javax.persistence.*;

@Entity
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String firstName;
    private String lastName;
    private int age;
    private double salary;

    public Employee() {
    }

    public Employee(String firstName, String lastName, int age, double salary) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.salary = salary;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}

The next step is to add the EmployeeRepository interface to retrieve Employee entities:

EmployeeRepository.java

package com.attacomsian.jpa.repositories;

import com.attacomsian.jpa.domains.Employee;
import org.springframework.data.repository.PagingAndSortingRepository;

public interface EmployeeRepository extends PagingAndSortingRepository<Employee, Long> {

    // TODO: add query methods
}

In the above code, we are extending the PagingAndSortingRepository interface. This is an extension of CrudRepository and provides additional methods for pagination and sorting, such as findAll(Sort sort).

Sort Query Results using Static Sorting

Static sorting refers to the mechanism where the retrieved data is always sorted by specified columns and directions. The columns and sort directions are defined at the development time and cannot be changed at runtime.

Derived Queries

For derived queries, Spring Data JPA allows us to apply static sorting by adding the OrderBy keyword to the method name along with the property name and sort direction (Asc or Desc).

Let us use the method naming conventions to create a derived query that returns all Employee entities in the ascending order of their salary:

List<Employee> findByOrderBySalaryAsc();

The Asc keyword is optional as OrderBy sorts the results in ascending order. The above query can be rewritten as follows:

List<Employee> findByOrderBySalary();

To reverse the sort direction, just add the Desc keyword after the property name:

List<Employee> findByOrderBySalaryDesc();

With the sort query, you can use all other Spring Data JPA keywords. Let us create another query method that returns employees by their last name and sorts them by their age in descending order:

List<Employee> findByLastNameOrderByAgeDesc(String lastName);

To sort query results by multiple attributes, all you need to do is just reference multiple properties after the OrderBy clause and specify their sort directions. For example, the following derived query sorts the employees by their last name in the ascending order and by their salary in the descending order:

List<Employee> findByOrderByLastNameAscSalaryDesc();

Custom Queries with @Query Annotation

If you use the @Query annotation to define custom JPQL or native SQL queries, adding support for static ordering is even simpler and straightforward. All you need to do is add the ORDER BY clause to the query definition.

The following JPQL query returns all employees with a salary greater than the specified amount and sorted by their age in descending order:

@Query("SELECT e FROM Employee e WHERE e.salary > ?1 ORDER BY e.age DESC")
List<Employee> findBySalaryGreaterThanJPQL(double salary);

Similarly, you can apply static sorting to a native SQL query. The following query returns employees by their first name sorted by salary in ascending order:

@Query(value = "SELECT * FROM Employee e WHERE e.fistName = :firstName ORDER BY e.salary ASC",
            nativeQuery = true)
List<Employee> findByFirstNameNativeSQL(@Param("firstName") String firstName);

Named Queries

Applying static sorting to named queries is similar to the above custom queries. All you need to do is add the ORDER BY clause to any JPQL or native SQL named query.

The following JPQL named query returns employees who are older than the given age after sorting them by their first name in the ascending order:

@NamedQuery(name = "Employee.findByAgeGreaterThanNamedJPQL",
        query = "SELECT e FROM Employee e WHERE e.age > :age ORDER BY e.firstName ASC")
public class Employee {
    // ...
}

Here is another example of native SQL named query that returns all employees after sorting them by their age in descending order:

@NamedNativeQuery(name = "Employee.findAllNamedNativeSQL",
        query = "SELECT * FROM Employee e ORDER BY e.age DESC")
public class Employee {
    // ...
}

Sorting Query Results using Dynamic Sorting

Unlike static sorting, dynamic sorting provides more flexibility in choosing sort columns and directions. Spring Data JPA allows you to add a Sort parameter to your query method. The Sort class is only a specification that provides sorting options for database queries.

With dynamic sorting, you can choose the sorting column and direction at runtime to sort the query results. The EmployeeRepository interface already inherits the findAll(Sort sort) method from PagingAndSortingRepository that returns all Employee entities sorted by the given options.

Derived Queries

You can easily define additional derived and custom query methods that accept the Sort parameter. The following derived query returns employees by their last name and also takes a Sort parameter:

List<Employee> findByLastName(String lastName, Sort sort);

Let us define another derived query that returns employees by their first name and the salary less than the given value:

List<Employee> findByFirstNameAndSalaryLessThan(String lastName, double salary, Sort sort);

Custom Queries with @Query Annotation

Similar to derived queries, custom queries also accept the Sort parameter to apply sorting to query results. The following JPQL query accepts the Sort parameter for sorting employees whose salaries fall in a certain range:

@Query("SELECT e FROM Employee e WHERE e.salary > ?1 AND e.salary < ?2")
List<Employee> findBySalaryRange(double start, double end, Sort sort);

Currently, Spring Data JPA does not support dynamic sorting for native SQL queries, as it would require the manipulation of the actual SQL query defined, which cannot be done reliably by the Spring Data JPA.

Using the Sort Parameter

Just defining queries with the Sort parameter is not enough. You have to create a Sort object to specify the entity attributes you want to use for sorting and their directions, and then pass this object as a parameter to call the above query methods.

The following example calls findAll() to sort all Employee entities found in the database by their age in descending order:

Iterable<Employee> emps =  employeeRepository.findAll(Sort.by("age").descending());

The real benefit of dynamic sorting is that you do not need to modify the query method if you decide to change the sort options. All you need to do is just update the Sort object.

Let us extend the above example to include one more sorting attribute:

Iterable<Employee> emps =  employeeRepository.findAll(Sort.by("age", "salary").descending());

Sometimes, we need a different sorting order for each sort attribute. You can easily do this using the Sort class. The following example sorts the query results in descending order by using the salary property and in ascending order by using the firstName property:

Sort sort = Sort.by("salary").descending().and(Sort.by("firstName"));
List<Employee> employees = employeeRepository.findByLastName("Doe", sort);

There is no explicit limit for the number of sorting attributes you can use in a single query. You can use as many as you want:

Sort sort = Sort.by("salary").descending().and(Sort.by("firstName"))
        .and(Sort.by("age").descending()).and(Sort.by("lastName").ascending());
List<Employee> employees = employeeRepository.findBySalaryRange(100, 10000, sort);

If you want to skip the sorting for a query that has a Sort parameter, just use the Sort.unsorted() method:

Iterable<Employee> emps = employeeRepository.findAll(Sort.unsorted());

Source code: Download the complete source code from GitHub available under MIT license.

Conclusion

In this article you learned how to apply sorting to query results using Spring Data JPA. There are two ways to sort the query results in Spring Data JPA: static and dynamic sorting.

For static sorting, we add the ORDER BY clause directly to the query definition at the development time. Therefore the query result is always sorted by defined columns and sorting directions.

Dynamic sorting is more flexible than static sorting. We can choose the sort columns and order them at runtime.

Read Next: Pagination with Spring Data JPA

Further Reading

To learn about Spring Data JPA, check out the following articles:

✌️ Like this article? Follow me on Twitter and LinkedIn. You can also subscribe to RSS Feed.