Query using “CASE WHEN” statement in WHERE causes QuerySyntaxException: unexpected AST

hqljpajpqlspring-dataspring-data-jpa

I'm trying to make a query using Spring Data, but I cannot make it work:

@Query(SELECT t FROM Thing t WHERE name LIKE :name AND CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <= :minVal) END AND CASE WHEN (:maxVal <= 0) THEN TRUE ELSE (val >= :maxVal) END)
Page<Thing> getThings(@Param("name") String name, @Param("maxVal") int maxVal, @Param("minVal") minVal);

StackTrace:

Caused by: java.lang.IllegalArgumentException:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST
node: CASE near line 1, column 49 [SELECT t FROM Thing t WHERE name
LIKE :name AND CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <=
:minVal) END AND CASE WHEN (:maxVal <= 0) THEN TRUE ELSE (val >=
:maxVal) END] at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331)
at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source) at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606) at
org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:334)
at com.sun.proxy.$Proxy83.createQuery(Unknown Source) at
org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:78)
… 207 more

Caused by:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST
node: CASE near line 1, column 49 [SELECT t FROM Thing t WHERE name
LIKE :name AND CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <=
:minVal) END AND CASE WHEN (:maxVal <= 0) THEN TRUE ELSE (val >=
:maxVal) END] at
org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
at
org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
at
org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284)
at
org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
at
org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at
org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:126)
at
org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:88)
at
org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
at
org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at
org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at
org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)

I'm using this because I want to make a longer query using five filters at least, and I want to simplify the effort of doing the filter combinations making differents querys.

Don't know if there is a different (and better) way to do what I want, glad to ear it if it is.

Thank you.

EDIT: Using native query works fine, but isn't compatible with pagination yet…

Best Answer

It looks like Hibernate cannot evaluate the result of a CASE expression when it returns a boolean literal directly. A workaround is to make the CASE expression part of another expression, e.g. by comparing it to another boolean literal.

So instead of:

... AND CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <= :minVal) END

Try:

... AND (CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <= :minVal) END) = TRUE

But looking at that expression, wouldn't it be simpler to just do:

... AND (:minVal <= 0 OR val <= :minVal)

Is it not equivalent?

Related Topic