Spring Data JPA is designed to make database operations more streamlined and intuitive. One common
requirement in many applications is fetching multiple records based on a list of identifiers. Spring Data
JPA offers an out-of-the-box solution for this through the findAllById
method. Let's dive into how you can
use this method and what you need to consider.
Let's launch Spring Initializr and fill up the following project details:
Project: Maven Project (or Gradle)
Language: Java
Packaging: Jar
Java version: 17
Dependencies: Spring Data JPA, MySQL Driver, and Lombok
Download, extract the project, and import it to your favorite IDE.
Let's open the src/main/resources/application.properties file and add the MySQL configuration properties:
spring.datasource.url=jdbc:mysql://localhost:3306/demo
spring.datasource.username=root
spring.datasource.password=Mysql@123
spring.jpa.hibernate.ddl-auto=update
Make sure that you change the MySQL database username and password as per your MySQL installation on your machine.
The spring.jpa.hibernate.ddl-auto=update line ensures that tables and
columns get automatically created or
updated based on your JPA entities.
Let's create a simple Employee JPA entity that will map to the database
table - employees:
import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String department;
}
We are using below Lombok annotations to reduce the boilerplate code such as getter/setter methods: @Getter: Generates getter methods for the fields of the class. @Setter: Generates setter methods for the fields of the class.
Let's create an EmployeeRepository interface that extends the 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> {
}
To fetch multiple Employee entities by a list of IDs, you can use the findAllById method provided by
JpaRepository:
List<Long> ids = Arrays.asList(1L, 2L, 3L);
List<Employee> employees = employeeRepository.findAllById(ids);
employees.forEach((employee) -> {
System.out.println(employee.getId());
});
Once you run the above code, Spring Data JPA generates the below SQL statement for the findAllById method:
select
e1_0.id,
e1_0.first_name,
e1_0.last_name
from
employees e1_0
where
e1_0.id in(?,?,?)
The findAllById accepts an Iterable of IDs and returns a list of entities
that match these IDs. If some IDs
are not present in the database, they are simply ignored, and their corresponding entities won't be part of
the returned list.
Now, let's write a JUnit test case to test EmployeeRepository query methods:
Test findAllById () Query Method
@Test
void findByDepartmentTest(){
List ids = Arrays.asList(1L, 2L, 3L);
List employees = employeeRepository.findAllById(ids);
employees.forEach((employee) -> {
System.out.println(employee.getId());
});
}
Note that Spring Data JPA ( internally uses Hibernate as JPA provider) generated SQL statement in a console:
Hibernate:
select
e1_0.id,
e1_0.first_name,
e1_0.last_name
from
employees e1_0
where
e1_0.id in(?,?,?)
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.Arrays;
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 findByDepartmentTest(){
List<Long> ids = Arrays.asList(1L, 2L, 3L);
List<Employee> employees = employeeRepository.findAllById(ids);
employees.forEach((employee) -> {
System.out.println(employee.getId());
});
}
@AfterEach
public void clean(){
employeeRepository.deleteAll();
}
}
Performance: The findAllById method can be efficient, especially if
the underlying database supports
fetching
multiple rows by multiple primary key values in a single query. However, be cautious about querying a vast
number of IDs in one go, as it might affect performance.
Order of Results: It's important to note that the order of entities in the returned list might not correspond to the order of IDs in the input list. If maintaining the order is crucial, you'd need to re-order the results in your application logic.
Handling Missing IDs: As mentioned, if some IDs aren't found in the database, they are just skipped. If you need to handle or report missing IDs, you'd need to implement that logic in your application after retrieving the results.
Alternative Approaches: If you need more control over the query, or if you want to combine the ID-based filtering with other query criteria, you can define custom query methods or utilize the @Query annotation to specify a JPQL or SQL query.
Fetching entities by multiple IDs is a breeze with Spring Data JPA's findAllById method. While it's a
powerful and convenient tool, always consider the potential performance implications and ensure your
application handles the results in a way that meets your specific needs. As with any tool, understanding the
underlying behavior is key to using it effectively.