Is there anyway I can use JPQL's SIZE function for binding parameters in a WHERE clause?

Matheus Cirillo 0
Viewed 15

Is there anyway I can use JPQL's SIZE function for binding variables in a WHERE clause?

Something like this:

and (size(:distributor_ids) = 0 or d.id in (:distributor_ids))

Hibernate throws the following exception:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unexpected expression ? found for collection function size

My query is very big, but this AND clause fairly simple: if the list is empty, then retrieve everything; if the list is not empty, then retrieve all the rows that met d.id IN (:distributor_ids).

Is there anyway to achieve this?

jpql hibernate java
1 Answers

It looks like you want to return everything in case argument is empty or only the items with ids matching the argument.

As it mentioned here this is not possible for now, though you might be interested in suggested workaround.

Is there anyway to achieve this?

Personally I would do it on Java side within service class calling SomeRepository.findAll() for empty collection and SomeRepository.findAllByIds() for the opposite.

Sergey Tsypanov0
Related