本文介绍了JPA/JPQL:SELECT子句中不允许使用AS标识符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个非常复杂的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标识符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-03 08:32