Passing a null parameter to a native query using @Query JPA annotation

Question

In a Spring Boot application, I have a SQL query that is executed on a postgresql server as follows :

@Query(value = "select count(*) from servers where brand= coalesce(?1, brand) " +
        "and flavour= coalesce(?2, flavour) ; ",
        nativeQuery = true)
Integer icecreamStockCount(String country, String category);

However,

I get the following error when I execute the method :

ERROR: COALESCE types bytea and character varying in PostgreSQL

How do I pass String value = null to the query?

**NOTE : ** I found that my question varied from JPA Query to handle NULL parameter value

I don't know if that's possible or not. I'd try creating separate queries if certain inputs are null. — Glen Pierce, Mar 15 '17 at 14:47
you can just change coalesce to `case when then else end`, like `"select count(*) from servers where brand = (case when ?1 = 'undefined' then brand else ?1::bytea) "` — Vao Tsun, Mar 15 '17 at 15:01

Jodiug · Answer 1 · 2020-06-11T08:57:39.750

When I encounted this error, I ended up using a combination of OR and CAST to solve the issue.

SELECT COUNT(*)
FROM servers
WHERE (?1 IS NULL OR brand = CAST(?1 AS CHARACTER VARYING))
AND (?2 IS NULL OR flavour = CAST(?2 AS CHARACTER VARYING))

This works even if ?1, ?2, brand and flavor are all nullable fields.

Note that passing null for ?1 means "all servers regardless of brand" rather than "all servers without a brand". For the latter, you could use IS DISTINCT FROM as follows.

SELECT COUNT(*)
FROM servers
WHERE (CAST(?1 AS CHARACTER VARYING) IS NOT DISTINCT FROM brand)
AND (CAST(?2 AS CHARACTER VARYING) IS NOT DISTINCT FROM flavour)

Finally, certain parameter types such as Boolean cannot be cast in SQL from BYTEA to BOOLEAN, for those cases you need a double cast:

SELECT COUNT(*)
FROM servers
WHERE (?1 IS NULL OR is_black = CAST(CAST(?1 AS CHARACTER VARYING) AS BOOLEAN))

In my eyes this is a problem in Hibernate which could be solved by passing Java null parameters as plain SQL NULLs rather than interpreting null as a value of type BYTEA.

score 6 · Answer 2 · answered Mar 15 '17 at 16:18

6

You need not coalesce, try this

@Query("select count(*) from servers where (brand = ?1 or ?1 is null)" +
        " and (flavour = ?2 or ?2 is null)")
Integer icecreamStockCount(String country, String category);

answered Mar 15 '17 at 16:18

Andriy Slobodyanyk

1,965
14
15

@Jodiug, do you check it? This is an issue that TS came with. – Andriy Slobodyanyk Jun 09 '20 at 15:12
Deleted my comment. It seems from my small experiments that `OR` can be performant as long as only a single clause uses data from the database. So `?1 = 3 OR x = ?1` can be indexed but `?1 = x OR x = 22` cannot be indexed. So your answer works according to that rule. My mistake! – Jodiug Jun 11 '20 at 08:41

Guilherme Alencar · Answer 3 · 2020-06-08T13:40:19.483

If you really need to use native query, there is a problem because it's an improvement not implemented yet, see hibernate. If you don't need to use native you can do (where ?1 is null or field like ?1). Assuming you do need native, you may treat the String before by setting this empty and then calling the repository and this one would be like:

@Query(value = "select count(*) from servers where (?1 like '' or brand like ?1) " +
    "and (?2 like '' or flavour like ?2)",
    nativeQuery = true)
Integer icecreamStockCount(String country, String category);

There is always javax.persistence.EntityManager bean as option for native query situations and I recommend it instead of previous approach. Here you can append to your query the way you want, as follows:

String queryString = "select count(*) from servers ";
if (!isNull(country)) queryString += "where brand like :country";
Query query = entityManager.createNativeQuery(queryString);
if (!isNull(country)) query.setParameter("country", country);
return query.getResultList();

Observations:

Newer versions have improved this '+' concatenation Strings. But you can build your queryString the way you want with StringBuilder or String Format, it doesn't matter.
Be careful with SQL injection, the setParameter method avoid this kind of problem, for more information see this Sql Injection Baeldung

score 1 · Answer 4 · answered Dec 16 '21 at 23:23

So this is not the exact answer to the question above, but I was facing a similar issue, I figured I would add it here, for those that come across this question.

I was using a native query, in my case, it was not a singular value like above, but I was passing in a list to match this part of the query:

WHERE (cm.first_name in (:firstNames) OR :firstNames is NULL)

I was getting the bytea error, in the end I was able to send an empty list.

(null == entity.getFirstName()? Collections.emptyList() : entity.getFirstName())

In this case, sending the empty list to the resolver worked, where as null did not.

hope this saves you some time.

Rich · Answer 5 · 2017-12-18T10:24:06.230

0

null parameters are not allowed before Hibernate 5.0.2. See https://hibernate.atlassian.net/browse/HHH-9165 and the replies to https://www.postgresql.org/message-id/6ekbd7dm4d6su5b9i4hsf92ibv4j76n51f@4ax.com

edited Dec 18 '17 at 10:24

answered Dec 18 '17 at 10:16

Rich

15,048
2
66
119

Passing a null parameter to a native query using @Query JPA annotation

5 Answers5