Spring Data JPA findBy Order By

When working with Spring Data JPA, it's often required not only to fetch records based on certain criteria but also to sort the results in a particular order. Spring Data JPA makes this task intuitive by allowing you to incorporate sorting directly within your method names.

In this post, we will dive into using findBy in combination with OrderBy to both filter and sort results.

Basics of OrderBy

The OrderBy keyword in method names allows you to specify sorting instructions for your queries. The general structure is:

findBy[PropertyName]OrderBy[PropertyName][Asc/Desc]

Create JPA Entity

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@Entity
@Table(name = "employees")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private String email;
}

We are using below Lombok annotations to reduce the boilerplate code such as getter/setter and toString methods:

@Getter: Generates getter methods for the fields of the class.

@Setter: setter methods for the fields of the class.

@ToString: Generates an implementation of the toString method based on the fields of the class.

Spring Data JPA Repository - EmployeeRepository

Let's create an EmployeeRepository interface that extends JpaRepository interface from Spring Data JPA:

import com.springdatajpa.springboot.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}
        

Sorting by Single Property:

To find all employees and sort them by their last names in ascending order:

List<Employee> findAllByOrderByLastNameAsc();

Filtering and Sorting:

To find employees by a specific first name and sort them by their hire date in descending order:

List<Employee> findByFirstNameOrderByHireDateDesc(String firstName);

Sorting by Multiple Properties:

To fetch all employees and sort first by their last name in ascending order, and then by their first name in ascending order:

List<Employee> findAllByOrderByLastNameAscFirstNameAsc();

Testing - EmployeeRepository Query Methods

Let's write the JUnit test cases to all the above query methods:

Test findAllByOrderByLastNameAsc() Query Method:

    @Test
    void findAllByOrderByLastNameAscTest(){
        List employees = employeeRepository.findAllByOrderByLastNameAsc();

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }
    

Run above JUnit test case, Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console:

Hibernate: 
    select
        e1_0.id,
        e1_0.first_name,
        e1_0.hire_date,
        e1_0.last_name 
    from
        employees e1_0 
    order by
        e1_0.last_name

Test findAllByOrderByLastNameAscFirstNameAsc() Query Method:

    @Test
    void findAllByOrderByLastNameAscFirstNameAscTest(){
        List employees = employeeRepository.findAllByOrderByLastNameAscFirstNameAsc();

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }

Run above JUnit test case, Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console:

Hibernate: 
    select
        e1_0.id,
        e1_0.first_name,
        e1_0.hire_date,
        e1_0.last_name 
    from
        employees e1_0 
    order by
        e1_0.last_name,
        e1_0.first_name

Test findByFirstNameOrderByHireDateDesc() Query Method:

    @Test
    void findByFirstNameOrderByHireDateDescTest(){
        List employees = employeeRepository.findByFirstNameOrderByHireDateDesc("Ramesh");

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }

Run above JUnit test case, Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console:

Hibernate: 
    select
        e1_0.id,
        e1_0.first_name,
        e1_0.hire_date,
        e1_0.last_name 
    from
        employees e1_0 
    where
        e1_0.first_name=? 
    order by
        e1_0.hire_date desc

Here is the complete code for your reference:

import com.springdatajpa.springboot.entity.Employee;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;
import java.util.List;

@SpringBootTest
public class EmployeeRepositoryTests {

    @Autowired
    private EmployeeRepository employeeRepository;

    @BeforeEach
    public void setUp() {
        Employee e1 = new Employee();
        e1.setFirstName("Ramesh");
        e1.setLastName("Fadatare");
        e1.setHireDate(new Date());
        employeeRepository.save(e1);
    }

    @Test
    void findAllByOrderByLastNameAscTest(){
        List<Employee> employees = employeeRepository.findAllByOrderByLastNameAsc();

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }

    @Test
    void findAllByOrderByLastNameAscFirstNameAscTest(){
        List<Employee> employees = employeeRepository.findAllByOrderByLastNameAscFirstNameAsc();

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }

    @Test
    void findByFirstNameOrderByHireDateDescTest(){
        List<Employee> employees = employeeRepository.findByFirstNameOrderByHireDateDesc("Ramesh");

        employees.forEach((employee) -> {
            System.out.println(employee.getId());
            System.out.println(employee.getFirstName());
            System.out.println(employee.getLastName());
        });
    }

    @AfterEach
    public void clean(){
        employeeRepository.deleteAll();
    }
}