The fifth part of my Spring Data JPA tutorialdescribed 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:
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 BYkeyword 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:
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 theorg.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:
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 theJpaRepository<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 thefindAll(Sort sort) method provided by the JpaRepository interface.
First, my repository interface must extend the JpaRepository interface. The source code of thePersonRepository is given in following:
public interface PersonRepository extends JpaRepository<Person, Long> {
}
Second, I created a private method called sortByLastNameAsc() to RepositoryPersonServiceclass. 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.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.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:
- Create an instance of the com.mysema.query.types.OrderSpecifier class.
- Pass the created instance as a parameter to the findAll(Predicate predicate, OrderSpecifier order) method of the QueryDslPredicateExecutor<T> interface.
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.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 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 RepositoryPersonServiceclass. This method is explained with more details in following:
- The predicate is still obtained by calling the lastNameIsLike() method of the PersonPredicatesclass.
- The OrderSpecifier instance is obtained by calling a private orderByLastNameAsc() method ofRepositoryPersonService class.
- The predicate and the OrderSpecifier instance are passed as a parameter to thefindAll(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 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.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.
