I'm using Hibernate to get data from Postgresql DB, but always getting this error when trying to pass EMPTY or NULL as list of Long values to query:
@Repository
public interface ProductRepository extends JpaRepository<Category, Long> {
@Query(nativeQuery = true,
value = "SELECT * FROM Products p " +
"WHERE (COALESCE(:#{#param.colors}) IS NULL OR p.color IN (:#{#param.colors}))")
List<Product> findFiltered(@Param("param") ProductFilterParams params);
}
(note: I use native query because of complex request)
ProductFilterParams looks like:
@Data
public class ProductFilterParams {
private List<Long> colors;
...other product filters...
}
So the idea is to filter products by color (which works good) or show all of them, if there is no color filter (and here I get this error).
When I pass null as colors logs is:
binding parameter [1] as [LONGVARCHAR] - [null]
ERROR: operator does not exist: bigint = character varying
or when I try to pass empty list:
*** NO BINDING LOG AT ALL ***
ERROR: syntax error at or near ")"
I have no idea why this error occure and how to fix it. I tried some googled answers from web/stackoverflow, but all of them for HQL and can't fix it for native query.
Actually, hibernate documentation says:
IN
predicates performs a check that a particular value is in a list of values. Its syntax is:
in_expression ::= single_valued_expression [NOT] IN single_valued_list
single_valued_list ::= constructor_expression | (subquery) | collection_valued_input_parameter
constructor_expression ::= (expression[, expression]*)
The list of values can come from a number of different sources. In the
constructor_expression
andcollection_valued_input_parameter
, the list of values must not be empty; it must contain at least one value.
But, as it is explained here it was changed in hibernate 5.4.10. But as you use hibernate 5.3.7.final you should move checking for collection emptiness outside your query.
And by the way, there is no sense to use COALESCE function with one argument.