Do want to get a better understanding of Spring web application architecture? If so, get started right now!

Spring Data JPA Tutorial Part Six: Sorting

Colorful graph

The fifth part of my Spring Data JPA tutorial described how you can create advanced queries with Spring Data JPA and Querydsl. This blog entry will describe how you can use Spring Data JPA for sorting the query results. As an example I will be adding two new requirements for my example application:

  • The list of persons shown in the front page should be sorted in ascending order by using the last name of a person.
  • The search results should be sorted in ascending order by using the last name of a person.

Spring Data JPA offers four different ways to sort the query results. Each of these approaches is described with more details in the following Sections.

Sorting with Method Name

If you are building your queries by using the query generation from method name strategy, you can sort the query results by using the OrderBy keyword in the name of your query method. If you want to search for persons whose last name matches with the given parameter and sort the query results, you can use the following query methods:

//Results are sorted in ascending order
public List<Person> findByLastNameOrderByLastNameAsc(String lastName);

//Results are sorted in descending order
public List<Person> findByLastNameOrderByLastNameDesc(String lastName)

Sorting with JPQL

If you are creating query method by using either @NamedQuery or @Query annotation as explained in the third part of this tutorial, you can sort the query results by using the ORDER BY keyword of the JPQL. For example, if you want to get all persons and sort the query results in ascending order by using the last name of a person, you can use the following query:

SELECT p from Person p ORDER BY p.lastName ASC

You can now consult the third part of this tutorial for finding out how you can use the @NamedQuery or @Query annotation to implement the actual query method.

Sorting with Sort Class

The second way to sort query results with Spring Data JPA is to use the org.springframework.data.domain.Sort class. You can use this approach for sorting the query results by following these steps:

  • Create an instance of the Sort class.
  • Pass the created instance as a parameter to the correct method.

I am going to describe to you three different ways for sorting the query results by using the Sort class. After that I will choose one of those methods and implement my first requirement with the selected method.

Query Generation by Method Name

You can pass the Sort object as a parameter to a query method, which uses the query generation by method name strategy. For instance, if you want search persons by last name and sort the results of the created query, you should add following method to your repository interface:

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

If you are interested of finding out more information about this query generation strategy, you should read the third part of this tutorial.

JpaRepository

As you might remember from the previous parts of this tutorial, Spring Data JPA repositories are just interfaces which extend other interfaces. One of those special interfaces is the JpaRepository<T, ID extends Serializable> interface. This interface declares the public List<T> findAll(Sort sort) method, which returns a list of all entities and sorts the entities in the order specified by the Sort object given as a parameter. If you are interested of obtaining a sorted list of all entities, using this method is the best way to do it.

The second part of my Spring Data JPA tutorial provides more information about the usage of the JpaRepository interface.

JPA Criteria API

If you are creating your queries with the JPA criteria API, your repository interface must extend the JpaSpecificationExecutor<T> interface. This interface declares the public List<T> findAll(Specification<T> spec, Sort sort) method, which returns a list of entities matching with the Specification given as a parameter. The returned entities are sorted in the order specified by the Sort object given as a parameter.

So, if you are interested of sorting the results of a query constructed by using the JPA criteria API, you can do it by using the described method. If you need more information about Spring Data JPA and the JPA criteria API, you can read the fourth part of this tutorial.

Example

I am going to describe next how you can implement the first requirement by using the findAll(Sort sort) method provided by the JpaRepository interface.

First, my repository interface must extend the JpaRepository interface. The source code of the PersonRepository is given in following:

import org.springframework.data.jpa.repository.JpaRepository;

public interface PersonRepository extends JpaRepository<Person, Long> {

}

Second, I created a private method called sortByLastNameAsc() to RepositoryPersonService class. This method is used in the findAll() method to obtain an instance of the Sort class. Third, I passed the obtained Sort object to the findAll() method of the JpaRepository interface. The source code of the relevant parts of the RepositoryPersonService is given in following:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Service
public class RepositoryPersonService implements PersonService {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(RepositoryPersonService.class);
    
    @Resource
    private PersonRepository personRepository;

    @Transactional(readOnly = true)
    @Override
    public List<Person> findAll() {
        LOGGER.debug("Finding all persons");
        //Passes the Sort object to the repository.
        return personRepository.findAll(sortByLastNameAsc());
    }

    /**
     * Returns a Sort object which sorts persons in ascending order by using the last name.
     * @return
     */
    private Sort sortByLastNameAsc() {
        return new Sort(Sort.Direction.ASC, "lastName");
    }
}

The last step was to fix the unit test of the findAll() method. The source code of the fixed unit test is given in following:

import org.junit.Before;
import org.junit.Test;
import org.mockito.ArgumentCaptor;
import org.springframework.data.domain.Sort;

import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;

public class RepositoryPersonServiceTest {
    
    private RepositoryPersonService personService;

    private PersonRepository personRepositoryMock;

    @Before
    public void setUp() {
        personService = new RepositoryPersonService();

        personRepositoryMock = mock(PersonRepository.class);
        personService.setPersonRepository(personRepositoryMock);
    }

    @Test
    public void findAll() {
        List<Person> persons = new ArrayList<Person>();
        when(personRepositoryMock.findAll(any(Sort.class))).thenReturn(persons);
        
        List<Person> returned = personService.findAll();

        ArgumentCaptor<Sort> sortArgument = ArgumentCaptor.forClass(Sort.class);
        verify(personRepositoryMock, times(1)).findAll(sortArgument.capture());

        verifyNoMoreInteractions(personRepositoryMock);

        Sort actualSort = sortArgument.getValue();
        assertEquals(Sort.Direction.ASC, actualSort.getOrderFor("lastName").getDirection());

        assertEquals(persons, returned);
    }
}

I have now demonstrated to you, how you can sort the persons presented in the front page of my example application in ascending order by using the last name of a person.

Sorting with Querydsl

If you are using Querydsl for building your queries, you can sort the query results by following these steps:

I will now describe to you how you can implement my second requirement with Querydsl.

First, I am ensuring that my repository extends the QueryDslPredicateExecutor interface. The source code of the PersonRepository is given in following:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;

public interface PersonRepository extends JpaRepository<Person, Long>, QueryDslPredicateExecutor<Person> {

}

Second, I created a class called PersonPredicates which has one method: lastNameIsLike(). This method is used to build the required query predicate. The source code of this class is given in following:

import com.mysema.query.types.Predicate;
import net.petrikainulainen.spring.datajpa.model.QPerson;

public class PersonPredicates {

    public static Predicate lastNameIsLike(final String searchTerm) {
        QPerson person = QPerson.person;
        return person.lastName.startsWithIgnoreCase(searchTerm);
    }
}

The next step was to made some changes to the search() method of the RepositoryPersonService class. This method is explained with more details in following:

  • The predicate is still obtained by calling the lastNameIsLike() method of the PersonPredicates class.
  • The OrderSpecifier instance is obtained by calling a private orderByLastNameAsc() method of RepositoryPersonService class.
  • The predicate and the OrderSpecifier instance are passed as a parameter to the findAll(Predicate predicate, OrderSpecifier order) method of my repository implementation.

The source code of the relevant parts of the RepositoryPersonService class is given in following:

import com.mysema.query.types.OrderSpecifier;
import net.petrikainulainen.spring.datajpa.model.QPerson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Service
public class RepositoryPersonService implements PersonService {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(RepositoryPersonService.class);
    
    @Resource
    private PersonRepository personRepository;
    
    @Transactional(readOnly = true)
    @Override
    public List<Person> search(String searchTerm) {
        LOGGER.debug("Searching persons with search term: " + searchTerm);

        //Passes the specification created by PersonPredicates class and the OrderSpecifier object to the repository.
        Iterable<Person> persons = personRepository.findAll(lastNameIsLike(searchTerm), orderByLastNameAsc());

        return constructList(persons);
    }

    /**
     * Returns an OrderSpecifier object which sorts person in ascending order by using the last name.
     * @return
     */
    private OrderSpecifier<String> orderByLastNameAsc() {
        return QPerson.person.lastName.asc();
    }

    private List<Person> constructList(Iterable<Person> persons) {
        List<Person> list = new ArrayList<Person>();
        for (Person person: persons) {
            list.add(person);
        }
        return list;
    }
}

The last step was to the fix the unit test of the search() method. The source code of the fixed unit test is given in following:

import com.mysema.query.types.Order;
import com.mysema.query.types.OrderSpecifier;
import com.mysema.query.types.expr.BooleanExpression;
import net.petrikainulainen.spring.datajpa.model.QPerson;
import org.junit.Before;
import org.junit.Test;
import org.mockito.ArgumentCaptor;

import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;

public class RepositoryPersonServiceTest {
    private static final String SEARCH_TERM = "foo";    

    private RepositoryPersonService personService;

    private PersonRepository personRepositoryMock;

    @Before
    public void setUp() {
        personService = new RepositoryPersonService();

        personRepositoryMock = mock(PersonRepository.class);
        personService.setPersonRepository(personRepositoryMock);
    }

    @Test
    public void search() {
        List<Person> expected = new ArrayList<Person>();
        when(personRepositoryMock.findAll(any(BooleanExpression.class), any(OrderSpecifier.class))).thenReturn(expected);
        
        List<Person> actual = personService.search(SEARCH_TERM);

        ArgumentCaptor<OrderSpecifier> orderArgument = ArgumentCaptor.forClass(OrderSpecifier.class);
        verify(personRepositoryMock, times(1)).findAll(any(BooleanExpression.class), orderArgument.capture());

        verifyNoMoreInteractions(personRepositoryMock);

        OrderSpecifier actualOrder = orderArgument.getValue();
        assertEquals(Order.ASC, actualOrder.getOrder());
        assertEquals(QPerson.person.lastName, actualOrder.getTarget());

        assertEquals(expected, actual);
    }
}

I have now demonstrated to you how you can you use Querydsl to sort the person search results in ascending order by using the last name of a person.

What is Next?

I have now described to you three different ways to sort the query results with Spring Data JPA. I have also implemented a fully functional example application, which is available at Github. The next part of my tutorial describes how you can implement pagination with Spring Data JPA.

If you want to learn more about Spring Data JPA, you should read all parts of my Spring Data JPA tutorial.
If you enjoyed reading this blog post, you should follow me on Twitter:

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

22 comments… add one

  • 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.

    Reply
    • Devin,

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

      Reply
  • 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?

    Reply
    • 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.

      Reply
  • 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!

    Reply
    • 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.

      Reply
  • 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!

    Reply
    • 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.

      Reply
  • 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?

    Reply
    • 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.

      Reply
  • 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!

    Reply
    • 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.

      Reply
  • 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 …

    Reply
    • 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.

      Reply
  • 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!

    Reply
    • 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.

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

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

      Reply
  • Great!

    Reply
    • Thanks!

      Reply
  • 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.

    Reply
    • 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.

      Reply

Leave a Comment