# Sorting Query Results with Spring Data JPA By Atta Ur Rehman Shah (https://attacomsian.com/about). Published Oct 9, 2019, updated Sep 25, 2022. Topics: Spring Boot, Spring Data JPA. Canonical URL: https://attacomsian.com/blog/spring-data-jpa-sorting > A complete guide to understanding how sorting works in Spring Data JPA and how to use it for derived, named, and custom queries. In previous articles, I have talked about creating and using [derived](https://attacomsian.com/blog/derived-query-methods-spring-data-jpa), [custom](https://attacomsian.com/blog/spring-data-jpa-query-annotation), and [named](https://attacomsian.com/blog/spring-data-jpa-named-queries) 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](https://attacomsian.com/blog/getting-started-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](https://attacomsian.com/blog/spring-data-jpa-h2-database) 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](https://attacomsian.com/blog/accessing-data-spring-data-jpa-mysql). For the sake of simplicity, our example application has just one entity — `Employee`:

Employee.java

```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

```java package com.attacomsian.jpa.repositories; import com.attacomsian.jpa.domains.Employee; import org.springframework.data.repository.PagingAndSortingRepository; public interface EmployeeRepository extends PagingAndSortingRepository { // 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](https://attacomsian.com/blog/spring-data-jpa-sorting#sort-query-results-using-static-sorting), 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: ```java List findByOrderBySalaryAsc(); ``` The `Asc` keyword is **optional** as `OrderBy` sorts the results in ascending order. The above query can be rewritten as follows: ```java List findByOrderBySalary(); ``` To **reverse** the sort direction, just add the `Desc` keyword after the property name: ```java List 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: ```java List 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: ```java List findByOrderByLastNameAscSalaryDesc(); ``` ### Custom Queries with `@Query` Annotation If you use the [@Query annotation](https://attacomsian.com/blog/spring-data-jpa-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: ```java @Query("SELECT e FROM Employee e WHERE e.salary > ?1 ORDER BY e.age DESC") List 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: ```java @Query(value = "SELECT * FROM Employee e WHERE e.fistName = :firstName ORDER BY e.salary ASC", nativeQuery = true) List findByFirstNameNativeSQL(@Param("firstName") String firstName); ``` ### Named Queries Applying static sorting to [named queries](https://attacomsian.com/blog/spring-data-jpa-sorting) 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: ```java @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: ```java @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](https://attacomsian.com/blog/derived-query-methods-spring-data-jpa) returns employees by their last name and also takes a `Sort` parameter: ```java List 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: ```java List findByFirstNameAndSalaryLessThan(String lastName, double salary, Sort sort); ``` ### Custom Queries with `@Query` Annotation Similar to derived queries, [custom queries](https://attacomsian.com/blog/spring-data-jpa-query-annotation) 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: ```java @Query("SELECT e FROM Employee e WHERE e.salary > ?1 AND e.salary < ?2") List 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: ```java Iterable 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: ```java Iterable 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: ```java Sort sort = Sort.by("salary").descending().and(Sort.by("firstName")); List 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: ```java Sort sort = Sort.by("salary").descending().and(Sort.by("firstName")) .and(Sort.by("age").descending()).and(Sort.by("lastName").ascending()); List 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: ```java Iterable emps = employeeRepository.findAll(Sort.unsorted()); ``` > **Source code:** Download the complete source code from [GitHub](https://github.com/attacomsian/code-examples/tree/master/spring-data-jpa/jpa-sorting) 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](https://attacomsian.com/blog/spring-data-jpa-pagination) ## Further Reading To learn about Spring Data JPA, check out the following articles: * [Getting Started with Spring Data JPA](https://attacomsian.com/blog/getting-started-spring-data-jpa) * [Spring Data JPA with H2 DataBase and Spring Boot](https://attacomsian.com/blog/spring-data-jpa-h2-database) * [Accessing Data with Spring Data JPA and MySQL](https://attacomsian.com/blog/accessing-data-spring-data-jpa-mysql) * [Derived Query Methods in Spring Data JPA](https://attacomsian.com/blog/derived-query-methods-spring-data-jpa) * [Spring Data JPA Custom Queries using @Query Annotation](https://attacomsian.com/blog/spring-data-jpa-query-annotation) * [How to Use Spring Data JPA Named Queries](https://attacomsian.com/blog/spring-data-jpa-named-queries) * [Pagination with Spring Data JPA](https://attacomsian.com/blog/spring-data-jpa-pagination)