Hibernate 5.5.x升级至6.1.7后鉴别器查询生成错误

实体模型 #

@Entity
@Table(name = "PARTY")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "CLASS", discriminatorType = DiscriminatorType.STRING)
public class Party implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "PARTY_ID")
    private long partyID;

    @Column(name = "STATE")
    private String state;

    /*Lots of other columns NOT including CLASS though */
}

@Entity
@DiscriminatorValue("Organisation")
public class Organisation extends Party {
 
 @Column(name = "ORGANISATION_NAME", unique = true)
 private String name; 
}

@Entity
@DiscriminatorValue("Person")
public class Person extends Party {
  
  @OneToMany(cascade=CascadeType.ALL, mappedBy = "clientParty", fetch = FetchType.LAZY, orphanRemoval = true)
    @Fetch(value = FetchMode.SUBSELECT)
    private List<Relationship> relationships = new ArrayList<>();

}

@Entity
@Table(name = "RELATIONSHIP")
public class Relationship implements Serializable { 

    @Id
    @GeneratedValue
    @Column(name = "RELATIONSHIP_ID")
    private long relationshipId;

    @Column(name="CLIENT_PARTY_ID", insertable = false, updatable = false)
    private Long clientPartyId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CLIENT_PARTY_ID", nullable = false)
    private Person clientParty;

    @Column(name="SUPPLIER_PARTY_ID", insertable = false, updatable = false)
    private Long supplierPartyId;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SUPPLIER_PARTY_ID", nullable = false)
    private Organisation supplierParty;
}

查询语句 #

用于获取状态为'active'或所属组织名称为'abc'的唯一用户的HQL：

SELECT DISTINCT u FROM Person u 
LEFT JOIN Relationship r ON u.partyID = r.clientPartyId 
LEFT JOIN Organisation o ON r.supplierPartyId = o.partyID 
WHERE lower(o.name) LIKE :orgName OR
lower(u.state) = :state

测试预期 #

预期返回3个用户：

• 用户A - 状态'active'，隶属于名称为'abc'的组织
• 用户B - 状态'inactive'，隶属于名称为'abc'的组织
• 用户C - 状态'active'，未隶属于任何组织

问题现象 #

Hibernate 5中该查询正常工作，但升级至6.1.7后，无关联关系的用户C被排除。

生成的SQL（Hibernate 6.1.7） #

select distinct
m1_0.PARTY_ID from PARTY m1_0 
left join RELATIONSHIP r1_0 on m1_0.PARTY_ID=r1_0.CLIENT_PARTY_ID 
left join PARTY m2_0 on r1_0.SUPPLIER_PARTY_ID=m2_0.PARTY_ID 
where (lower(m2_0.ORGANISATION_NAME) like ? or lower(m1_0.STATE)=?) and
m1_0.CLASS='Person' and m2_0.CLASS='Organisation'

问题分析 #

Hibernate 6错误地将Organisation的鉴别器条件m2_0.CLASS='Organisation'放到了WHERE子句中，而非LEFT JOIN的ON关联条件里。这会导致LEFT JOIN退化为INNER JOIN的效果——没有关联组织的用户记录会因为m2_0.CLASS为NULL而被WHERE条件过滤掉。

解决方案 #

由于无法升级至6.2版本，可通过修改HQL手动将鉴别器条件移至LEFT JOIN的ON子句中，有两种写法：

写法1：使用HQL的type()函数（推荐） #

利用HQL内置的type()函数指定实体类型，Hibernate会自动转换为对应的鉴别器条件：

SELECT DISTINCT u FROM Person u 
LEFT JOIN Relationship r ON u.partyID = r.clientPartyId 
LEFT JOIN Organisation o ON r.supplierPartyId = o.partyID AND type(o) = Organisation
WHERE (lower(o.name) LIKE :orgName OR lower(u.state) = :state)

写法2：直接指定鉴别器列 #

由于实体未映射CLASS字段，可直接在HQL中引用数据库列名：

SELECT DISTINCT u FROM Person u 
LEFT JOIN Relationship r ON u.partyID = r.clientPartyId 
LEFT JOIN Organisation o ON r.supplierPartyId = o.partyID AND o.CLASS = 'Organisation'
WHERE (lower(o.name) LIKE :orgName OR lower(u.state) = :state)

修改后的HQL生成的SQL会将鉴别器条件加入LEFT JOIN的ON子句，确保无关联关系的用户C不会被过滤，符合预期结果。

内容的提问来源于stack exchange，提问作者petula