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: Generates 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<Employee> 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_nameTest findAllByOrderByLastNameAscFirstNameAsc() Query Method:
@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());
});
}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_nameTest findByFirstNameOrderByHireDateDesc() Query Method:
@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());
});
}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 descHere 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();
}
}
Comments
Post a Comment
Leave Comment