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)

谢谢

Answer 1

您正在使用搜索的 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