java - Criteria builder IN clause with nested query for embedded @ElementCollection

Question

I have the User and Address entities (actually others but for simplify).

@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;
    ...
}

How to make nested query for Address and use it in IN clause? Result query that I expect `select * from user u where u.id in (select user_id from address where street like '%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))

I tried something like this, but it does not work - Address it is not entity and I can't create CriteriaQuery with it. Important note: I can't use root.join("addresses").get("street").like(...) for several reasons

question from:https://stackoverflow.com/questions/65942304/criteria-builder-in-clause-with-nested-query-for-embedded-elementcollection

Answer 1

You need to use an exists subquery, something like the following:

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