jpa - 是否可以通过 ElementCollections 查询 JPA 实体，其中 ElementCollection 包含给定元素集中的所有元素？

Question

如何使用 JPQL 通过 ElementCollections 查询 JPA 实体，其中 ElementCollection 包含给定元素集中的所有元素？

例如，如果 Node 实体定义了“属性”的 ElementCollection

@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="node_attributes", joinColumns=@JoinColumn(name="node_name"))
@MapKeyColumn(name="name")
@Column(name="value")
Map<String, String> attributes = new HashMap<String, String>();

我想找到具有一组给定属性的所有节点实体。我尝试了以下操作，希望“MEMBER OF”支持集合。

"SELECT n FROM Node n WHERE :attributes MEMBER OF n.attributes"

此查询始终返回空列表。此用例可以使用简单的 JPQL 查询吗？

Answer 1

来自 JPA 2.0 规范，章节 4.4.4 路径表达式:

An identification variable followed by the navigation operator (.) and a state field or association field is a path expression. The type of the path expression is the type computed as the result of navigation; that is, the type of the state field or association field to which the expression navigates.

An identification variable qualified by the KEY, VALUE, or ENTRY operator is a path expression. The KEY, VALUE, and ENTRY operators may only be applied to identification variables that correspond to map-valued associations or map-valued element collections.

A path expression using the ENTRY operator is terminal. It cannot be further composed and can only appear in the SELECT list of a query.

理论就讲这么多。至于这个问题，我看到三个略有不同的答案，具体取决于您是否想通过 Map.Key、Map.Value 或 Map.Entry 进行搜索:

String qlKeys = "SELECT DISTINCT n " +
                "FROM Node n JOIN n.attributes a " +
                "WHERE KEY(a) IN :keys";
List<Node> nodes = em.createQuery(qlKeys, Node.class)
                     .setParameter("keys", Arrays.asList("foo", "bar"))
                     .getResultList();

String qlValues = "SELECT DISTINCT n " +
                  "FROM Node n JOIN n.attributes a " +
                  "WHERE VALUE(a) IN :values";
List<Node> nodes = em.createQuery(qlValues, Node.class)
                     .setParameter("values", Arrays.asList("baz", "qux"))
                     .getResultList();

请记住，ENTRY 运算符只能出现在 SELECT 子句中，我们可能必须将其替换为 KEY 和 VALUE 运算符的组合，即

SELECT DISTINCT n 
FROM Node n JOIN n.attributes a
WHERE KEY(a) IN :keys AND VALUE(a) IN :values

在上面的例子中:

• 识别变量由c表示
• 路径表达式由c.attributes表示
• 关联字段由a表示