作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有以下情况:
人物类别:
@Entity
@Table(name = "person")
@SequenceGenerator(name = "seq_person", sequenceName = "seq_person", allocationSize = 1, initialValue = 1)
public class Person implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_pessoa")
@Column(name = "id")
private int id;
@Column(name = "name", nullable = false)
private String name;
@ManyToMany(fetch = FetchType.LAZY, cascade = { javax.persistence.CascadeType.REFRESH, javax.persistence.CascadeType.MERGE })
@JoinTable(name = "person_category", joinColumns = { @JoinColumn(name = "person_id") }, inverseJoinColumns = { @JoinColumn(name = "category_id") })
protected List<Category> categories = new ArrayList<>();
...
}
类别.类:
@Entity
@Table(name = "category")
@SequenceGenerator(name = "seq_category", sequenceName = "seq_category", allocationSize = 1, initialValue = 1)
public class Category implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_category")
@Column(name = "id")
private int id;
@Column(name = "name", nullable = false)
private String name;
...
}
在这种情况下,我需要使用 JPA2 Criteria 进行查询,以替换此 HQL 查询:
SELECT obj FROM Person AS obj
WHERE EXISTS (
SELECT category.id FROM obj.categories AS category
WHERE category.name = 'Staff'
)
我尝试与类别进行连接,但结果是将每个人的类别数量相乘:
CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Person> cq = cb.createQuery(Person.class);
Root<Person> root = cq.from(Person);
cq.select(root);
Subquery<Category> subquery = cq.subquery(Category.class);
Root<Category> subqueryFrom = subquery.from(Category.class);
subquery.select(subquery.from(clazz).get("id"));
Predicate predicateJoin = cb.equal(root.join("categories", JoinType.LEFT), subqueryFrom);
Predicate predicateName = cb.like(subqueryFrom.get("name"), "%" + content + "%");
subquery.where(cb.and(predicateJoin, predicateName));
cb.where(cb.exists(subquery));
TypedQuery<Person> typedQuery = this.entityManager.createQuery(cq);
List<Person> resultList = typedQuery.getResultList();
此条件会产生此 SQL:
select
person0_.id as id1_50_,
person0_.name as name29_50_
from
person person0_
left outer join
person_category categories2_
on person0_.id=categories2_.person_id
left outer join
category categoryp3_
on categories2_.category_id=categoryp3_.id
where
exists (
select
categoryp5_.id
from
person_category categoryp4_ cross
join
person_category categoryp5_
where
categoryp3_.id=categoryp4_.id
and (
categoryp4_.name like ?
)
)
生成的 SQL 与 person_category
执行 CROSS JOIN,并在子选择中执行 LEFT JOIN。
使用 HQL Hibernate 会生成以下 SQL:
select
person0_.id as id1_50_,
person0_.name as name29_50_
from
person person0_
where
exists (
select
categoryp2_.id
from
person_category categories1_,
category categoryp2_
where
person0_.id=categories1_.person_id
and categories1_.category_id=categoryp2_.id
and categoryp2_.name=?
)
如何在 Criteria 中执行此查询?
最佳答案
您需要 correlate()
外部查询与子查询。尝试按照以下方式进行操作(没有真实模型很难进行测试,但应该足够接近):
CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Person> cq = cb.createQuery(Person.class);
Root<Person> root = cq.from(Person.class);
cq.select(root);
Subquery<Category> subquery = cq.subquery(Category.class);
Root<Person> subqueryRoot = subquery.correlate(root);
Join<Person,Category> personCategories = subqueryRoot.join("categories");
subquery
.select(personCategories) // check out comment for possible needed change
.where(cb.equal(personCategories.get("name"), "Staff"));
cq.where(cb.exists(subquery));
TypedQuery<Person> typedQuery = this.entityManager.createQuery(cq);
List<Person> resultList = typedQuery.getResultList();
这应该导致:
SELECT obj FROM Person AS obj
WHERE EXISTS (
SELECT category FROM obj.categories AS category
WHERE category.name = 'Staff'
)
关于java - JPA 2 标准多对多子选择,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43325640/
对于以下多子脚本: multi sub Screen_get_valid_string($prompt, $accept_empty_string, $max_length = 999) { retu
我正在开发 open-source, cross-platform具有统计支持的番茄计时器。 对于任务,我有一个像这样的树数据结构: class Task { String name;
我是一名优秀的程序员,十分优秀!