如何使用CriteriaQuery获取交集查询

Question

给定：

@Entity
public class entity_details  {
@Id
private int id;
@column(name ="entity_id")
private int entityId;
@column(name="att_id")
private int attId;
@column(name="att_value")
private string attVal;

我想找到一个与下面的等效的查询

从entity_details中选择entity_id，其中att_id=15且att_value='test'
相交
从entity/details中从att_id=18且att_vvalue='test2'中选择entity _id；

仅使用CriteriaQuery。

Answer 1

这是一个复杂的操作，您可以尝试这样的操作：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<EntityDetails> root = cq.from(EntityDetails.class);
Subquery<EntityDetails> sq = cq.subquery(EntityDetails.class);
Root<EntityDetails> rootSQ = cq.from(EntityDetails.class);
sq.where(
cb.and(
cb.and(
cb.equal(rootSQ.get("attId"),18),
cb.equal(rootSQ.get("attValue"),"test2")
),
cb.equal(rootSQ.get("id"),root.get("id"))
)
);
cq.where(
cb.and(
cb.and(
cb.equal(root.get("attId"),15),
cb.equal(root.get("attValue"),"test")
),
cb.exist(sq)
)
);
cq.multiselect(root.get("entityId"));

什么会生成以下查询：

select e1.entity_id from entity_details e1
where e1.att_id = 15 and e1.att_value = "test"
and exists(
select * from entity_details e2
where e2.att_id = 18 and e2.att_value = "test2"
and e1.id = e2.id
)