SubQuery in a CriteriaQuery

Question

I have the abstract class Entity and these two Entities Role and User which extends from Entity:

@Entity@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class Entity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected long id = 0; }
    

@Entity
public class Role extends Entity{
    @Column(name = "NAME", unique = true, nullable = false)
    private String name;
}

@Entity
public class User extends Entity{
    @Column(name = "ABBREVIATION", unique = true, nullable = false)
    private String abbreviation;

    //The user can have several roles
    @ManyToMany
    @JoinTable(
        name = "USER_ROLE",
        joinColumns = @JoinColumn(name = "USER_ID"),
        inverseJoinColumns = @JoinColumn(name = "ROLE_ID"))
        private Set<Role> roles = new HashSet<>();
}

Now when I click on a role, I want the abbreviations of the users to be displayed. The native query for it is:

query = em.createNativeQuery("SELECT ABBREVIATION FROM dbtest.user WHERE ID IN"
                        + " (SELECT USER_ID FROM dbtest.user_role WHERE ROLE_ID = " + role.getId() + ")");

i have now created a criteria query of it, which always gives me an empty list.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Role> cq = cb.createQuery(Role.class);
Root<User> root = cq.from(User.class);

Subquery subquery = cq.subquery(Role.class);
Root subfrom = subquery.from(Role.class);

subquery.select(subfrom.get(Role_.id));
subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));

cq.multiselect(root.get(User_.abbreviation));
cq.where(cb.equal(root.get(User_.id), subquery));

query = em.createQuery(cq);
return query.getResultList();

What is here wrong? Can anyone help ?

Answer 1

First of all, set the correct types:

CriteriaBuilder cb = em.getCriteriaBuilder();
//CriteriaQuery<Role> cq = cb.createQuery(Role.class);
//The type of the data returned in multiselect.
CriteriaQuery<String> cq = cb.createQuery(String.class);
Root<User> root = cq.from(User.class);
//Add to compare
Join<User,Role> joinRole = root.join(User_.roles,JoinType.Inner);

//The type of the data returned in select.
//Subquery subquery = cq.subquery(Role.class);
Subquery<Long> subquery = cq.subquery(Long.class);
//Root subfrom = subquery.from(Role.class);
//We set the data type in the Root
Root<Role> subfrom = subquery.from(Role.class);

subquery.select(subfrom.get(Role_.id));
subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));

cq.multiselect(root.get(User_.abbreviation));
//cq.where(cb.equal(root.get(User_.id), subquery));
//To use in the where subquery, call the getSelection () method
cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));

query = em.createQuery(cq);
return query.getResultList();

Also, the where of the main query is an in not an equals, change this

cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));

by this

cq.where(joinRole.get(Role_.id).in(subquery.getSelection()));