java - Hibernate HQL 与 IS NULL 的奇怪行为

Question

我的 HQL 查询有问题。我想要获取所有管理性别设置为“M”或没有管理性别(在 Java 中该值设置为 null)的 PI​​D。

PID.class

@Entity
@Table(name = "PatientIdentification")
public class PID {    

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "administrativeSex", referencedColumnName = "is_id")
    private IS administrativeSex;
    ...
}

IS.class

@Entity
@Table(name = "CodedValueForUserDefinedTables")
public class IS {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "is_id")
    private Integer id;

    private String value;
    ...
}

HQL 查询

from PID where administrativeSex is null or administrativeSex.value = 'M'

生成的SQL

select
  pid0_.pid_id as pid1_84_,
  pid0_.administrativeSex as adminis11_84_,
  pid0_.birthOrder as birthOrder84_,
  pid0_.birthPlace as birthPlace84_,
  pid0_.citizenship as citizen12_84_,
  pid0_.countyCode as countyCode84_,
  pid0_.dateTimeOfBirth as dateTim19_84_,
  pid0_.driverLicenseNumber as driverL22_84_,
  pid0_.maritalStatus as maritalS8_84_,
  pid0_.multipleBirthIndicator as multiple4_84_,
  pid0_.nationality as nationa17_84_,
  pid0_.owner_id as owner9_84_,
  pid0_.patientAccountNumber as patientA6_84_,
  pid0_.patientDeathDateAndTime as patient16_84_,
  pid0_.patientDeathIndicator as patient18_84_,
  pid0_.patientId as patientId84_,
  pid0_.primaryLanguage as primaryL7_84_,
  pid0_.race as race84_,
  pid0_.religion as religion84_,
  pid0_.setId as setId84_,
  pid0_.ssnNumber as ssnNumber84_,
  pid0_.veteransMilitaryStatus as veterans2_84_
 from
  PatientIdentification pid0_,
  CodedValueForUserDefinedTables is1_
 where
  pid0_.administrativeSex=is1_.is_id
  and (
   pid0_.administrativeSex is null
   or is1_.value='M'
  )

查询仅返回管理性别设置为“M”的PID。它缺少一个没有行政性别的人。如果你查看SQL查询，这是正常的。如何更正我的 HQL 查询？

Answer 1

Hibernate 引用手册 writes :

HQL supports two forms of association joining: implicit and explicit.

The queries shown in the previous section all use the explicit form, that is, where the join keyword is explicitly used in the from clause. This is the recommended form.

The implicit form does not use the join keyword. Instead, the associations are "dereferenced" using dot-notation. implicit joins can appear in any of the HQL clauses. implicit join result in inner joins in the resulting SQL statement.

由于内部联接会过滤掉联接条件不匹配的那些行，因此您会丢失未知性别的人员。您将需要一个左外连接，您必须请求 explicity :

from PID pid
left outer join pid.administrativeSex as sex 
where pid.administrativeSex is null or sex.value = 'M'