Expand Authors Top

If you have a few years of experience in the Java ecosystem and you’d like to share that with the community, have a look at our Contribution Guidelines.

Expanded Audience – Frontegg – Security (partner)
announcement - icon User management is very complex, when implemented properly. No surprise here.

Not having to roll all of that out manually, but instead integrating a mature, fully-fledged solution - yeah, that makes a lot of sense.
That's basically what Frontegg is - User Management for your application. It's focused on making your app scalable, secure and enjoyable for your users.
From signup to authentication, it supports simple scenarios all the way to complex and custom application logic.

Have a look:

>> Elegant User Management, Tailor-made for B2B SaaS

NPI – JPA Buddy – JPA (partner)
announcement - icon

JPA is huge! It covers nearly every aspect of communication between relational databases and the Java application and is deeply integrated into all major frameworks.

If you're using IntelliJ, JPA Buddy is super helpful. The plugin gently guides you through the subtleties of the most popular JPA implementations, visually reminds you of JPA features, generates code that follows best practices, and integrates intelligent inspections to improve your existing persistence code.

More concretely, it provides powerful tooling to generate Spring Data JPA repositories and methods, Flyway Versioned Migrations, Liquibase Differential Changelogs, DDL and SQL statements, DTO objects, and MapStruct interfaces.

Oh, and it actually generates JPA entities from an existing database and gradually update the data model as the database evolves! Yeah.

>> Become a lot more productive with JPA Buddy

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. For some queries or database servers, especially involving a huge table scan, there could be a performance overhead.

4. Conclusion

In this article, we demonstrated how Spring Data JPA interprets null parameters in query methods and 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.

Persistence bottom

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
Persistence footer banner
Comments are closed on this article!