我有一个非常复杂的 JPQL 查询,其形式为
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>
摆脱这些错误。
我查看了 JPQL BNFhttp://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF在这里,但我找不到AS
在 SELECT 子句层次结构中。
Q's:
这是 Hibernate 特定的语法吗?
为什么 JPQL 不支持这个?
有时表达式可能变得非常复杂,我想通过以下方式记录该表达式的实际含义:AS
子句,特别是考虑到 JPQL 没有注释功能。恕我直言,这是 JPQL 的一个真正的缺陷。
此外,Hibernate 允许我参考这些AS
es 中的ORDER BY
子句,它允许我重用上面的复杂语句。
也许我的表达可以缩短,但在其他案例/项目中复杂性仍然很高。
Thanks