JPA/JPQL:SELECT子句中不允许使用AS标识符 [英] JPA/JPQL: AS identifier disallowed in SELECT clause
问题描述
我有一个非常复杂的JPQL查询形式
I have a pretty complex JPQL query of the form
SELECT NEW com.domain.project.view.StandingsStatLine(
ro.id AS rid
, cl.name AS team
, te.ordinalNbr + 1 AS nr
, pa.wasWithdrawn AS wd
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END) AS g
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore > sca.finalScore THEN 1 ELSE 0 END) AS w
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore < sca.finalScore THEN 1 ELSE 0 END) AS l
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 20 AND sca.finalScore = 0 THEN 1 ELSE 0 END) AS wbr
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 0 AND sca.finalScore = 20 THEN 1 ELSE 0 END) AS lbr
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 0 AND sca.finalScore = 0 THEN 1 ELSE 0 END) AS blbr
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore > sca.finalScore THEN 1 ELSE 0 END)
+ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END)
- SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 0 AND sca.finalScore = 20 THEN 1 ELSE 0 END) AS rp
, CASE WHEN ( SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore > sca.finalScore THEN 1 ELSE 0 END)
+ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END)
- SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 0 AND sca.finalScore = 20 THEN 1 ELSE 0 END)
- SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END))
/ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1.0 ELSE 0.0 END) >= 0.0
THEN (( SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore > sca.finalScore THEN 1 ELSE 0 END)
+ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END)
- SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore = 0 AND sca.finalScore = 20 THEN 1 ELSE 0 END)
- SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END))
/ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1.0 ELSE 0.0 END))
ELSE 0.0 END AS nrp
, CASE WHEN SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END) > 0
THEN (SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore > sca.finalScore THEN 1.0 ELSE 0.0 END)
/ SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN 1 ELSE 0 END))
ELSE NULL END AS wperc
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore IS NOT NULL THEN scf.finalScore ELSE 0 END) AS ptsf
, SUM(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND sca.finalScore IS NOT NULL THEN sca.finalScore ELSE 0 END) AS ptsa
, MAX(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore NOT IN (0, 20) THEN scf.finalScore ELSE NULL END) AS hi
, MAX(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore NOT IN (0, 20) THEN scf.game.id ELSE NULL END) AS higid
, MIN(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore NOT IN (0, 20) THEN scf.finalScore ELSE NULL END) AS lo
, MIN(CASE WHEN paf.wasWithdrawn = FALSE AND paa.wasWithdrawn = FALSE AND scf.finalScore NOT IN (0, 20) THEN scf.game.id ELSE NULL END) AS logid
)
FROM Club cl
JOIN cl.teams te
JOIN te.rosters ro
JOIN ro.season se
JOIN ro.participations pa
JOIN pa.group gr
JOIN gr.round rd
JOIN rd.subCompetition sc
JOIN sc.competition cn
JOIN gr.games ga
JOIN ga.scores scf
JOIN ga.scores sca
JOIN scf.roster rof
JOIN sca.roster roa
JOIN rof.participations paf
JOIN roa.participations paa
WHERE ...
GROUP BY ...
ORDER BY pa.wasWithdrawn, nrp DESC, w DESC, lbr, g DESC, cl.shorthand
在Hibernate 4.x上运行它可以正常工作，但是Eclipse Dali在构造函数表达式的几乎每一行上都显示JPQL错误.删除... AS <identifier>可以消除这些错误.
Running this with Hibernate 4.x works without problems, however Eclipse Dali shows a JPQL error on pretty much every line of the constructor expression. Removing the ... AS <identifier> gets rid of these errors.
我查看了JPQL BNF http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF 在这里，但是我在SELECT子句层次结构中找不到AS.
I looked at the JPQL BNF http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF here, but I couldn't find the AS in the SELECT clause hierarchy.
Q:
这是特定于Hibernate的语法吗?
Is this Hibernate-specific syntax?
JPQL为什么不支持此功能?
Why doesn't JPQL support this?
有时表达式可能变得非常复杂，我想通过AS子句记录该表达式的实际含义，尤其是考虑到JPQL没有注释功能.这是JPQL恕我直言的真正缺陷.
Sometimes expression can become very complex and I'd like to document what the expression actually means via the AS clause, especially considering that JPQL has no commenting functionality. This is a real flaw in JPQL IMHO.
此外，Hibernate还允许我在ORDER BY子句中引用这些AS es，这使我可以重复使用上面的复杂语句.
Furthermore, Hibernate allows me to reference these ASes in the ORDER BY clause, which allows me to reuse the complex statement from above.
也许我的表情可以缩短，但在其他情况/项目中，复杂度仍然很高.
Maybe my expressions could be shortened but complexity can still be high in other cases/projects.
谢谢
推荐答案
找到了它.
根据JPQL BNF，网址为 http://en.wikibooks.org/Wiki/Java_Persistence/JPQL_BNF#New_in_JPA_2.0 应该由JPA 2.0支持.请注意，倒数第二个示例.
According to the JPQL BNF at http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#New_in_JPA_2.0 this should be supported by JPA 2.0. Note, the second to last example.
这几乎可以回答原始问题，为什么Eclipse Dali会显示JPQL验证错误.大理似乎有问题.
This pretty much answers the original question why Eclipse Dali shows JPQL validation errors. It seems to be a problem with Dali.
这篇关于JPA/JPQL:SELECT子句中不允许使用AS标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
