Spring Data JPA Tutorial: Sorting

My Spring Data JPA tutorial has taught us how we can create both static and dynamic database queries with Spring Data JPA.

We have also implemented a search function that ignores case and returns todo entries whose title or description contains the given search term.

However, we haven’t paid any attention to one very important thing:

We have no idea how we can sort the query results of our database queries.

This blog post fixes this problem. We will learn to sort the query results of our database queries and add sorting support into the search function of our example application.

Let’s get started.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

Spring Data JPA Tutorial: Introduction provides a quick introduction to Spring Data JPA and gives an overview of the Spring Data repository interfaces.
Spring Data JPA Tutorial: Getting the Required Dependencies describes how you can get the required dependencies.
Spring Data JPA Tutorial: Configuration describes how you can configure the persistence layer of a Spring application that uses Spring Data JPA.
Spring Data JPA Tutorial: Introduction to Query Methods describes how you can pass method parameters to your query methods and identifies the “legal” return values of Spring Data JPA query methods.
Spring Data JPA Tutorial: Creating Database Queries From Method Names describes how you can create database queries from the method names of your query methods.
Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation describes how you can create database queries by annotating your query methods with the @Query annotation.
Spring Data JPA Tutorial: Creating Database Queries With Named Queries describes how you can create database queries by using named queries.
Spring Data JPA Tutorial: Creating Database Queries With the JPA Criteria API describes how you can create dynamic queries by using the JPA Criteria API.
Spring Data JPA Tutorial: Creating Database Queries With Querydsl describes how you can create dynamic database queries by using Querydsl.

Sorting Query Results With the Method Names of Our Query Methods

If we create our database queries by using the query generation from the method name strategy, we can sort the query results of our database queries by using the OrderBy keyword. We can use the OrderBy keyword by following these steps:

Append the OrderBy keyword to the method name of our query method.
Append the name of the property to the method name of our query method and transform its first letter into uppercase. If we want to order our query results by using the title of a todo entry, we have to append the string: Title to the method name of our query method.
Describe the sort direction. If we want to sort the query results in ascending order, we have to append the keyword Asc to the method name of our query method. On the other hand, if we want to sort the query results in descending order, we have to append the keyword Desc to the method name of our query method.
If we need to sort our query results by using multiple properties, we have to go back to the step 2.

The following examples demonstrate how we can use the OrderBy keyword:

Example 1: We have created a query method that returns todo entries whose title is given as a method parameter. If we want to sort the query results of that query method in ascending order by using the value of the title field, we have to use the following code:

1import org.springframework.data.repository.Repository;
2
3import java.util.List;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7    List<Todo> findByTitleOrderByTitleAsc(String title);
8}

Example 2: We have created a query method that returns todo entries whose title is given as a method parameter. If we want to sort the query results of that query method in ascending order by using the value of the title field and in descending order by using the value of the description field, we have to use the following code:

1import org.springframework.data.repository.Repository;
2
3import java.util.List;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7    List<Todo> findByTitleOrderByTitleAscDescriptionDesc(String title);
8}

Example 3: The search function of our example application returns todo entries whose title or description contains the given search term. If we want to sort the search results in ascending order by using the value of the title field, we have to add the following query method into our repository interface:

1import org.springframework.data.repository.Repository;
2
3import java.util.List;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7    List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCaseOrderByTitleAsc(String descriptionPart,
8                                                                                    String titlePart);
9}

Additional Reading:

Let’s move on and find out how we can sort query results with query strings.

Sorting Query Results With Query Strings

If we create our database queries by using named queries or the @Query annotation, we can specify the sorting logic in our query strings.

If our database queries are named queries or native queries that use the @Query annotation, we must specify the sorting logic in our query strings.

The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. The following examples demonstrate how we can sort our query results by modifying the existing JPQL and SQL queries:

Example 1: If we want to modify an existing JPQL query to sort the query results in ascending order by using the value of the title field, we have to use the JPQL ORDER BY clause.

The modified JPQL query looks as follows:

1SELECT t FROM Todo t WHERE
2	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR
3	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))
4	ORDER BY t.title ASC

Example 2: If we want to modify an existing SQL query to sort the query results in ascending order by using the value of the title field, we have to use the SQL ORDER BY clause.

1SELECT * FROM todos t WHERE
2	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR
3	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))
4	ORDER BY t.title ASC

Additional Reading:

Let’s find out how we can sort query results by using the Sort class.

Sorting Query Results With the Sort Class

If our database queries are not named queries or native queries that use the @Query annotation, we can sort their query results by using the Sort class. It is essentially a specification class that describes the sorting options of our database queries.

We can sort our query results by following these steps:

Obtain the Sort object that describes the sorting options of the invoked database query.
Pass the Sort object forward to the correct repository method as a method parameter.

Let’s find out how we can obtain the Sort object.

Obtaining the Sort Object

We can obtain the Sort object by using two different methods: we can specify the sort options manually or we can use Spring Data Web Support.

Let’s start by specifying the sort options manually.

Specifying the Sort Options Manually

If we want to specify the sort options manually, the service class (or another component) that wants to sort the query results returned by a Spring Data JPA repository, must create the Sort object and pass it forward to the invoked repository method.

The source code of the RepositoryTodoSearchService class, which uses this method, looks as follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.data.domain.Sort;
 3import org.springframework.stereotype.Service;
 4import org.springframework.transaction.annotation.Transactional;
 5
 6import java.util.List;
 7
 8@Service
 9final class RepositoryTodoSearchService implements TodoSearchService {
10
11    private final TodoRepository repository;
12
13    @Autowired
14    public RepositoryTodoSearchService(TodoRepository repository) {
15        this.repository = repository;
16    }
17
18    @Transactional(readOnly = true)
19    @Override
20    public List<TodoDTO> findBySearchTerm(String searchTerm) {
21		Sort sortSpec = orderBy();
22
23		//Obtain search results by invoking the preferred repository method.
24        List<Todo> searchResults = ...
25
26		return TodoMapper.mapEntitiesIntoDTOs(searchResults);
27    }
28
29	private Sort orderBy() {
30		//Create a new Sort object here.
31	}
32}

The following examples demonstrates how we can implement the private orderBy() method:

Example 1:

If we must sort the query results in ascending order by using the value of the title field, we have to create the Sort object by using the following code:

1private Sort orderBy() {
2	return new Sort(Sort.Direction.ASC, "title");
3}

Example 2:

If we must sort the query results by in descending order by using the values of the title and description fields, we have to create the Sort object by using the following code:

1private Sort orderBy() {
2	return new Sort(Sort.Direction.DESC, "title", "description");
3}

Example 3:

If we want to sort the query results in descending order by using the value of the description field and in ascending order by using the value of the title field, we have to create the Sort object by using the following code:

1private Sort orderBy() {
2	return new Sort(Sort.Direction.DESC, "description")
3				.and(new Sort(Sort.Direction.ASC, "title"));
4}

Additional Reading:

The Javadoc of the Sort class

Let’s find out how we can obtain Sort objects by using Spring Data web support.

Using Spring Data Web Support

We can enable Spring Data web support by annotating our application context configuration class with the @EnableSpringDataWebSupport annotation. The relevant part of the PersistenceContext class, which configures the persistence layer of our example application, looks as follows:

 1import org.springframework.context.annotation.Configuration;
 2import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
 3import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
 4import org.springframework.data.web.config.EnableSpringDataWebSupport;
 5import org.springframework.transaction.annotation.EnableTransactionManagement;
 6
 7@Configuration
 8@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
 9@EnableJpaRepositories(basePackages = {
10        "net.petrikainulainen.springdata.jpa.todo"
11})
12@EnableTransactionManagement
13@EnableSpringDataWebSupport
14class PersistenceContext {
15}

This registers a new SortHandlerMethodArgumentResolver instance that can create Sort objects from request parameters or @SortDefault annotations. This means that we can specify the sorting logic by setting the value of the sort request parameter. The reference documentation of Spring Data JPA describes the content of the sort request parameter as follows:

Properties that should be sorted by in the format property,property(,ASC|DESC). Default sort direction is ascending. Use multiple sort parameters if you want to switch directions, e.g. ?sort=firstname&sort=lastname,asc.

After we have enabled Spring Data web support, we can inject Sort objects into controller handler methods. The source code of the TodoSearchController class, which utilizes Spring Data web support, looks as follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.data.domain.Sort;
 3import org.springframework.web.bind.annotation.RequestMapping;
 4import org.springframework.web.bind.annotation.RequestMethod;
 5import org.springframework.web.bind.annotation.RequestParam;
 6import org.springframework.web.bind.annotation.RestController;
 7
 8import java.util.List;
 9
10@RestController
11final class TodoSearchController {
12
13    private final TodoSearchService searchService;
14
15    @Autowired
16    public TodoSearchController(TodoSearchService searchService) {
17        this.searchService = searchService;
18    }
19
20    @RequestMapping(value = "/api/todo/search", method = RequestMethod.GET)
21    public List<TodoDTO> findBySearchTerm(@RequestParam("searchTerm") String searchTerm,
22										  Sort sort) {
23        return searchService.findBySearchTerm(searchTerm, sort);
24    }
25}

Additional Reading:

The TodoSearchController gets the information of the returned todo entries from the TodoSearchService object. The RepositoryTodoSearchService class implements the TodoSearchService interface, and its findBySearchTerm() method simply passes the search term and the Sort object forward to the invoked repository method.

The source code of the RepositoryTodoSearchService class looks as follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.data.domain.Sort;
 3import org.springframework.stereotype.Service;
 4import org.springframework.transaction.annotation.Transactional;
 5
 6import java.util.List;
 7
 8@Service
 9final class RepositoryTodoSearchService implements TodoSearchService {
10
11    private final TodoRepository repository;
12
13    @Autowired
14    public RepositoryTodoSearchService(TodoRepository repository) {
15        this.repository = repository;
16    }
17
18    @Transactional(readOnly = true)
19    @Override
20    public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) {
21		//Obtain search results by invoking the preferred repository method.
22        List<Todo> searchResults = ...
23
24		return TodoMapper.mapEntitiesIntoDTOs(searchResults);
25    }
26}

Let’s move on and find out how we can use the Sort object.

Using the Sort Object

After we have created the Sort object manually or obtained it by using Spring Data web support, we have to create the database query that sorts its query results by using the Sort object.

Let’s start by finding out how we can sort all entities found from the database.

Sorting All Entities

If we want to sort all entities found from the database, we can use one of the following methods:

First, if we created our repository interface by extending the CrudRepository interface, we can modify it to extend only the PagingAndSortingRepository interface.

The relevant part of our repository interface looks as follows:

1import org.springframework.data.repository.PagingAndSortingRepository;
2
3interface TodoRepository extends PagingAndSortingRepository<Todo, Long> {
4
5}

Additional Reading:

The Javadoc of the PagingAndSortingRepository

The PagingAndSortingRepository interface declares one method that we can use when we want to get all entities found from the database and sort them:

The Iterable<T> findAll(Sort sort) method returns all entities found from the database and sorts them by using the sort options specified by the Sort object.

In other words, if we want to get a sorted list of all entities found from the database, we have to use the Iterable<T> findAll(Sort sort) method instead of the Iterable<T> findAll() method.

Second, if we created our repository interface by extending the Repository interface, we can declare the findAll(Sort sort) method in our repository interface.

The relevant part of our repository interface looks as follows:

 1import org.springframework.data.domain.Sort;
 2import org.springframework.data.repository.Repository;
 3
 4import java.util.List;
 5import java.util.Optional;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9    void delete(Todo deleted);
10
11    List<Todo> findAll(Sort sort);
12
13    Optional<Todo> findOne(Long id);
14
15    void flush();
16
17    Todo save(Todo persisted);
18}

We can now get a sorted of list of all entities found from the database by invoking the findAll() method and passing the Sort object as a method parameter.

Additional Reading:

Spring Data JPA Tutorial: CRUD

Let’s find out how we can sort the query results of database queries that use the query generation from the method name strategy.

Sorting the Query Results of Queries That Use the Query Generation From the Method Name Strategy

If we create our database queries from the method name of our query method, we can sort the query results by adding a new method parameter (Sort object) to the query method.

The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. If our query method uses the query generation from the method name strategy, its source code looks as follows:

 1import org.springframework.data.domain.Sort;
 2import org.springframework.data.repository.Repository;
 3
 4import java.util.List;
 5
 6interface TodoRepository extends Repository<Todo, Long> {
 7
 8    List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
 9                                                                     String titlePart,
10                                                                     Sort sort);
11}

Additional Reading:

Spring Data JPA Tutorial: Creating Database Queries From Method Names

Let’s move on and find out how we can sort the query results of JPQL queries that are created by using the @Query annotation.

Sorting the Query Results of JPQL Queries That Use the @Query Annotation

If we create our database queries by using JPQL and the @Query annotation, we can sort the query results by adding a new method parameter (Sort object) to the query method.

If you are creating a native query with the @Query annotation, you cannot sort the query results by using the Sort class. You have to add the sorting logic into the SQL query.

The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. If our query method uses the @Query annotation, its source code looks as follows:

 1import org.springframework.data.domain.Sort;
 2import org.springframework.data.repository.Repository;
 3import org.springframework.data.repository.query.Param;
 4
 5import java.util.List;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9	@Query("SELECT t FROM Todo t WHERE " +
10			"LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
11			"LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
12	List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, Sort sort);
13}

Additional Reading:

Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation

Let’s move on and find out how we can sort the query results of JPA criteria queries.

Sorting the Query Results of JPA Criteria Queries

If we create our database queries by using the JPA Criteria API, our repository interface must extend the JpaSpecificationExecutor<T> interface. This interface declares one method that we can use when we want to sort the query results of JPA criteria queries:

The List<T> findAll(Specification<T> spec, Sort sort) method returns all entities that fulfil the conditions specified by the Specification object. It sorts the returned entities by using the Sort object given as a method parameter.

In other words, we can sort the query results of JPA criteria queries by using the List<T> findAll(Specification<T> spec, Sort sort) method instead of the List<T> findAll(Specification<T> spec) method.

The source code of the RepositoryTodoSearchService class, which sorts our query results by using the Sort object, looks as follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.data.domain.Sort;
 3import org.springframework.stereotype.Service;
 4import org.springframework.transaction.annotation.Transactional;
 5
 6import java.util.List;
 7
 8import static net.petrikainulainen.springdata.jpa.todo.TodoSpecifications.titleOrDescriptionContainsIgnoreCase;
 9
10@Service
11final class RepositoryTodoSearchService implements TodoSearchService {
12
13    private final TodoRepository repository;
14
15    @Autowired
16    public RepositoryTodoSearchService(TodoRepository repository) {
17        this.repository = repository;
18    }
19
20    @Transactional(readOnly = true)
21    @Override
22    public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) {
23        Specification<Todo> searchSpec = titleOrDescriptionContainsIgnoreCase(searchTerm);
24        List<Todo> searchResults = repository.findAll(searchSpec, sort);
25        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
26    }
27}

Additional Reading:

Let’s find out how we can sort the query results of database queries that are created by using Querydsl.

Sorting Query Results of Querydsl Queries

If we create our database queries by using Querydsl, our repository interface must extend the QueryDslPredicateExecutor<T> interface. This interface declares one method that we can use when we want to sort the query results of the invoked query: - The Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>… orders) method returns all entities that fulfil the search conditions specified by the Predicate object and sorts the query results by using the sort options specified by the OrderSpecifier objects.

This means that we can sort the query results of an existing Querydsl query by following these steps: 1. Specify the sorting options by creating new OrderSpecifier objects. 2. Invoke the findAll() method, and pass the Predicate and OrderSpecier objects as method parameters.

For example, if we want to modify the findBySearchTerm() method of the RepositoryTodoSearchService class to sort the query results in ascending order by using the value of the title field, we have to make following changes to the RepositoryTodoSearchService class: 1. Add a private orderByTitleAsc() method to the class and implement by returning an OrderSpecifier object which specifies that the search results are sorted in ascending order by using the value of the title field. 2. Make the following changes to the findBySearchTerm() method: 1. Get the OrderSpecifier object by invoking the orderByTitleAsc() method. 2. Invoke the Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>… orders) method of the QueryDslPredicateExecutor interface instead of the Iterable<T> findAll(Predicate predicate) method. Pass the Predicate and OrderSpecifier objects as method parameters.

The source code of the RepositoryTodoSearchService class looks as follows:

 1import com.mysema.query.types.OrderSpecifier;
 2import com.mysema.query.types.Predicate predicate;
 3import org.springframework.beans.factory.annotation.Autowired;
 4import org.springframework.stereotype.Service;
 5import org.springframework.transaction.annotation.Transactional;
 6
 7import java.util.List;
 8
 9import static net.petrikainulainen.springdata.jpa.todo.TodoPredicates.titleOrDescriptionContainsIgnoreCase;
10
11@Service
12final class RepositoryTodoSearchService implements TodoSearchService {
13
14    private final TodoRepository repository;
15
16    @Autowired
17    public RepositoryTodoSearchService(TodoRepository repository) {
18        this.repository = repository;
19    }
20
21    @Transactional(readOnly = true)
22    @Override
23    public List<TodoDTO> findBySearchTerm(String searchTerm) {
24        Predicate searchPred = titleOrDescriptionContainsIgnoreCase(searchTerm);
25		OrderSpecifier sortSpec = orderByTitleAsc();
26        Iterable<Todo> searchResults = repository.findAll(searchPred, sortSpec);
27        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
28    }
29
30	private OrderSpecifier<String> orderByTitleAsc() {
31		return QTodo.todo.title.asc();
32	}
33}

Additional Reading: - Spring Data JPA Tutorial: Creating Database Queries With Querydsl - The Javadoc of the OrderSpecifier class - The Javadoc of the QueryDslPredicateExecutor interface

Let’s move on and find out which sorting method we should use.

Which Sorting Method Should We Use?

Sometimes the technique that we use to create our database queries forces us to use a specific sorting method. For example, - If our database queries are named queries or native queries that use the @Query annotation, we must add the sorting logic into our query strings. - If we create our database queries by using the JPA Criteria API, we have to sort the query results by using the Sort class. - If we create our database queries by using Querydsl, we have to sort the query results by using the OrderSpecifier class.

However, if we have a choice, we should put our query generation logic and sorting logic to the same place because it makes our code easier to read. This means that: - If we create our database queries by using SQL or JPQL, we should add the sorting logic into our query strings. - If we create our database queries by using the query generation from the method name strategy, we should use the same method for sorting our query results (append the OrderBy keyword to the method name). If we don’t want to use this method because the method name of our query method becomes too long, we should rewrite our query by using the @Query annotation.

Nevertheless, there are a couple of situations when we must separate our query generation logic and sorting logic: - If we have to paginate the query results of our database queries, we must sort them by using the Sort class. We will talk more about this in the next part of this tutorial. - If we must support dynamic sorting (i.e. the sorting direction and the used fields can be changed), we must sort our query results by using the Sort class because it is the only way that allows us to fulfil this requirement.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us five things: - We can create Sort objects manually or obtain them by using Spring Data web support. - We can configure Spring Data web support by annotating our application context configuration class with @EnableSpringDataWebSupport annotation. - We can create OrderSpecifier objects by using the Querydsl query types. - If the technique that we use for creating our database queries doesn’t force us to use a specific sorting method, we should put our query generation logic and sorting logic to the same place. - If we have to paginate the query results of our database queries, or we must support dynamic sorting, we must sort the query results by using the Sort class.

The next part of this tutorial describes how we can paginate the query results of our database queries.

P.S. You can get the example applications of this blog post from Github: query methods, JPA Criteria API, and Querydsl. If you decide to clone this repository, remember that the examples are found from the sorting branch.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.

Sorting Query Results With the Method Names of Our Query Methods

Sorting Query Results With Query Strings

Sorting Query Results With the Sort Class

Obtaining the Sort Object

Specifying the Sort Options Manually

Using Spring Data Web Support

Using the Sort Object

Sorting All Entities

Sorting the Query Results of Queries That Use the Query Generation From the Method Name Strategy

Sorting the Query Results of JPQL Queries That Use the @Query Annotation

Sorting the Query Results of JPA Criteria Queries

Sorting Query Results of Querydsl Queries

Which Sorting Method Should We Use?

Summary

Write Better Code and Save Time