Spring Data JPA existsBy Multiple Columns

Spring Data JPA is known for reducing boilerplate code, especially when it comes to CRUD operations and query methods. One very useful feature is the existsBy derived query method, which can determine if a particular record exists based on certain criteria, returning a boolean value. Combining this with multiple columns allows developers to efficiently check for the existence of records based on a combination of attributes. 

In this post, we'll explore how to utilize the existsBy method with multiple columns. 

Why existsBy? 

Before diving into the main topic, let's understand why existsBy is so useful: 

Performance: Checking for the existence without loading the entire entity is often faster, especially for large datasets. 

Simplicity: Directly returning a boolean for the existence check is more straightforward and can make the code cleaner.

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

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

If you want to check if an employee exists with a specific first and last name, you can do:

  boolean existsByFirstNameAndLastName(String firstName, String lastName);

This method will return true if an employee with the specified first and last names exists, and false otherwise. 

Similarly, if you want to verify the existence based on an email and last name:

 boolean existsByEmailAndLastName(String email, String lastName);

Spring Data JPA also allows you to combine And & Or in the method names:

boolean existsByFirstNameAndLastNameOrEmail(String firstName, String lastName, String email);

This method will check if an employee exists with the given first and last names, or with the given email.

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, Long> {

    boolean existsByFirstNameAndLastName(String firstName, String lastName);

    boolean existsByEmailAndLastName(String email, String lastName);

    boolean existsByFirstNameAndLastNameOrEmail(String firstName, String lastName, String email);

}

Testing - EmployeeRepository 

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

Test existsByFirstNameAndLastName() query method:

    @Test
    void existsByFirstNameAndLastNameTest(){
        boolean status = employeeRepository.existsByFirstNameAndLastName("Ramesh", "Fadatare");

        System.out.println(status);
    }

Output:

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

Test existsByEmailAndLastName() query method:

    @Test
    void existsByEmailAndLastNameTest(){
        boolean status = employeeRepository.existsByEmailAndLastName("[email protected]", "Fadatare");

        System.out.println(status);
    }

Output:

Hibernate: 
    select
        e1_0.id 
    from
        employees e1_0 
    where
        e1_0.email=? 
        and e1_0.last_name=? limit ?

Test existsByFirstNameAndLastNameOrEmail() query method:

    @Test
    void existsByFirstNameAndLastNameOrEmailTest(){
        boolean status = employeeRepository
                .existsByFirstNameAndLastNameOrEmail("Ramesh",
                        "Fadatare",
                        "[email protected]");

        System.out.println(status);
    }

Output:

Hibernate: 
    select
        e1_0.id 
    from
        employees e1_0 
    where
        e1_0.first_name=? 
        and e1_0.last_name=? 
        or e1_0.email=? limit ?

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.setEmail("[email protected]");
        employeeRepository.save(e1);
    }

    @Test
    void existsByFirstNameAndLastNameTest(){
        boolean status = employeeRepository.existsByFirstNameAndLastName("Ramesh", "Fadatare");

        System.out.println(status);
    }

    @Test
    void existsByEmailAndLastNameTest(){
        boolean status = employeeRepository.existsByEmailAndLastName("[email protected]", "Fadatare");

        System.out.println(status);
    }

    @Test
    void existsByFirstNameAndLastNameOrEmailTest(){
        boolean status = employeeRepository
                .existsByFirstNameAndLastNameOrEmail("Ramesh",
                        "Fadatare",
                        "[email protected]");

        System.out.println(status);
    }

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

Conclusion

The existsBy method in Spring Data JPA provides a succinct way to check for the existence of records based on multiple conditions. When combined with multiple columns, it enables developers to efficiently ensure uniqueness or make decisions based on the existence of particular records. As always, use these tools wisely, keeping readability and maintainability in mind.