通过集合集选择实体 [英] Selecting an entity by collection set equality
问题描述
我正在尝试执行以下操作的JPQL查询或JPA操作。我有一个元素,由一个字符串的元素集合组成:
I'm trying to do a JPQL query or JPA operation that does the following. I have an element, that consist of an element collection of Strings:
@Entity(name="REQUEST")
public class Request {
@ElementCollection
private Set<String> keywords;
...
}
我希望能够选择实体whos关键字与给定的字符串集完全匹配。我已经研究过使用IN但是如果只存在一个关键字则匹配。如果所有关键字都存在,我该如何匹配?
I want to be able to select the entity whos keywords exactly matches a given Set of strings. I've looked into using IN but that will match if only one keyword exists. How do I match only if all keywords exist?
推荐答案
我能想到的最简单的方法是做两个计数查询: / p>
The simplest approach I can think of is to do two count queries:
- 集合中关键字数量的计数
- 关键字数量的计数NOT在集合中
#1的结果应该等于集合中的关键字数量。 #2的结果应该等于0.例如:
The result of #1 should equal the number of keywords in the set. The result of #2 should equal 0. For example:
List<Request> requests =
em.createQuery("select r from Request r " +
"where (select count(k1) from Request r1 " +
" join r1.keywords k1 " +
" where r1 = r and k1 in :keywords) = :numKeywords " +
"and (select count(k2) from Request r2 " +
" join r2.keywords k2 " +
" where r2 = r and k2 not in :keywords) = 0", Request.class)
.setParameter("keywords", keywords)
.setParameter("numKeywords", keywords.size())
.getResultList();
如果您关心的是该集合是否是请求关键字的子集,那么不需要第二次计数。这可以通过以下方式在一个查询中完成:
If all you cared about was whether or not the set is a subset of the Request's keywords, then the second count is not needed. This can be done in a single query with a group by:
List<Request> requests =
em.createQuery("select r from Request r " +
"join r.keywords k " +
"where k in :keywords " +
"group by r " +
"having count(r) = :numKeywords", Request.class)
.setParameter("keywords", keywords)
.setParameter("numKeywords", keywords.size())
.getResultList();
这篇关于通过集合集选择实体的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!