如何使用CritieraQuery得到一个interesect查询?(How to get an i

2019-08-18 03:40发布

特定

 @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);


文章来源: How to get an interesect query using CritieraQuery?