Spring data jpa findall order by

1. Oveview

In this article, we will discuss incorporating the order by with findAll repository query in Spring Data JPA. Often you want to sort the query result-set either in ascending or descending order. Here, we will discuss the various sorting possibilities available in Spring Data JPA.

2. Spring Data JPA

2.1. Entity class

First, let’s create an entity class that represents the Customer table in the database. We specified the primary key of the entity class by annotating the field id with @Id.

import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.data.annotation.Id;

import javax.persistence.Entity;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Customer {

    @Id
    private String id;
    private String name;
    private int new_user;
}

Here, we have annotated the Entity class with annotations @AllArgsConstructor and @Data from the Lombok library to generate constructors and getter/setter methods.

However, you can also manually create constructors and getters/setters.

3. JpaRepository class

Now, let’s create a repository class that implements the JpaRepository interface. The annotation @Repository is a specialization of @Component annotation which is used to show that the implementing class provides the mechanism for storage, retrieval, update, delete and search operation on DB objects.

The JpaRepository extends PagingAndSortingRepository which in turn extends CrudRepository. Because of this inheritance, JpaRepository will have all the functions of CrudRepository and PagingAndSortingRepository.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    List<Customer> findAllByName(String name, Pageable pageable);
}

4. Spring data jpa findall order by

Now, let’s discuss the options available to sort the data returned by findAll method.

4.1. Spring data jpa order by with a Sort parameter

The PagingAndSortingRepository interface provides both pagination and sorting functionalities. See this article to know more. Since JpaRepository extends the PagingAndSortingRepository interface, you can perform sorting easily by following the below steps:

1. Create an instance of the Sort class using any of the
2. Pass the Sort instance as a parameter to the repository query methods

You had to pass this Sort instance as a parameter to your repository methods such as findAll.

Several overloaded factory methods are available to create a Sort instance:

public static Sort by(Direction direction, String... properties)

public static Sort by(Order... orders)

public static Sort by(List<Order> orders)

The Direction enum contains the enum constants ASC or DESC.

For example, the following findAll method takes the Sort instance as an argument. This Sort instance requests sorting the results using the name property in descending order.

 public List<Customer> getCustomersByDesc() {
        return customerRepository.findAll(Sort.by(Sort.Order.desc("name")));
 }

Similarly, the following code also provides the same output. It uses the by(Direction, Property) static method to create the Sort instance.

public List<Customer> getCustomersByDesc2() {
    return customerRepository.findAll(Sort.by(Sort.Direction.DESC, "name"));
}

If you execute the above codes, it returns the following result. As you can see, the results are sorted in descending order of name property.

[{"id":"12145","name":"Siv","new_user":1},
{"id":"56782","name":"Praj","new_user":0},
{"id":"12435","name":"Kevin","new_user":1},
{"id":"46782","name":"Mike","new_user":0}]

You can also implement sorting along with pagination. See this article to know more.

customerRepository.findAll(PageRequest.of(0, 10, Sort.by(Sort.Direction.DESC, "name")));

4.2. Sort with Order By method keyword

Alternatively, use the Spring Data derived methods that generate queries using the method name and signature. You can sort the query results by including the keyword orderBy in the repository method name followed by the property name(s) and direction (Asc or Desc).

For example, when you invoke the following derived method findAllByOrderByNameDesc, it sorts the result in the descending order of the property name.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {
    List<Customer> findAllByOrderByNameDesc();
}

5. Conclusion

To sum up, we have learned to incorporate order by with findAll repository query in Spring Data JPA. You can find the code samples of this article in our GitHub repository.