java - 使用实体多对多定义的 Hibernate HQL 投影问题

Question

问题:当我引用实体集合字段作为 HQL 语句的一部分时，HQL 查询不返回任何结果。它适用于一个 HQL 投影，例如如下所示:

select inc.categoryTypes as categoryTypes from IncidentEntity inc where (inc.id = :id105019)

categoryTypes 是 IncidentEntity 类字段之一(它是定义为 ManyToMany 连接的集合，如下所示)。这工作正常，但是当我尝试引用另一个映射为 ManyToMany 连接的投影集合时，就会出现问题。

select inc.categoryTypes as categoryTypes, inc.consequences as consequences from IncidentEntity inc where (inc.id = :id105019)

一旦我这样做，我就会得到一个空集。这意味着 hibernate 生成的 SQL 查询不会返回任何内容。我已经通过在 SQL Manager 中执行命令验证了这一点，该命令没有返回任何结果。

这是事件实体:

/**
 * Database entity for the 'incidents' table records.<br>
 * Entity domain object is {@link nz.co.doltech.ims.shared.domains.Incident}
 * @author Ben Dol
 * 
 */
@javax.persistence.Entity(name = "incidents")
@Cache(usage=CacheConcurrencyStrategy.TRANSACTIONAL)
public class IncidentEntity implements Entity {

    ...

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "incident_categorytype", joinColumns = { 
            @JoinColumn(name = "incident_id") }, 
        inverseJoinColumns = {
            @JoinColumn(name = "categorytype_id") 
    })
    private Set<CategoryTypeEntity> categoryTypes = new HashSet<CategoryTypeEntity>();

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "incident_consequence", joinColumns = { 
            @JoinColumn(name = "incident_id") }, 
        inverseJoinColumns = {
            @JoinColumn(name = "consequence_id") 
    })
    private Set<ConsequenceEntity> consequences = new HashSet<ConsequenceEntity>();

    ...

    public Set<CategoryTypeEntity> getCategoryTypes() {
        return categoryTypes;
    }
    public void setCategoryTypes(Set<CategoryTypeEntity> categoryTypes) {
        this.categoryTypes = categoryTypes;
    }

    public Set<ConsequenceEntity> getConsequences() {
        return consequences;
    }
    public void setConsequences(Set<ConsequenceEntity> consequences) {
        this.consequences = consequences;
    }

    ...
}

CategoryTypeEntity关系定义:

@ManyToMany(fetch = FetchType.LAZY, mappedBy = "categoryTypes")
private Set<IncidentEntity> incidents = new HashSet<IncidentEntity>();

ConsequenceEntity关系定义:

@ManyToMany(fetch = FetchType.LAZY, mappedBy = "consequences")
private Set<IncidentEntity> incidents = new HashSet<IncidentEntity>();

数据结构:

使用Hibernate 3.6.10

也许我设置的定义错误，或者我在这里缺少 HQL 的限制，我不确定。如果我能在这里得到任何帮助，我将不胜感激。谢谢!

问候，本

Answer 1

您知道您正在使用此查询生成笛卡尔积，对吧？

查询可以更好地可视化为:

select categoryTypes, consequences 
from IncidentEntity inc 
inner join inc.categoryTypes as categoryTypes
inner join inc.consequences as consequences
where (inc.id = :id105019)

因为您没有指定显式联接，所以假定 INNER JOIN 不是 LEFT JOIN。

假设指定事件有多个类别。因此，此查询将返回此事件的类别，这也是您报告的内容:

select categoryTypes
from IncidentEntity inc 
inner join inc.categoryTypes as categoryTypes
where (inc.id = :id105019)

但是当没有结果时，INNER JOIN 将不会返回任何结果，因此:

select categoryTypes, consequences 
from IncidentEntity inc 
inner join inc.consequences as consequences
where (inc.id = :id105019)

不会返回任何内容，但是您的查询也可能会发生这种情况:

select categoryTypes, consequences 
from IncidentEntity inc 
inner join inc.categoryTypes as categoryTypes
inner join inc.consequences as consequences
where (inc.id = :id105019)