1. Overview
In this tutorial, we’ll show how to handle 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 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
First, we’ll create a method that interprets null values of the parameters as IS NULL. 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", "[email protected]"));
entityManager.persist(new Customer("D", null));
entityManager.persist(new Customer("D", "[email protected]"));
}
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 this way of ignoring one of our columns scales poorly as the number increases since 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, the clause is always true and so doesn’t influence the whole WHERE clause:
:email is null or s.email = :email
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. There could be a performance overhead for some queries or database servers, especially involving a huge table scan.
Please note that the above methods for querying null parameters also work if the parameter type is of type – UUID. We can find examples in our Github repo.
4. Conclusion
In this article, we demonstrated how Spring Data JPA interprets null parameters in query methods and how to change the default behaviour.
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 on GitHub.