JPA CriteriaQuery - How to use IN comparison operator

Question

I have two java objects.

1. User(every user has an index column)
2. Address( every address has an user_index column too)

I have a List of all the users index list, usersIndexList as given input and I want to fetch all of the address objects based on this usersIndexList. I found an example on another thread. And tried to follow it but it does not work.

JPA CriteriaBuilder - How to use "IN" comparison operator

My code:

List<String> usersIndexList= new ArrayList<String> ();
for (User u : usersList) {
    usersIndexList.add(u.getIndex());
}

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<User> subQuery = criteriaBuilder.createQuery(User.class);
    Root<User> fromUser= subQuery.from(User.class);

    Expression<String> exp = fromUser.get("user_index");
    Predicate predicate = exp.in(usersIndexList);

    subQuery.where(predicate);

    TypedQuery<User> query = getEntityManager().createQuery(subQuery);

    return query.getResultList();

But this query is not returning the desired result :(

Can someone please tell me, where I am doing wrong or give me an alternate solutions if its possible via nativequery or namedquery or any other way

Answer 1

As per your question, you want to fetch all of the address objects based on this usersIndexList. But in your code you are selecting User objects, not the Address. Is my understanding Correct? If yes, then please change your root to Address as below -

List<String> usersIndexList= new ArrayList<String> ();
for (User u : usersList) {
    usersIndexList.add(u.getIndex());
}

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<Address> subQuery = criteriaBuilder.createQuery(Address.class);
    Root<Address> fromAddress= subQuery.from(Address.class);

    Expression<String> exp = fromAddress.get("user_index");
    Predicate predicate = exp.in(usersIndexList);

    subQuery.where(predicate);

    TypedQuery<Address> query = getEntityManager().createQuery(subQuery);

    return query.getResultList();