Does HQL support CASE WHEN ... ELSE END condition in WHERE statement to include a clause?

Hibernate ORM
1

I’m trying to write an HQL query with a case when condition in where clause. But, it’s failing with Unexpected token exception when executing.

Here’s what I’m trying to do:

select [entity1.attribute1,...]
from [ENTITY1 entity1 join entity1.relatedEntity1 re1 ...]
where [... (case when :bool_param_if_true then entity1.attribute2 is null else 1=1 end) ...]

So, I want a where clause to be part of the query when a variable/param value is true, else it should not be part of the query.

Reason for else 1=1 - when param value is false and the clause is not included, it returns incorrect results. So, just to have a default true condition with this.

The same query, when rewritten in SQL, works perfectly fine from inside the base code. But, I was curious to learn if HQL supports case when in where clause?

If yes, then what is the mistake I’m making here. If no, is there any other approach suggested to achieve this functionality?

Even though converting to SQL does the job for me, but would be interesting to learn how to achieve this through HQL. I spent hours to try out a few workarounds but couldn’t succeed.

Thanks.

2

Hibernate does support that, but it doesn’t support coercion of boolean expressions to predicates. You will have to use the following instead:

select [entity1.attribute1,...]
from [ENTITY1 entity1 join entity1.relatedEntity1 re1 ...]
where [... (case when :bool_param_if_true = true then entity1.attribute2 is null else 1=1 end) ...]
1 Like