代码之家  ›  专栏  ›  技术社区  ›  Zoidberg

HQL Bug和可空字段

  •  0
  • Zoidberg  · 技术社区  · 16 年前

    为什么这个查询不起作用

    FROM WorkflowConfiguration
    WHERE ((saReplacement IS NOT NULL) AND (:currentTime >= saReplacement.start) AND (saReplacement.end >= :currentTime)) OR
                  ((hrmsAdminReplacement IS NOT NULL) AND (:currentTime >= hrmsAdminReplacement.start) AND (hrmsAdminReplacement.end >= :currentTime)) OR
                  ((hrManagerReplacement IS NOT NULL) AND (:currentTime >= hrManagerReplacement.start) AND (hrManagerReplacement.end >= :currentTime)) OR
                  ((payrollAdminReplacement IS NOT NULL) AND (:currentTime >= payrollAdminReplacement.start) AND (payrollAdminReplacement.end >= :currentTime))
    

    但这个是

    FROM WorkflowConfiguration
    WHERE (saReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
                  (hrmsAdminReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
                  (hrManagerReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
                  (payrollAdminReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime)))
    

    之所以这么长,是因为sareplacement、hrmsadminreplacement、hrmanagerreplacement和payrolladminreplacement都可以为空,所以我进行了一个检查,以确保它们不为空,从而不会执行该子句的其余部分。但是出于某种原因,当我运行第一个查询时,我没有得到任何结果,但是如果我运行第二个查询(效率要低得多),我会得到所有预期的结果。这是Hibernate的HQL中的一个bug,还是我疯了?

    1 回复  |  直到 16 年前
        1
  •  1
  •   Jherico    16 年前

    您是否尝试过类似的操作:

    FROM WorkflowConfiguration wfc
    LEFT JOIN FETCH wft.saReplacement a
    LEFT JOIN FETCH wft.hrmsAdminReplacement b
    LEFT JOIN FETCH wft.hrManagerReplacement c
    LEFT JOIN FETCH wft.payrollAdminReplacement d
    WHERE
    ((:currentTime >= a.start) AND (a.end >= :currentTime)) or
    ((:currentTime >= b.start) AND (b.end >= :currentTime)) or
    ((:currentTime >= c.start) AND (c.end >= :currentTime)) or
    ((:currentTime >= d.start) AND (d.end >= :currentTime))