Hibernate Criteria API Subquery on @ElementCollection

Question

I've already searched a lot but can't get it to work. I've also found this, which comes close but it requires a unnecessary join and can't ignore case sensitivity: Criteria API not in @ElementCollection

This is the simplified entity:

@Entity(name = "Users")
public class User {
    @Id
    @Column(columnDefinition = "TEXT")
    private String id;

    @ElementCollection
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<String> communities;
}

The jpa/hibernate magic translates this into two tables: users(text id) and users_communities(text id, text communities)

The plain sql statement I would like to map to a criteria is:

select u.id from users u
   where u.users_id not in (
    select uc.users_id
    from users_communities uc
    where lower(communities) = 'test_user_app'
);

This is my current java code. How can use "criteriaBuilder.lower()" on the subJoin? Is there no easier way? Isn't it possible to avoid the manual subJoin?

Subquery<String> subquery = criteria.subquery(String.class);
Root<User> subRoot = subquery.from(User.class);
Join<Object, Object> subJoin = subRoot.join("communities");
subquery.select(subRoot.get("id"));
subquery.where(criteriaBuilder.equal(root.get("id"), subRoot.get("id")), subJoin.in("community_to_find"));
return criteriaBuilder.exists(subquery);

score 4 · Accepted Answer · answered Jul 06 '20 at 18:00

Unbelievable that took me all day ... In case one does NOT need case insensitivity or like expression, the simplest way is:

criteriaBuilder.isMember(
   "community_to_find", 
   criteria.from(User.class).get("communities")
);

In case one DOES require like expressions or case insensitivity: It doesn't seem it's currently possible to avoid the unnecessary sub join! Anyway this is finally making my tests green again :-) :

Subquery<String> subquery = criteria.subquery(String.class);
Root<User> subRoot = subquery.from(User.class);
subquery.select(subRoot.get("id"));
subquery.where(criteriaBuilder.like(criteriaBuilder.lower(subRoot.join("communities")), argument));
return criteriaBuilder.in(root.get("id")).value(subquery);

Hint: You do probably already have the Root defined from the outer query and since it's an ElementCollection it's most likely the same class. In that case you can define the subRoot more generic by just:

Root<? extends User> subRoot = subquery.from(root.getJavaType());

Hibernate Criteria API Subquery on @ElementCollection

1 Answers1