Issue
I have the following scenario:
Person.class:
@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<>();
...
}
Category.class:
@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;
...
}
With this scenario, I need to do a query using JPA2 Criteria, to substitute this HQL query:
SELECT obj FROM Person AS obj
WHERE EXISTS (
SELECT category.id FROM obj.categories AS category
WHERE category.name = 'Staff'
)
I've tried to make a join with categories, but it multiply the result by the quantity of categories of each person:
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();
This criteria result in this 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 ?
)
)
The generated SQL do a CROSS JOIN with person_category
and do the LEFT JOIN out of subselect.
Using the HQL Hibernate produces the following 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=?
)
How I can do this query in Criteria?
Solution
You need to correlate()
the outer query with the subquery. Try something along the following lines (it is hard to test without having the real model, but should be close enough):
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();
This should result in:
SELECT obj FROM Person AS obj
WHERE EXISTS (
SELECT category FROM obj.categories AS category
WHERE category.name = 'Staff'
)
Answered By - Nikos Paraskevopoulos
Answer Checked By - Timothy Miller (JavaFixing Admin)