java - 如何使用查询而不是带有 JPA 的 @JoinColumn 映射实体关联？

Question

我将 Hibernate 4.1.3.Final 与 JPA 2.1 和 MySQL 5.5.37 一起使用。我有一个具有以下字段的实体:

@Entity
@Table(name = "category",
       uniqueConstraints = { @UniqueConstraint(columnNames = { "NAME" })}
)
public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    private Set<Subject> subjects;

    ...
}

subjects 字段没有简单的连接表，取而代之的是稍微复杂一点的 MySQL 查询。以下是根据特定类别 ID 计算主题的示例:

SELECT DISTINCT e.subject_id 
FROM category c, resource_category rc, product_resource pr, 
     sb_product p, product_book pe, book e 
WHERE c.id = rc.category_id 
  AND rc.resource_id = pr.resource_id 
  AND pr.product_id = p.id 
  AND p.id = pe.product_id 
  AND pe.ebook_id = e.id 
  AND c.id = ‘ABCEEFGH‘;

加载类别时，使用下面的查询连接上述字段的最简单方法是什么？

这个问题涉及处理 Java 以完成此操作，因此构建 View 或执行其他类型的 MySQL 疯狂操作不是一种选择，至少作为这个问题的答案是这样。

编辑:

根据建议添加了符号(将“=id”替换为“=id”)，但是当我查询与类别实体相关的项目时，Hibernate 生成了这个无效的 SQL。这是 SQL Hibernate 吐出的

SELECT categories0_.resource_id AS RESOURCE1_75_0_,
       categories0_.category_id AS CATEGORY2_76_0_,
       category1_.id            AS ID1_29_1_,
       category1_.NAME          AS name2_29_1_,SELECT DISTINCT e.subject_id
FROM            category c,
                resource_category rc,
                product_resource pr,
                product p,
                product_ebook pe,
                book e
WHERE           c.id = rc.category_id
AND             rc.resource_id = pr.resource_id
AND             pr.product_id = p.id
AND             p.id = pe.product_id
AND             pe.ebook_id = e.id
AND             c.id = category1_.id as formula1_1_,
                subject2_.id         AS id1_102_2_,
                subject2_.NAME       AS name2_102_2_
FROM            resource_category categories0_
INNER JOIN      category category1_
ON              categories0_.category_id=category1_.id
LEFT OUTER JOIN subject subject2_
ONSELECT DISTINCT e.subject_id
FROM            category c,
                resource_category rc,
                product_resource pr,
                product p,
                product_ebook pe,
                book e
WHERE           c.id = rc.category_id
AND             rc.resource_id = pr.resource_id
AND             pr.product_id = p.id
AND             p.id = pe.product_id
AND             pe.ebook_id = e.id
AND             c.id = category1_.id=subject2_.id
where           categories0_.resource_id=?

注意最后的“left outer join subject subject2_ on SELECT DISTINCT e.subject_id”和“AND c.id = category1_.id=subject2_.id”。

编辑 2:

这里是上面查询涉及的实体

@Entity
@Table(name="resource")
public class Resource implements Serializable, Comparable<Resource>
{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    …    
    @Column(name = "FILE_NAME")
    private String fileName;

    @Column(name = "URI")
    private String uri;

    …

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "resource_category", joinColumns = { @JoinColumn(name = "RESOURCE_ID") }, inverseJoinColumns = { @JoinColumn(name = "CATEGORY_ID") })
    private Set<Category> categories;

这是查询本身......

CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteria = builder.createQuery(Resource.class);
Root<T> rootCriteria = criteria.from(Resource.class);
criteria.select(rootCriteria).where(builder.equal(rootCriteria.get(“uri”),uri));
Resource ret = null;
try {
        final TypedQuery<T> typedQuery = m_entityManager.createQuery(criteria);
        ret = typedQuery.getSingleResult();
} catch (NoResultException e) {
        LOG.warn(e.getMessage());
}
return ret; 

Answer 1

您需要使用特定于 Hibernate 的 JoinColumnOrFormula :

public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(
            formula = @JoinFormula(
                value = 
                    "SELECT DISTINCT e.subject_id " +
                    "FROM category c, resource_category rc, product_resource pr, " +
                    "     sb_product p, product_book pe, book e " +
                    "WHERE c.id = rc.category_id " +
                    "  AND rc.resource_id = pr.resource_id " +
                    "  AND pr.product_id = p.id " +
                    "  AND p.id = pe.product_id " +
                    "  AND pe.ebook_id = e.id " +
                    "  AND c.id = ‘ABCEEFGH‘", 
                referencedColumnName="id"
            )
        )
    })
    private Set<Subject> subjects;

    ...
}

或者，您可以将此查询包含在存储过程中:

CREATE FUNCTION join_book(text) RETURNS text
    AS  'SELECT DISTINCT e.subject_id ' +
        'FROM category c, resource_category rc, product_resource pr, ' +
        '     sb_product p, product_book pe, book e ' +
        'WHERE c.id = rc.category_id ' +
        '  AND rc.resource_id = pr.resource_id ' +
        '  AND pr.product_id = p.id ' +
        '  AND p.id = pe.product_id ' +
        '  AND pe.ebook_id = e.id ' +
        '  AND c.id = $1;'  
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

然后，您的映射变为:

public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(
            formula = @JoinFormula(
                value = "join_book(id)", 
                referencedColumnName="id"
            )
        )
    })
    private Set<Subject> subjects;

    ...
}