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

Answer 1

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

Answer 2

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.