使用"CASE WHEN"进行查询. WHERE中的语句导致QuerySyntaxException:意外的AST [英] Query using "CASE WHEN" statement in WHERE causes QuerySyntaxException: unexpected AST
问题描述
我正在尝试使用Spring Data进行查询,但是我无法使其工作:
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:
StackTrace:
由以下原因引起:java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException:意外的AST 节点:第1行第49列附近的CASE [SELECT t FROM Thing t WHERE name 像:name和CASE WHEN(:minVal< = 0)然后是true(否则val(< = :minVal)结束并以大小写形式出现(:maxVal< = 0)然后为真(SEAL)(val> = :maxVal)END]在 org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750) 在 org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) 在 org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683) 在 org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331) 在sun.reflect.GeneratedMethodAccessor40.invoke(未知源)处 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 在java.lang.reflect.Method.invoke(Method.java:606)在 org.springframework.orm.jpa.ExtendedEntityManagerCreator $ ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:334) 在com.sun.proxy.$ Proxy83.createQuery(Unknown Source)处 org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:78) ...另外207个
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
起因: org.hibernate.hql.internal.ast.QuerySyntaxException:意外的AST 节点:第1行第49列附近的CASE [SELECT t FROM Thing t WHERE name 像:name和CASE WHEN(:minVal< = 0)然后是true(否则val(< = :minVal)结束并以大小写形式出现(:maxVal< = 0)然后为真(SEAL)(val> = :maxVal)END]在 org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91) 在 org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) 在 org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:126) 在 org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:88) 在 org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) 在 org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) 在 org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) 在 org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) 在 org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
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.
谢谢.
使用本机查询可以正常工作,但尚不支持分页...
Using native query works fine, but isn't compatible with pagination yet...
推荐答案
当Hibernate直接返回布尔文字时,它似乎无法评估CASE表达式的结果.一种解决方法是使CASE表达式成为另一个表达式的一部分,例如通过将其与另一个布尔文字进行比较.
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.
所以代替:
... AND CASE WHEN (:minVal <= 0) THEN TRUE ELSE (val <= :minVal) END
尝试:
... 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)
不是等效的吗?
这篇关于使用"CASE WHEN"进行查询. WHERE中的语句导致QuerySyntaxException:意外的AST的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!