java Criteria builder IN子句，带有嵌入式@ElementCollection的嵌套查询

Question

我有User和Address实体（实际上是其他实体，只是为了简化）

@Entity
public class User {
    @Id
    @GeneratedValue
    protected long id;
    ...

    @ElementCollection
    @CollectionTable(name = "address",
        joinColumns = {@JoinColumn(name = "user_id")})
    @Column(name = "street")
    private List<Address> addresses;

    ...
}

@Embeddable
public class Address {
    private String street;
    ...
}

如何对Address进行嵌套查询并在IN子句中使用它？结果查询，我期望'select*来自用户u where u.id in（从地址where street中选择用户id，如'%Green%'）

Root<User> root = ...; // Root query already exists

CriteriaQuery<Address> cq = builder.createQuery(Address.class);
Root<Address> addressRoot = cq.from(Address.class);

cq.select(addressRoot.get("user_id"))
    .where(
        builder.like(
            addressRoot.get("street"),
            "%Green%"
        )
    );

Predicate .... = criteriaQuery.where(root.get("id").in(cq))

我尝试过类似的方法，但它不起作用-Address它不是实体，我无法用它创建CriteriaQuery。 重要提示：我不能使用root.join("addresses").get("street").like(...)，原因有几个

Answer 1

您需要使用exists子查询，如下所示：

Root<User> root = ...; // Root query already exists

CriteriaQuery<Address> cq = builder.createQuery(Address.class);
Subquery<Integer> subquery = cq.subquery(Integer.class);
Root<User> correlatedRoot = subquery.correlate(root);
Join<User, Address> addressRoot = correlatedRoot.join("addresses");

cq.select(addressRoot.get("user_id"));
subquery.where(
        builder.like(
            addressRoot.get("street"),
            "%Green%"
        )
    );

Predicate .... = criteriaQuery.where(builder.exists(subquery));