Spring Data JPA findBy Multiple Columns

Spring Data JPA is all about simplifying the way we interact with databases. One of the areas where it shines the most is in its ability to generate SQL queries from method names. These methods can be based on simple entity attributes or even on multiple columns. In this post, we will explore the findBy methods using multiple columns.

Before we dive into multiple columns, let's get a quick understanding of the basics. Spring Data JPA analyzes repository method names and creates SQL queries accordingly. If your entity has an attribute named name and your repository has a method findByName(String name), Spring Data JPA would generate SQL to find records by the name column.

Spring Data JPA findBy Multiple Columns

Real-world scenarios often require querying based on multiple attributes.

JPA Entity - Employee

Let's create an Employee 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 department;
}

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

Using And for Multiple Conditions

If you want to find an employee by both first name and last name, you can create a method in your repository like:

Employee findByFirstNameAndLastName(String firstName, String lastName);

This method translates into a SQL query that fetches employees where both the first name and last name match the given parameters.

Using Or for Alternative Conditions

Similarly, if you want to fetch employees by either their first name or last name:

List<Employee> findByFirstNameOrLastName(String firstName, String lastName);

This will retrieve employees where either the first name or the last name matches the given parameters.

Combining And & Or

It's possible to combine both:

List<Employee> findByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department);

This will fetch employees where either the first name matches or the last name matches and belongs to a specific department.

Here is the complete code for EmployeeRepository:

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

import java.util.List;

public interface EmployeeRepository extends JpaRepository {

    Employee findByFirstNameAndLastName(String firstName, String lastName);

    List<Employee> findByFirstNameOrLastName(String firstName, String lastName);

    List<Employee> findByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department);

}

Testing - EmployeeRepository

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

Test findByFirstNameAndLastName() query method:

    @Test
    void findByFirstNameAndLastNameTest(){
        Employee employee = employeeRepository.findByFirstNameAndLastName("Ramesh", "Fadatare");

        System.out.println(employee.toString());
    }

Output:

Hibernate: 
    select
        e1_0.id,
        e1_0.department,
        e1_0.first_name,
        e1_0.last_name 
    from
        employees e1_0 
    where
        e1_0.first_name=? 
        and e1_0.last_name=?

Test findByFirstNameOrLastName() query method:

Hibernate: 
    select
        e1_0.id,
        e1_0.department,
        e1_0.first_name,
        e1_0.last_name 
    from
        employees e1_0 
    where
        e1_0.first_name=? 
        or e1_0.last_name=?

Test findByFirstNameOrLastNameAndDepartment() query method:

Hibernate: 
    select
        e1_0.id,
        e1_0.department,
        e1_0.first_name,
        e1_0.last_name 
    from
        employees e1_0 
    where
        e1_0.first_name=? 
        or e1_0.last_name=? 
        and e1_0.department=?

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.List;

@SpringBootTest
public class EmployeeRepositoryTests {

    @Autowired
    private EmployeeRepository employeeRepository;

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

        Employee e2 = new Employee();
        e2.setFirstName("John");
        e2.setLastName("Cruise");
        e2.setDepartment("IT");
        employeeRepository.save(e2);
    }

    @Test
    void findByFirstNameAndLastNameTest(){
        Employee employee = employeeRepository.findByFirstNameAndLastName("Ramesh", "Fadatare");

        System.out.println(employee.toString());
    }

    @Test
    void findByFirstNameOrLastNameTest(){
        List employees = employeeRepository.findByFirstNameOrLastName("Ramesh", "Fadatare");
        employees.forEach((employee) -> {
            System.out.println(employee.toString());
        });
    }

    @Test
    void findByFirstNameOrLastNameAndDepartmentTest(){
        List employees = employeeRepository
                .findByFirstNameOrLastNameAndDepartment("Ramesh",
                "Fadatare",
                "IT");

        employees.forEach((employee) -> {
            System.out.println(employee.toString());
        });
    }

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

Conclusion

Spring Data JPA's ability to work with multiple columns using method names significantly reduces boilerplate code, making the development process faster and cleaner. Whether you're searching using a single column, combining multiple columns, or setting up more complex conditions, the framework provides intuitive ways to get it done. Always be aware of the actual queries being generated, especially in more intricate scenarios, to ensure both the correctness and performance of your operations. Happy querying!