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

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?

CodePudding user response：

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.

CodePudding user response：

Yeah, it's very simple to do this. Only add the condition to your query if the list is non empty. With JPA Criteria, you can do something like this:

List<Integer> distributorIds = ...
if (!distributorIds.isEmpty()) {
    criteriaQuery.where(root.get("id").in(distributorIds));
}

In a bigger query, you can collect individual Predicate objects in a list and finally set a conjunction of them, created via CriteriaBuilder.and() as where clause predicate.