Spring Data JPA and Null Parameters

1. Overview

In this article, we’ll show the ways of handling null parameters in Spring Data JPA.

In some cases, when we search for records by parameters we want to find rows with null as the field value. Other times, we may want to ignore a null and skip that field in our query.

Below we’ll show how to implement each of these.

2. Quick Example

Let’s say we have a Customer entity:

@Entity
public class Customer {

    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String email;

    public Customer(String name, String email) {
        this.name = name;
        this.email = email;
    }

    // getters/setters

}

Also, we have a JPA repository:

public interface CustomerRepository extends JpaRepository<Customer, Long> { 

   // method1
   // method2
}

We want to search for customers by name and email.

For this purpose, we’ll write two methods that handle null parameters differently.

3. Ways to Handle Null Parameters

Firstly, we’ll create a method that interprets null values of the parameters as IS NULL, and then we’ll create a method that ignores null parameters and excludes them from the WHERE clause.

3.1. IS NULL Query

The first method is very simple to create because null parameters in the query methods are interpreted as IS NULL by default.

Let’s create the method:

List<Customer> findByNameAndEmail(String name, String email);

Now if we pass a null email, the generated JPQL will include the IS NULL condition:

customer0_.email is null

To demonstrate this let’s create a test.

First, we’ll add some customers to the repository:

@Before
public void before() {
    entityManager.persist(new Customer("A", "A@example.com"));
    entityManager.persist(new Customer("D", null));
    entityManager.persist(new Customer("D", "D@example.com"));
}

Now let’s pass “D” as the value of the name parameter and null as the value of the email parameter to our query method. We can see that exactly one customer will be found:

List<Customer> customers = repository.findByNameAndEmail("D", null);

assertEquals(1, customers.size());

Customer actual = customers.get(0);

assertEquals(null, actual.getEmail());
assertEquals("D", actual.getName());

3.2. Avoid null Parameter With Alternative Methods

Sometimes we want to ignore some parameters and not include their corresponding fields in the WHERE clause.

We can add more query methods to our repository. For example, to ignore email we can add a method that only accepts name:

List<Customer> findByName(String name);

But such a way of ignoring one of our columns scaled poorly as the number increases, as we would have to add many methods to achieve all the combinations.

3.3. Ignoring null Parameters Using the @Query Annotation

We can avoid creating additional methods by using the @Query annotation and adding a small complication to the JPQL statement:

@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null"
  + " or c.email = :email)")
List<Customer> findCustomerByNameAndEmail(@Param("name") String name, @Param("email") String email);

Notice that if the: email parameter is null:

:email is null or s.email = :email

Then the clause is always true and so doesn’t influence the whole WHERE clause.

Let’s make sure that this works:

List<Customer> customers = repository.findCustomerByNameAndEmail("D", null);

assertEquals(2, customers.size());

We found two customers whose name is “D” ignoring their emails.

The generated JPQL WHERE clause looks like this:

where (? is null or customer0_.name=?) and (? is null or customer0_.email=?)

With this method, we are putting trust in the database server to recognize the clause regarding our query parameter being null and optimize the execution plan of the query so that it doesn’t have a significant performance overhead. For some queries or database servers, especially involving a huge table scan, there could be a performance overhead.

4. Conclusion

We’ve demonstrated how Spring Data JPA interprets null parameters in query methods and shown how to change the default behavior.

Perhaps in the future, we’ll be able to specify how to interpret null parameters using the @NullMeans annotation.  Notice that it’s a proposed feature at this time and is still under consideration.

To sum up, there are two main ways to interpret null parameters, and they would both be provided by the proposed @NullMeans annotation:

• IS (is null) – the default option demonstrated in section 3.1.
• IGNORED (exclude a null parameter from the WHERE clause) – achieved either by extra query methods (section 3.2.) or by using a workaround (section 3.3.)

As usual, the complete source code is available over on GitHub.