jpa - 如何使用 Criteria Query 获取相交查询？

Question

给定

 @Entity
 public class Document {

    @Id
    @Column(name = "DOCUMENT_ID")
    private Long id;

    @ElementCollection
    @CollectionTable(
       name="TAG",
       joinColumns=@JoinColumn(name="DOCUMENT_ID")
    )
    @Column(name="TAG")
    private Set<String> tags;    
 }

查找所有标记有特定标签集合的文档。本质上，EclipseLink 等效于:

SELECT d FROM Document d WHERE :tag1 MEMBER OF d.tags
INTERSECT
SELECT d FROM Document d WHERE :tag2 MEMBER OF d.tags
...
SELECT d FROM Document d WHERE :tagn MEMBER OF d.tags

但使用 JPA CritieraQuery。

Answer 1

使用带有 having 子句的聚合查询来选择与所有必需标记匹配的行:

    CriteriaBuilder cb = entityManager().getCriteriaBuilder();
    CriteriaQuery<Long> q = cb.createQuery(Long.class);
    Root<Document> from = q.from(Document.class);
    List<Predicate> predicates = new ArrayList<Predicate>();
    Expression<Long> id = from.get("id");

    Expression<Collection<String>> documentTags = from.get("tags");
    for (String tag : searchedTags) {
        Expression<String> param = cb.literal(tag);
        Predicate predicate = cb.isMember(param, documentTags);
        predicates.add(predicate);
    }

    q.multiselect(id).where(
            cb.or(predicates.toArray(new Predicate[0])));
    q.distinct(true);
    q.groupBy(id);

    q.having(cb.equal(cb.count(id), searchedTags.size()));

    TypedQuery<Long> query = entityManager().createQuery(q);