JPA Query to handle NULL parameter value

Question

I am new to JPA here is one of my query and i have couple of parameters as part of query and any parameter can be null value

@Query(value = "SELECT ord.purchaseOrderNumber,ord.salesOrderNumber,ord.quoteNumber"

            + " FROM Order ord WHERE ord.purchaseOrderNumber LIKE :poNumber%  "
            + " AND ord.salesOrderNumber LIKE :soNumber "
            + " AND ord.quoteNumber = :quoteNumber "

example in the above if my input parameter :quoteNumber is NULL then i shouldn't filter by ord.quoteNumber = NULL, so how do avoid this

Another approach using JpaRepository Specification is provided [https://stackoverflow.com/a/61948111/3440284] — ganaraj, May 22 '20 at 04:58
Related: https://stackoverflow.com/questions/28874135/dynamic-spring-data-jpa-repository-query-with-arbitrary-and-clauses — Jens Schauder, Apr 01 '21 at 06:36

ccamcmxc · Answer 1 · 2016-07-28T14:07:49.437

23

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 = '' ")

edited Jul 28 '16 at 14:07

answered Jul 22 '16 at 18:17

ccamcmxc

491
4
12

score 4 · Answer 2 · answered Sep 27 '19 at 17:22

4

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) "

answered Sep 27 '19 at 17:22

Ibrahima Timera

598
4
7

I tried this - without "LIKE" but with "=" instead and it always returns an empy array. – Delice Dec 30 '21 at 12:12

score 2 · Answer 3 · answered Feb 17 '15 at 12:54

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.

it's strange that this works in native sql when I pgAdmin, but native sql in JPA doesn't support this, for example: select * from table where (:queryParam IS NULL or :queryParam=table.column1). — Zhenya, Jun 20 '16 at 13:41

score 1 · Answer 4 · answered Nov 25 '21 at 09:23

1

If null is valid parameter,

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

answered Nov 25 '21 at 09:23

sura2k

7,365
13
61
80

score 0 · Answer 5 · answered Nov 20 '21 at 09:30

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.

score 0 · Answer 6 · answered Mar 10 '22 at 08:34

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),'') "

score 0 · Answer 7 · answered Apr 01 '22 at 13:49

0

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

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

answered Apr 01 '22 at 13:49

Sai Ram Nadigoti

1
1

score -1 · Answer 8 · edited Aug 26 '20 at 17:32

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.

This answer looks copy-pasted from [https://www.baeldung.com/spring-data-jpa-null-parameters](https://www.baeldung.com/spring-data-jpa-null-parameters). Please give credits when you reuse someone else's work... — fbastien, May 03 '21 at 17:15
thanks @fbastien for such an insightful comment, yes it is from baeldung shared with an intention to help someone looking for solution to the question posted — Arnav Karforma, May 20 '21 at 20:11

score -4 · Answer 9 · answered Jul 15 '15 at 18:42

-4

You can use something like this :quoteNumber = -999 AND ord.quoteNumber = :quoteNumber

and pass -999 to the query when you want to pass null.

I'm trying to see if you can default it to -999 using the @parma

answered Jul 15 '15 at 18:42

user5120738

1

JPA Query to handle NULL parameter value

9 Answers9

Linked

Related