java - org.hibernate.hql.ast.HqlParser - processEqualityExpression() ：没有要处理的表达式

Question

执行以下 HQL 查询时出现异常

select f from StudentFee f inner join f.studentFeeDetails d left outer join d.feeReceiptDetails r inner join f.student s inner join s.studyHistoryList h inner join h.academicYear a where a.id in (:academicYearId)  and  (case when year(a.startDate)=year(a.endDate) then f.month >= month(a.startDate) and f.month <= month(a.endDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) end or case when year(a.endDate) >= year(a.startDate) then f.month <= month(a.endDate) and f.month>= month(a.startDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) end ) group by f.id having (sum(r.amountPaid) < sum(d.feeAmount) or sum(r.amountPaid) = null) and str_to_date(concat(:feeDueDay,'-',f.month,'-',f.year), '%d-%m-%Y') < CURRENT_DATE

例外：

37931 [DefaultQuartzScheduler_Worker-1] ERROR org.hibernate.hql.PARSER - line 1:319: unexpected token: >=
37932 [DefaultQuartzScheduler_Worker-1] WARN org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!
37933 [DefaultQuartzScheduler_Worker-1] WARN org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!
37938 [DefaultQuartzScheduler_Worker-1] ERROR org.hibernate.hql.PARSER - line 1:435: unexpected token: end
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: >= near line 1, column 319 [select f from com.alcandor.school.model.businessobject.StudentFee f inner join f.studentFeeDetails d left outer join d.feeReceiptDetails r inner join f.student s inner join s.studyHistoryList h inner join h.academicYear a where a.id in (:academicYearId)  and  (case when year(a.startDate)=year(a.endDate) then f.month >= month(a.startDate) and f.month <= month(a.endDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) end or case when year(a.endDate) >= year(a.startDate) then f.month <= month(a.endDate) and f.month>= month(a.startDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) end ) group by f.id having (sum(r.amountPaid) < sum(d.feeAmount) or sum(r.amountPaid) = null) and str_to_date(concat(:feeDueDay,'-',f.month,'-',f.year), '%d-%m-%Y') < CURRENT_DATE]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
    at com.alcandor.school.model.dao.impl.FeeDAOImpl.calculateFines(FeeDAOImpl.java:128)
    at com.alcandor.school.job.FeeCalculationJobBean.execute(FeeCalculationJobBean.java:38)
    at com.alcandor.school.job.DelegatingJobBean.executeInternal(DelegatingJobBean.java:36)
    at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)

谢谢

标签： javahibernatehql

Answer 1

您正在使用Searched CASE 表达式，但您的语法错误。

正确的语法是：

CASE [ WHEN {test_conditional} THEN {match_result} ]* ELSE {miss_result} END

如果您将查询更改为我在此处提供的内容，则至少会对其进行解析而不会引发异常：

import antlr.RecognitionException;
import antlr.TokenStreamException;
import org.hibernate.hql.internal.ast.HqlParser;

public class Main {
    public static void main(String[] args) throws TokenStreamException, RecognitionException {
        HqlParser parser = HqlParser.getInstance("select f from StudentFee f inner join f.studentFeeDetails d left outer join d.feeReceiptDetails r inner join f.student s inner join s.studyHistoryList h inner join h.academicYear a where a.id in (:academicYearId) and ( case when year(a.startDate) = year(a.endDate) then case when f.month >= month(a.startDate) and f.month <= month(a.endDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) then TRUE else FALSE end end or case when year(a.endDate) >= year(a.startDate) and f.month <= month(a.endDate) and f.month >= month(a.startDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate) then TRUE else FALSE end ) group by f.id having ( sum(r.amountPaid) < sum(d.feeAmount) or sum(r.amountPaid) = null ) and str_to_date( concat(:feeDueDay,'-',f.month,'-',f.year), '%d-%m-%Y' ) < CURRENT_DATE");
        parser.statement();
    }
}

但是，原始案例表达式中的逻辑似乎不正确：

  case when year(a.startDate) = year(a.endDate)
  then
    f.month >= month(a.startDate)
    and
    f.month <= month(a.endDate)
    and
    f.year >= year(a.startDate)
    and
    f.year <= year(a.endDate)
  end
  or
  case when year(a.endDate) >= year(a.startDate)
  then
    f.month <= month(a.endDate)
    and
    f.month>= month(a.startDate)
    and
    f.year >= year(a.startDate)
    and
    f.year <= year(a.endDate)
  end

你的意思是这个吗？

  case when year(a.startDate) = year(a.endDate)
  then
    case when f.month >= month(a.startDate) and f.month <= month(a.endDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate)
      then
      TRUE
    else
      FALSE
    end
  end
  or
  case when year(a.endDate) >= year(a.startDate) and f.month <= month(a.endDate) and f.month >= month(a.startDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate)
    then
    TRUE
    else
    FALSE
  end

下次还可以漂亮地打印您的代码片段（现在是一个 700 多个字符宽的查询——不容易阅读）。

有很多工具可以用来漂亮地打印查询。例如，我提供的修改后的查询实际上是这样打印的：

select f from StudentFee f
inner join      f.studentFeeDetails d
left outer join d.feeReceiptDetails r
inner join      f.student s
inner join      s.studyHistoryList h
inner join      h.academicYear a
where
  a.id in (:academicYearId)
and
(
  case when year(a.startDate) = year(a.endDate)
  then
    case when f.month >= month(a.startDate) and f.month <= month(a.endDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate)
      then
      TRUE
    else
      FALSE
    end
  end
  or
  case when year(a.endDate) >= year(a.startDate) and f.month <= month(a.endDate) and f.month >= month(a.startDate) and f.year >= year(a.startDate) and f.year <= year(a.endDate)
    then
    TRUE
    else
    FALSE
  end
)
group by f.id
having (
  sum(r.amountPaid) < sum(d.feeAmount)
  or
  sum(r.amountPaid) = null
)
and
str_to_date(
  concat(:feeDueDay,'-',f.month,'-',f.year), '%d-%m-%Y'
) < CURRENT_DATE