我正在使用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以实现这一点,因此构建视图或做其他类型的MySQL疯狂不是一种选择,至少作为这个问题的答案 .
Edit:
根据建议添加了表示法(用'= id'替换'='ABCDEFG“')但是当我查询绑定到Category实体的项目时,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=?
注意结尾处的“左外连接主题subject2_在SELECT DISTINCT e.subject_id上”和“AND c.id = category1_.id = subject2_.id” .
Edit 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;
2 回答
你需要使用Hibernate特定的JoinColumnOrFormula:
编辑
您可以在存储过程中包含此查询:
然后您的映射变为:
SessionFactory范围的hibernate拦截器实现可能会有所帮助 . 我没有一个有效的例子 .