代码之家 › 专栏 › 技术社区 › Joshua Taylor

使用criteriabuilder从子查询中的路径选择?

criteria-api jpql jpa java

Joshua Taylor · 技术社区 · 6 年前

如何使用标准生成器来构造 子查询 形式的 SELECT a FROM e.attributes a .... 哪里 e 外部查询中是否引用了某个实体?

我有一些实体类涉及一个自由形式的关键价值结构(它提出了自己的问题,但这正是我所拥有的)。我需要找到存在某些键值对的实体。我可以将其写为表单的jpql查询:

SELECT e FROM Entity e 
WHERE e.type = 'foo' 
AND EXISTS (SELECT a FROM e.attributes a 
             WHERE a.key = 'bar' 
               AND a.value = 'baz')

对于固定的查询字符串,我可以使用EntityManager.CreateQuery()创建查询:

EntityManager em = /* ... */;
TypedQuery<Entity> tq = em.createQuery(queryString, Entity.class);

实际上,不止一个存在在查询中,所以我需要使用CriteriaBuilder构造查询。到目前为止,我最接近的是子查询 SELECT a from Attributes a WHERE ... 但这不限于 e.attributes 当然:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Entity> query = cb.createQuery(Entity.class);
Root<Entity> root = query.from(Entity.class);

Subquery<Attribute> subquery = query.subquery(Attribute.class);
Root<Attribute> subroot = subquery.from(Attribute.class); // too broad
subquery.select(subroot)
    .where(cb.and(//
        cb.equal(subroot.get("key"), cb.literal("bar")),
        cb.equal(subroot.get("value"), cb.literal("baz"))));

query.select(root)
    .where(cb.and(//
        cb.equal(root.get("type"), cb.literal("foo")), //
        cb.exists(subquery)));

有很多 correlate() 子查询上的方法,我想知道是否需要将实体与其外部查询中的属性联接起来,然后以某种方式关联(),但我不确定从EE7 javadocs中究竟相关做了什么(但correlate()确实返回了一个from,这意味着我可以从中进行选择,这是很有希望的)。

1 回复 | 直到 6 年前

Joshua Taylor 6 年前

JPA 2.0 spec

CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
Root<Customer> customer = q.from(Customer.class);
Subquery<Long> sq = q.subquery(Long.class);
Root<Customer> customerSub = sq.correlate(customer);
Join<Customer,Order> order = customerSub.join(Customer_.orders);
q.where(cb.gt(sq.select(cb.count(order)), 10))
.select(customer);

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

from_clause ::=  FROM  identification_variable_declaration  ...
identification_variable_declaration ::= range_variable_declaration ...
range_variable_declaration ::= entity_name [AS] identification_variable

e.attributes

subquery_from_clause ::= FROM subselect_identification_variable_declaration ...
subselect_identification_variable_declaration ::=
  identification_variable_declaration |
  derived_path_expression [AS] identification_variable {join}* |
  derived_collection_member_declaration
derived_path_expression ::=
  superquery_identification_variable.{single_valued_object_field.}*collection_valued_field |
  superquery_identification_variable.{single_valued_object_field.}*single_valued_object_field