Spring Data JPA findBy Multiple IDs

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.

1. Set up a Spring Boot project

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.

2. Configure the MySQL database 

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.

3. Create JPA Entity - Employee

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. 

3. Create Spring Data JPA Repository - EmployeeRepository

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> {
}

Fetching Multiple Entities by IDs 

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.

4. Testing - EmployeeRepository Query Methods

Now, let's write a JUnit test case to test EmployeeRepository query methods:

Test findAllById () Query Method

    @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());
        });
    }

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();
    }
}

Points to Consider

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.

Conclusion

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.

Related Spring Data JPA Query Method Tutorials

• Spring Data JPA Query Method - Multiple Fields
• Spring Data JPA Query Method - Distinct
• Spring Data JPA Query Method - Greater Than
• Spring Data JPA Query Method - Less Than
• Spring Data JPA Query Method - Between
• Spring Data JPA Query Method - Like
• Spring Data JPA Query Method - IN Clause