CODEWITHSUNDEEP
jpa
gnanesh
gnanesh

Reputation: 361

JPA Query to handle NULL parameter value

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

Upvotes: 23

Views: 70181

Answers (9)

Sai Ram Nadigoti
Sai Ram Nadigoti

Reputation: 1

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

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

Upvotes: 0

Mohak Gupta
Mohak Gupta

Reputation: 1

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

Upvotes: 0

sura2k
sura2k

Reputation: 7517

If null is valid parameter,

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

Upvotes: 2

Payam Soudachi
Payam Soudachi

Reputation: 489

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.

Upvotes: 0

Arnav Karforma
Arnav Karforma

Reputation: 110

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.

Upvotes: -2

Ibrahima Timera
Ibrahima Timera

Reputation: 707

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

Upvotes: 4

ccamcmxc
ccamcmxc

Reputation: 511

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

Upvotes: 25

user5120738
user5120738

Reputation: 1

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

Upvotes: -6

Zielu
Zielu

Reputation: 8562

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.

Upvotes: 2

Related Questions