JPA Query to handle NULL parameter value

You could add a set of conditions to "ignore" properties with null values or empty strings.

e.g.

+ " AND (ord.quoteNumber = :quoteNumber or :quoteNumber is null or :quoteNumber = '' ")

for nullable params, you can do something as :

@Query(value = "SELECT ord.purchaseOrderNumber,ord.salesOrderNumber,ord.quoteNumber"
        + " FROM Order ord "
        + " WHERE (:poNumber is NULL OR ord.purchaseOrderNumber LIKE :poNumber)"
        + " AND (:soNumberis NULL OR ord.salesOrderNumber LIKE :soNumber)"
        + " AND (:quoteNumber is NULL OR ord.quoteNumber = :quoteNumber) "

Unfortunately the = comparison does not work with null values, for that you have predicate IS NULL, IS NOT NULL. What it means that you need different queries for cases when you quoteNumber is null and is not. You can check for null in your java code and then choose the right query to call. If the oder, quote number cannot be empty strings ("") (which they shouldnt), then you can represent lack of given number as the empty string and always use normal comparison. Just remeber to set your DB schema that the columns cannot be null and the default values for them is "". BTW Your columns names suggest numbers but you are using string comparison I hope it is OK.

If null is valid parameter,

... AND ((:param is null and param is null) or (:param is not null and param = :param)) AND ...

If you want to check the parameter has a null or empty value, you should do like this:

@Query("SELECT t FROM Test t WHERE (t.parameterOne = :parameterOne  OR ((:parameterOne IS NULL) OR (:parameterOne = ''))");

Notice that if the parameterOne is null or empty, then the clause is always true and so doesn't influence the whole WHERE clause.

What you can do this first cast null parameter as text and then COALESCE it with '' empty strings so your query becomes @Query(value = "SELECT ord.purchaseOrderNumber,ord.salesOrderNumber,ord.quoteNumber"

        + " FROM Order ord WHERE COALESCE(ord.purchaseOrderNumber,'') LIKE COALESCE(cast(:poNumber% As TEXT),'') "
        + " AND COALESCE(ord.salesOrderNumber,'') LIKE COALESCE(cast(:soNumber As TEXT),'') "
        + " AND COALESCE(ord.quoteNumber,'') = COALESCE(cast(:quoteNumber As TEXT),'') "

AND (ord.quoteNumber = :quoteNumber or ord.quoteNumber is null")

With java17 and jpa2.6 >, this worked for me.

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.