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:

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:

  1. Append the OrderBy keyword to the method name of our query method.
  2. 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.
  3. 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.
  4. 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:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findByTitleOrderByTitleAsc(String title);
}

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:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findByTitleOrderByTitleAscDescriptionDesc(String title);
}

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:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCaseOrderByTitleAsc(String descriptionPart,
                                                                                    String titlePart);
}

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:

SELECT t FROM Todo t WHERE 
	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR 
	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) 
	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.

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

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:

  1. Obtain the Sort object that describes the sorting options of the invoked database query.
  2. 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:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
final class RepositoryTodoSearchService implements TodoSearchService {

    private final TodoRepository repository;

    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    @Override
    public List<TodoDTO> findBySearchTerm(String searchTerm) {
		Sort sortSpec = orderBy();
		
		//Obtain search results by invoking the preferred repository method.
        List<Todo> searchResults = ...
        
		return TodoMapper.mapEntitiesIntoDTOs(searchResults);
    }
	
	private Sort orderBy() {
		//Create a new Sort object here.
	}
}

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:

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

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:

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

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:

private Sort orderBy() {
	return new Sort(Sort.Direction.DESC, "description")
				.and(new Sort(Sort.Direction.ASC, "title"));
}
Additional Reading:

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:

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.web.config.EnableSpringDataWebSupport;
import org.springframework.transaction.annotation.EnableTransactionManagement;


@Configuration
@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
@EnableJpaRepositories(basePackages = {
        "net.petrikainulainen.springdata.jpa.todo"
})
@EnableTransactionManagement
@EnableSpringDataWebSupport
class PersistenceContext {
}

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:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
final class TodoSearchController {

    private final TodoSearchService searchService;

    @Autowired
    public TodoSearchController(TodoSearchService searchService) {
        this.searchService = searchService;
    }

    @RequestMapping(value = "/api/todo/search", method = RequestMethod.GET)
    public List<TodoDTO> findBySearchTerm(@RequestParam("searchTerm") String searchTerm, 
										  Sort sort) {
        return searchService.findBySearchTerm(searchTerm, sort);
    }
}

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:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
final class RepositoryTodoSearchService implements TodoSearchService {

    private final TodoRepository repository;

    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    @Override
    public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) {		
		//Obtain search results by invoking the preferred repository method.
        List<Todo> searchResults = ...
        
		return TodoMapper.mapEntitiesIntoDTOs(searchResults);
    }
}

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:

import org.springframework.data.repository.PagingAndSortingRepository;
 
interface TodoRepository extends PagingAndSortingRepository<Todo, Long> {
 
}

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:

import org.springframework.data.domain.Sort;
import org.springframework.data.repository.Repository;

import java.util.List;
import java.util.Optional;

interface TodoRepository extends Repository<Todo, Long> {

    void delete(Todo deleted);

    List<Todo> findAll(Sort sort);

    Optional<Todo> findOne(Long id);

    void flush();

    Todo save(Todo persisted);
}

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:

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:

import org.springframework.data.domain.Sort;
import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
                                                                     String titlePart,
                                                                     Sort sort);
}

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:

import org.springframework.data.domain.Sort;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	@Query("SELECT t FROM Todo t WHERE " +
			"LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
			"LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
	List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, Sort sort);
}

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:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
import static net.petrikainulainen.springdata.jpa.todo.TodoSpecifications.titleOrDescriptionContainsIgnoreCase;
 
@Service
final class RepositoryTodoSearchService implements TodoSearchService {
 
    private final TodoRepository repository;
 
    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }
 
    @Transactional(readOnly = true)
    @Override
    public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) {
        Specification<Todo> searchSpec = titleOrDescriptionContainsIgnoreCase(searchTerm);
        List<Todo> searchResults = repository.findAll(searchSpec, sort);
        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
    }
}

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:

import com.mysema.query.types.OrderSpecifier;
import com.mysema.query.types.Predicate predicate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
import static net.petrikainulainen.springdata.jpa.todo.TodoPredicates.titleOrDescriptionContainsIgnoreCase;
 
@Service
final class RepositoryTodoSearchService implements TodoSearchService {
 
    private final TodoRepository repository;
 
    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }
 
    @Transactional(readOnly = true)
    @Override
    public List<TodoDTO> findBySearchTerm(String searchTerm) {
        Predicate searchPred = titleOrDescriptionContainsIgnoreCase(searchTerm);
		OrderSpecifier sortSpec = orderByTitleAsc();
        Iterable<Todo> searchResults = repository.findAll(searchPred, sortSpec);
        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
    }
	
	private OrderSpecifier<String> orderByTitleAsc() {
		return QTodo.todo.title.asc();
	}
}

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.
64 comments… add one
  • Devin Apr 12, 2012 @ 5:40

    Great work, this blog was a big help for me. I am just getting into using Spring Data JPA and there is not much info out there on it. I now understand it. Thanks.

    • Petri Apr 12, 2012 @ 8:25

      Devin,

      thanks for your comment. It was great to hear that I could help you out.

  • Thiago Henrique Mar 18, 2013 @ 15:08

    Hi Petry,

    I have one question, how do you using one "Generic" repository, for example:

    public interface GenericRepository
    extends JpaRepository {

    //method custom
    void sharedCustomMethod(ID id);
    }

    You must have a GenericRepositoryImpl ?

    For I am following the documentation JPA is indicating the use of this form, most here is giving the error: Invalid derived query! In shared property found for type java.lang.Object in the declaration of method.

    can you help me?

    • Petr Mar 18, 2013 @ 20:31

      Are you trying to add methods to a single repository or to all repositories?

      If you are trying to add methods to a single repository, you can follow the instructions given in this blog post: Adding Functionality to a Repository.

      If you want to add methods to all repositories, you should follow the instructions given in the reference manual of Spring Data JPA.

      I hope that this answered to your question.

  • Thiago Henrique Mar 18, 2013 @ 22:20

    Hi,

    I need to add methods to all repositories. And I put it as the specification:

    public interface MyRepository
    extends JpaRepository {

       ClasseX sharedCustomMethod(ID id);
    }

    But, Is not working, an error occurs saying:

    Invalid derived query! No property found for type sharedCustomMethod com.example.domain.ClasseX.

    So, it if I put anotation @ Query upon the method, the error disappears. For example:

    public interface MyRepository
    extends JpaRepository {

    @Query
       ClasseX sharedCustomMethod(ID id);
    }

    understand?

    Thank you, for help me!

    • Petri Mar 18, 2013 @ 23:09

      The exception is caused by the fact that the Spring Data repository infrastructure thinks that you want to generate the executed query from the method name of your query method and cannot find the sharedCustomMethod property from ClasseX class.

      If you annotate the method with the @Query annotation, the problem disappears because Spring Data JPA executes the query given as the value of the @Query annotation when your query method is called.

      If you want to add custom methods to all repositories, you must follow the instructions given in these resources:

      I hope that this answered to your question.

      • Dmitri Arkhipov Feb 10, 2017 @ 4:11

        Thank you sir!

        • Petri Feb 15, 2017 @ 18:19

          You are welcome!

  • Hendrik Apr 11, 2013 @ 18:45

    Hi, is there a way to sort for a sublist. I have an event which has participants and now I want to find the event with the most participants. Thought something like the following would work.
    repository.findAll(QEvent.event.participants);
    or
    repository.findAll(QEvent.event.participants.size());
    I think that is a little problem for you but I don't get it to work.
    Thanks for your help!

    • Petri Apr 12, 2013 @ 21:07

      Hi,

      I think that the easiest way to do this is to create a query method and limit the query results by applying pagination. You can do this by following these steps:

      1. Add the query method to your repository interface. This method returns a list of Event objects and takes a Pageable object as a parameter.
      2. Annotate the query method with the @Query annotation and set the executed query as its value.

      The source code of the event repository looks as follows:

      
      public interface EventRepository extends JpaRepository<Event, Long> {
      	
      	@Query("SELECT e From Event e ORDER BY e.participants.size DESC")
      	public List<Event> findEvents(Pageable page);
      }
      
      

      You can now get the Event which has the most participants by using the following code:

      
      List<Event> events = repository.findEvents(new PageRequest(0, 1))
      //Remember to verify that event was found. I left this out for the sake of clarity.
      Event mostParticipants = event.get(0);
      
      

      I did not test this but it should do the trick.

  • Hendrik Apr 17, 2013 @ 11:55

    Thank you very much!
    That did it!
    I thought that there has to be a solution with predicates but with the @Query-annotation works great.

    I have another Problem that I solved in the services but perhaps there is a solution without handling it manually. What is when there are two events with the same number of participants? I want to have that event on first position that has the number of participants at first time.
    Is there a best practice doing it with a query or would you do that also in the code?

    • Petri Apr 24, 2013 @ 19:53

      You are welcome! I am happy that I could help you out.

      About your other problem:

      If your current solution works and it is not a resource hog, I recommend that you continue using it.

      On the other hand, if you want to handle this on database, you can solve this by following these steps:

      1. Add a timestamp field to the Event class. This field is updated every time when a new participant registers to the event.
      2. Modify the sorting logic of your query to take this new field into account.

      The source code of your repository interface looks as follows:

      
      public interface EventRepository extends JpaRepository {
      	
      	@Query("SELECT e From Event e ORDER BY e.participants.size DESC,e.regTime ASC")
      	public List<Event> findEvents(Pageable page);
      }
      
      

      I hope that this answered to your question.

  • Hendrik Apr 25, 2013 @ 14:43

    The solution with with timestamp was an idea I also had. The problem with this is, that I always have to watch what happens when a participant is canceling its participation. Maybe this is something where it comes to errors in database when it is not well handled. So I think I will try the solution I have at the moment and when I see that it leads to lags I have to think about the solution with the timestamp.

    Thank you again for your great help!

    • Petri Apr 25, 2013 @ 18:48

      I agree that dealing with a cancellations can be tricky and you might have to change your domain model in order to solve this problem. I though about this and I think that can you solve this problem by following these steps:

      1. Introduce a new model called Registration. This model contains a reference to the event, participant and a registration timestamp.
      2. Change the Event class to contain a list of Registration objects.
      3. When a participant cancels his registration, you have to remove the registration from the associated event.
      4. Find the most recent registration and update the timestamp of the associated event.

      I think that this would do the trick.

  • Thiago Henrique May 9, 2013 @ 22:37

    Hi Petri,

    First I would like to apologize for not responding in time, but I was in an uproar here and I got no time to answer.

    Thank you for your assistance because it was quite valid.

    But now I got a question somewhat puzzling, and his experience must have been through it. It is this: I noticed that in the Spring MVC framework, the business rules of the application hum in the service layer, but if I need specific rules, also more common for the entire application when the use of that object in the service layer. Like, at some point my controller will call the desired service, but prior to persist itself, I'll run some rules (validations), as it would be? as you do it in your applications in case.

    Thanks again, and look forward ...

    PS: If you do not understand can ask what I try to explain better ...

    • Petri May 9, 2013 @ 23:46

      Hi Thiago,

      Don't worry about not having time to answer to me. I think it is only natural that sometimes we have more important things to take care of.

      About your problem:

      Although it is quite common that Spring applications have business logic in the service layer, I think that the business logic should be added to the domain model. If I understood you correctly, you want to validate that the state of your domain object is correct before you persist it to the database. If this is the case, you probably want to do this check when a new domain object is added to the database or the information of an existing domain object is updated.

      There is a quite clean way to implement this. You do that by following these steps:

      1. Add the validation method to your domain model object. This method should throw an exception if the state of your domain object is invalid.
      2. Call the validation method before a new domain model object is persisted or existing one is updated. The optimal way to do this is to call the validation method inside your domain object when a new domain object is created or the information of an existing one is changed. This way the other classes which deal with these objects won't have to do it (you can also change the visibility of the validation method to private so that it is impossible to call it outside your domain model class).

      I hope that this answered to your question.

  • Thiago Henrique May 21, 2013 @ 20:07

    Hi Petry,

    Thanks for answer my question, in true i don't want validate my object, because i'm using validation via hibernate annotation and BindingResult , then the i want say is validate business rules properly said, in nivel of rules really, for example:

    If one user before be inserted, need to validate if it is active, have dependents and have age above 20 years, where would this validation? understood.

    Most I've managed to solve here, I created an interface that defines my methods that must be performed before this insertion, and each service implements this interface, implementing their respective rules.

    Thank again you for your help ... it's people like you that our community needs, willing to help ...

    Thank you again!

    • Petri May 22, 2013 @ 10:24

      Hi Thiago,

      Maybe I did not choose my words wisely in my first answer (the word validation was maybe not a good choice). I will try to clarify what I meant.

      When you are creating a new object or updating the information information of an existing object, you have to implement a two-step validation process. The steps of this process are described in the following:

      First, you need to validate that the entered data is "correct". This validation typically ensures that all the required fields are given and that the given data is "correct". This is the validation which is done in the controller classes.

      Second, you need enforce the business rules of your application. The best place for this logic is to add it to your domain model class. There are basically three reasons for this:

      • If the business rule changes, you don't have to change them in multiple locations.
      • It makes your code cleaner (especially the service layer) because the services don't have to worry about enforcing the business rules of your application.
      • When you need to check how the business rules are implemented, you know where to find the implementation.

      You mentioned that you are enforcing the business rules on the service layer. Although this typical for Spring applications, I think that it has one big problem:

      The service layer should not know when the state of an object is not legal. Only the object in question should know this. I think that the best way to demonstrate this is to think about the following situation:

      Let's assume that I am a service class and you are an object. I order you to do something. Who should check that you are ready to do it? I think that it would a bit weird if it would be me.

      Here is the source code of a simple domain model class which enforces the business rules when new objects are created or the information of an existing object is updated:

      
      public class Person {
      
          private int age;
      
          //If you use a builder pattern for creating your objects, you can
          //call the enforceBusinessRules() method in the build() method.
          public Person(int age) throws Exception {
              this.age = age;
              enforceBusinessRules();
          }
      
          public void update(int age) throws Exception {
              this.age = age;
              enforceBusinessRules();
          }
      	
          //Decide whether you want to throw a checked exception or not.
          private void enforceBusinessRules() throws Exception {
              if (this.age < 18) {
                  throw new Exception("Too young");
              }
          }
      }
      
      

      I hope that this answered to your question.

  • Jueser Jul 17, 2014 @ 2:26

    You're amazing my friend, Congratulations for your great tutorials.
    Thank you a lot, has been very useful to me.

    • Petri Jul 17, 2014 @ 17:05

      Thank you for your kind words. I really appreciate them. It is is good to hear that these tutorials have been useful to you.

  • YongHun Oct 4, 2014 @ 12:23

    Great!

    • Petri Oct 4, 2014 @ 16:46

      Thanks!

  • Olli Nov 20, 2014 @ 9:48

    Great examples, I especially like that you have also examples of unit tests :) Spring Data JPA documentation sucks, for example using Sort there's zero lines of howto use it.

    • Petri Nov 20, 2014 @ 9:55

      Olli,

      thank you for your kind words.

      You might want to check out my blog post titled: Spring Data Solr Tutorial: Sorting. It has a section titled: 'Specifying the Sort Options of a Query' that explains how you can create new Sort objects.

      Also, I plan to update my Spring Data JPA tutorial since my current tutorial is a bit obsolete. Thanks for pointing out that I can search inspiration from the reference manual of Spring Data JPA.

  • mark Dec 19, 2014 @ 4:41

    Kiitos suuri blogi (I hope that means, thanks for your great blog)
    It had the best explanation for the sorting in the repository methods. However, I could still find no way to sort beyond the basic field name, like this:

    
    private Sort sortByTranslitDesc() {
    	return new Sort(Sort.Direction.DESC, "translit");
    }
    
    

    I want to sort on an expression on a field name, like this:

    
    private Sort sortByTranslitLengthDesc() {
    	return new Sort(Sort.Direction.DESC, "translit.length");
    }
    
    

    Is that possible?

    Thanks
    Mark.

    • Anonymous Dec 19, 2014 @ 20:32

      I might have solved my own problem like this in a named Query (taken from another post on this page)

      
      @Query(
      "SELECT l FROM GreekLetter l WHERE l.translit Like :search% ORDER BY length(l.translit) Desc"
      )
      public List findByTranslitLengthDesc(@Param("search") String translit);
      
      

      Still would have been nice if something like this would have worked:

      
      private Sort sortByTranslitLengthDesc() {
      	// instead of translit.length
      	return new Sort(Sort.Direction.DESC, “length(translit)”); 
      }
      
      
      • Petri Dec 19, 2014 @ 21:45

        Hi,

        I am sorry that it took me some time to answer to your question. I have been trying to find a solution to the same problem for a while now, but I haven't been able to find a way to do this by using the Sort class.

        The Javadoc of the Sort class states that:

        Sort option for queries. You have to provide at least a list of properties to sort for that must not include null or empty strings.

        I assume that you can use the Sort class only when you want to sort your query results by using the values of your entity's properties. That is why you cannot use it if you want to sort your query results by using the size of the associated collection (it would require a some kind of expression support that seems to be missing).

        In other words, as far as I know, you have to use JPQL to solve this problem. However, I would love to be wrong.

  • Pietro Bonanno Aug 12, 2015 @ 0:10

    Thanks for this really in-depth analysis of sorting with our loved Spring!

    In my limited experience, I found myself to use less and less these annotations/hierarchies. Most of the time I have to optimize my queries, joining with other entities or returning a subset of entity's properties.

    So I end with writing a whole querydsl query (starting from Entity Manager) and missing all these goodies.

    • Petri Aug 12, 2015 @ 9:24

      Thank you for your kind words! I really appreciate them.

      In my limited experience, I found myself to use less and less these annotations/hierarchies. Most of the time I have to optimize my queries, joining with other entities or returning a subset of entity’s properties.

      I have noticed the same thing. Although I prefer using the @Query annotation, the syntax that is required to query DTOs is pretty awful. That is why I often end up adding custom methods into my repository interfaces.

      So I end with writing a whole querydsl query (starting from Entity Manager) and missing all these goodies.

      I use NamedParameterJdbcTemplate and BeanPropertyRowMapper for this purpose. Of course, if I need to create dynamic queries, I will use Querydsl (if it is possible).

  • Anonymous Aug 12, 2015 @ 14:30

    @service

    
    commitTwoRep() {
    	emp.save(empEntity);
    	dept.save(depEnitywithIncorrectLength);
    }
    
    

    I thought emp.save would not save since depEnity has wrong length (max is 4, i have passed 10).

    what happens is, emp entity saved successfully and dept.save throws Exception for max length.

    is there anything to be done?

    • Petri Aug 12, 2015 @ 20:26

      Is the commitTwoRep() method invoked inside a transaction? If the emp entity is saved even though the dept.save() method throws an exception, I assume that you are not using transactions. If you don't know how to use the declarative transaction management, read the section 12.5 Declarative transaction management of the Spring Framework reference documentation.

      • Anonymous Aug 13, 2015 @ 7:26

        Yes, you are right. commitTwoRep() has declared as Transnational.

        @Transactional //this was missing in the previous comment
        commitTwoRep() {

        • Petri Aug 13, 2015 @ 19:51

          Is the commitTwoRep() method public? If it is not public, you should make it public because the @Transactional annotation works only when the annotated method is public.

          If the method is public, it is impossible to say what is wrong because I am not able to debug your code (or see your app context configuration). Can you add the whole class and your application context configuration to Pastebin.com?

          If you cannot share your code, you should check out a blog post titled: Tips for Debugging Spring's @Transactional Annotation.

  • SANKAR Aug 17, 2015 @ 13:39

    Can we use In operator in native query to delete records? seems its not working.

    
    interface subjectRepo extends JpaRepository {
    	@Modifying
    	@Query(
    		value = "delete from subject where regno=:regno and subjectcode in (:subjectcode)", 
    		nativeQuery = true
    	)
    	void deletesubjects(@Param("regno") String regno, 
    						@Param("subjectcode") String subjectcode);
    }
    
    
    • Petri Aug 19, 2015 @ 21:01

      Yes. Because I had never used the IN operator in a native query, I did a small test and noticed that we can use it in native queries as well.

      First, if you want to find something from the database, you can use this query method:

      
      interface TodoRepository extends Repository<Todo, Long> {
      
          @Query(value = "SELECT * FROM todos t WHERE t.id IN :ids",
                  nativeQuery = true
          )
          List findByIds(@Param("ids") List<Long> ids);
      }
      
      

      Second, if you want to delete something from the database, you can use this query method:

      
      interface TodoRepository extends Repository<Todo, Long> {
      
          @Modifying
          @Query(value = "DELETE FROM todos t WHERE t.id IN :ids",
                  nativeQuery = true
          )
          void deleteByIds(@Param("ids") List<Long> ids);
      }
      
      

      If you have any additional questions, don't hesitate to ask them.

  • sankar Dec 8, 2015 @ 7:48

    Hi, I have been using JpaRepository in my application, now i have to save around 2000 entities.

    is it advisable to use save (Interable EntityList>, what is the maximum entities can be saved in one transaction? or what is the best limit?

    • Petri Dec 8, 2015 @ 21:24

      Hi Sankar,

      It's hard to give specific advice because this really depends from your use case and the saved entity. The only way to know this is to profile your application and see how it behaves when you try to save 10, 50, 100, 500, 1000, and 2000 entities. Basically the idea is to save X entities inside one transaction and see which X fulfills your performance requirements.

      Is there a reason why you want to save so many entities inside one transaction?

      • Anonymous Dec 11, 2015 @ 9:08

        Its like recreating groups for a company. Delete old groups emp and recreate new groups for all the emp.

        • Petri Dec 14, 2015 @ 21:03

          Is this a batch job? If so, you should consider using Spring Batch.

  • Brent Jan 12, 2016 @ 8:51

    Hi Petri,

    Your blog is amazing! It's really helpful to me.

    I'm a beginner to Spring-data-jpa, so it's not surprised that I have met many questions, thanks for your detailed examples, I have solved most of them. But recently I was stuck by one question:

    I want to add collation variable in query, so I can execute query like below dynamically:

    
    Select b.* from Business b Order by b.name desc COLLATE "en_US" 
    
    

    I mean the b.name, direction and "en_US" should be variables.

    Do you have a proper solution? Thanks in advance.

    Best Wishes!
    Brent.

    • Petri Jan 12, 2016 @ 22:18

      Hi Brent,

      If you want to specify the name of the sort property (e.g. b.name) and the sort direction (e.g. desc) dynamically, you need sort your query results by using the Sort class (this is explained in this blog post).

      However, JPQL doesn't support the COLLATE keyword. This means that you have to use SQL query for this purpose. The problem is that AFAIK you cannot set the sort field and the sort order dynamically if you use native queries with Spring Data JPA.

      In other words, it seems that you cannot fulfill your requirements with Spring Data JPA.

      • Brent Jan 13, 2016 @ 3:31

        Hi Petri,

        Thanks for your guidance. Do you mean that even I choose native SQL, it's still impossible to add COLLATE keyword in the query? So I have to turn to Spring jdbcTemplate or something related to dialect?

        Thanks.
        Brent.

        • Petri Jan 13, 2016 @ 9:22

          Hi Brent,

          I meant that AFAIK you cannot support dynamic sorting (specify sort property and direction) and use the COLLATE keyword at the same time (if you use Spring Data JPA). It seems that your "best" choice is to use JdbcTemplate.

          • Leandro de Jesus Jorge Sep 14, 2018 @ 20:49

            you can use like this, like a Switch case clause, you use 'any string' just to control the direction of your order by, direction of what column will be ordered, for 1 column you have to use 1 case, for instance if you have two column you have to use 2 'CASE WHEN' like the example below:

            +" ORDER BY CASE WHEN :sort = 'any string' THEN yourSqlColumn END ASC "
            +" , CASE WHEN :sort = 'any string' THEN yourSqlColumn END ASC "

            this is the most dynamic way to have a order by using Spring data jpa you will find.

          • Petri Sep 25, 2018 @ 22:49

            This looks awesome. Thank you for sharing :)

  • Juanca Feb 25, 2016 @ 14:36

    Hi, thank you for this post!

    This was useful to implement an example with spring data, but now I want to do the unit test but I don't know to to mock the Pageable object. I have the following code:

    
    @Service
    @Transactional
    public class WorkerServiceImpl implements WorkerService {
    	
    	@Autowired
    	private WorkerRepository workerRepository;
    
    	public Page findAll(int page, int size) {
    		Pageable pageable = new PageRequest(page - 1,size, Direction.ASC,"id");
    		return workerRepository.findAll(pageable);
    	}
    }
    
    

    could you help me please?, I don't know how to create a mockito object to test this method, I have been searching some example but I don't found anything that can be useful.

    • Petri Feb 25, 2016 @ 20:52

      Hi Juanca,

      First, if you want to verify that your service method returns the correct objects, you need to create a WorkerRepository mock in your setup method. After you have done this, you can configure it to return a list of Worker objects by using the following code:

      
      List<Worker> workers = new ArrayList<>();
      
      //If you want to use "normal" Mockito
      when(workerRepository.findAll(isA(Pageable.class))).thenReturn(workers);
      
      //If you want to use BDDMockito
      given(workerRepository.findAll(isA(Pageable.class))).willReturn(workers);
      
      

      Second, if you want to verify that your service method creates a Pageable object with correct page number and page size, you have to use argument captors. You can do this by using the following code:

      
      ArgumentCaptor<Pageable> pageableArgument = ArgumentCaptor.forClass(Pageable.class);
      verify(workerRepository, times(1)).findAll(pageableArgument.capture());
      
      Pageable actualPageable = pageableArgument.getValue()
      //Write assertions here
      
      

      P.S. If you use Java 8, you might want to read this blog post.

      • Juanca Feb 26, 2016 @ 15:30

        Thank you so much Petri, it was very useful your help.

        Kind regards,

        • Petri Feb 26, 2016 @ 18:59

          You are welcome!

  • Anaice Apr 1, 2016 @ 1:54

    Hi Petri,

    Your website is awesome!
    Can you help me? How do I change the name of the query parameters used in Pageable ? By default they are size = and page= . If I needed to change pageSize = and currentPage= . What would be the best approach?
    Thanks!

    • Petri Apr 1, 2016 @ 18:37

      Hi Anaice,

      You need to customize the default configuration provided by Spring Data JPA. The Reference Documentation of Spring Data JPA describes how you can do this:

      To customize this behavior extend either SpringDataWebConfiguration or the HATEOAS-enabled equivalent and override the pageableResolver() or sortResolver() methods and import your customized configuration file instead of using the @Enable-annotation.

      In your case, you have to override the pageableResolver() method and implement it by following these steps:

      1. Create a new PageableHandlerMethodArgumentResolver object.
      2. Change the parameter names by using the setPageParameterName() and setSizeParameterName() methods.
      3. Return the created object.

      If you have any additional questions, don't hesitate to ask them.

      • Anaice Apr 1, 2016 @ 20:34

        Hi Petri,

        Accurate as always.
        Thank you very much!

        
        public class MySpringDataWebConfiguration extends SpringDataWebConfiguration{
        	
        	@Override
        	public PageableHandlerMethodArgumentResolver pageableResolver() {
        		PageableHandlerMethodArgumentResolver resolver = 
        			new PageableHandlerMethodArgumentResolver();
        		resolver.setPageParameterName("currentPage"); //url mapping changed
        		resolver.setSizeParameterName("itemsPerPage"); //url mapping changed
        		return resolver;
        	}
        }
        
        public class AppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
        	@Override
        	protected Class[] getRootConfigClasses() {
        		return new Class[]  { MyAppContext.class, MySpringDataWebConfiguration.class };
        	}
        
        	@Override
        	protected Class[] getServletConfigClasses() {
        		return null;
        	}
        
        	@Override
        	protected String[] getServletMappings() {
        		return new String[] {"/*"};
        	}	
        }
        
        
        • Petri Apr 3, 2016 @ 15:18

          Hi Anaice,

          You are welcome. I am happy to hear that you were able to solve your problem. Also, thank you for sharing your solution. I bet that it is useful to other people who have the same problem.

  • enigma Jun 2, 2016 @ 17:08

    Is it possible to have a complex sort query to be written in specification?
    eg, a simple table with 3 columns
    (ID date_of_death date_of_birth)
    Now i want to order based on 2 date columns, with a simple logic written in sql may look like

    Select ID,date_of_birth,date_of_death from SOMETABLE ORDER BY CASE
    WHEN date_of_birth IS NULL THEN date_of_death
    WHEN date_of_death IS NULL THEN date_of_birth
    WHEN date_of_birth < date_of_death THEN date_of_birth

    END;

    Could you please enlighten me if it's possible

  • Krish Dec 30, 2016 @ 14:23

    Hi,
    Very nice tutorial. A lot example queries you mentioned. I was wondering is there any possible query for finding only one random id? Thanks for your efforts. really appreciated.

    • Petri Dec 31, 2016 @ 10:01

      Hi,

      It is possible, but you need to use SQL. This blog post explains how you can select a random database row when you are using MySQL, PostgreSQL, Microsoft SQL Server, DB2, or Oracle. That being said, this query might be quite slow (more details).

  • Anonymous May 21, 2018 @ 12:56

    What if i pass sorting as null in findAll Method does it givies any error

    • Petri May 22, 2018 @ 9:40

      I have never tried that, but I assume that Spring Data JPA will throw an exception if the Sort object is null. I would probably specify a default sort direction or invoke a method that doesn't take a Sort object as a method parameter.

  • arun singh May 22, 2018 @ 15:55

    Hello sir,
    within orderBy() method of "Sorting" return new Sort(Sort.Direction.ASC, "title");
    May I know this "title" is an exact field of POJO class or representing directly a column name in DB?

  • s Oct 26, 2018 @ 15:04

    how i can sort by usint multipel table below is my repository
    public interface DownPaymentRepository extends JpaRepository {

    @Query(value = "select ddc.sku as sku, slc.description as description from DefaultDownpaymentConfig ddc,SkuLtcConfig slc where ddc.sku = slc.sku order by slc.description desc")
    public Page getAlldefaultDownpaymentConfig_SkuAndskuLtcConfig_Description(Pageable pageable);

    }

    • Petri Nov 14, 2018 @ 21:09

      Hi,

      I have never done this myself, but based on this StackOverflow answer, you should be able to solve your problem as long as you specify the used alias when you create a new Sort object. Also, keep mind that you can combine multiple Sort objects by using the static by() method of the Sort class.

  • Alena Jul 14, 2020 @ 18:49

    Thank you so much! Great tutorial, a real blessing

    • PetrI Jul 15, 2020 @ 9:12

      Thank you for your kind words. I really appreciate them!

Leave a Reply