JPA CriteriaBuilder - How to use "IN" comparison operator

Question

Can you please help me how to convert the following code to using "in" operator of criteria builder? I need to filter by using list/array of usernames using "in".

I also tried to search using JPA CriteriaBuilder - "in" method but cannot find good result. So I would really appreciate also if you can give me reference URLs for this topic. Thanks.

Here is my code:

//usersList is a list of User that I need to put inside IN operator 

CriteriaBuilder builder = getJpaTemplate().getEntityManagerFactory().getCriteriaBuilder();
CriteriaQuery<ScheduleRequest> criteria = builder.createQuery(ScheduleRequest.class);

Root<ScheduleRequest> scheduleRequest = criteria.from(ScheduleRequest.class);
criteria = criteria.select(scheduleRequest);

List<Predicate> params = new ArrayList<Predicate>();

List<ParameterExpression<String>> usersIdsParamList = new ArrayList<ParameterExpression<String>>();

for (int i = 0; i < usersList.size(); i++) {
ParameterExpression<String> usersIdsParam = builder.parameter(String.class);
params.add(builder.equal(scheduleRequest.get("createdBy"), usersIdsParam) );
usersIdsParamList.add(usersIdsParam);
}

criteria = criteria.where(params.toArray(new Predicate[0]));

TypedQuery<ScheduleRequest> query = getJpaTemplate().getEntityManagerFactory().createEntityManager().createQuery(criteria);

for (int i = 0; i < usersList.size(); i++) {
query.setParameter(usersIdsParamList.get(i), usersList.get(i).getUsername());
}

List<ScheduleRequest> scheduleRequestList = query.getResultList();

The internal Query String is converted to below, so I don't get the records created by the two users, because it is using "AND".

select generatedAlias0 from ScheduleRequest as generatedAlias0 where ( generatedAlias0.createdBy=:param0 ) and ( generatedAlias0.createdBy=:param1 ) order by generatedAlias0.trackingId asc

perissf · Accepted Answer · 2012-02-19T18:19:48.810

112

If I understand well, you want to Join ScheduleRequest with User and apply the in clause to the userName property of the entity User.

I'd need to work a bit on this schema. But you can try with this trick, that is much more readable than the code you posted, and avoids the Join part (because it handles the Join logic outside the Criteria Query).

List<String> myList = new ArrayList<String> ();
for (User u : usersList) {
    myList.add(u.getUsername());
}
Expression<String> exp = scheduleRequest.get("createdBy");
Predicate predicate = exp.in(myList);
criteria.where(predicate);

In order to write more type-safe code you could also use Metamodel by replacing this line:

Expression<String> exp = scheduleRequest.get("createdBy");

with this:

Expression<String> exp = scheduleRequest.get(ScheduleRequest_.createdBy);

If it works, then you may try to add the Join logic into the Criteria Query. But right now I can't test it, so I prefer to see if somebody else wants to try.

edited Feb 19 '12 at 18:19

answered Feb 17 '12 at 08:44

perissf

15,979
14
80
117

Hi perissf, thanks for your reply. Can i ask please, what is "ScheduleRequest_"? How do i create it? – Jemru Feb 17 '12 at 09:49
2

It's a Metamodel class auto-generated by your JPA provider. If you are using Hibernate, check out this link http://docs.jboss.org/hibernate/jpamodelgen/1.0/reference/en-US/html_single/ – perissf Feb 17 '12 at 09:52
Reading my pseudo-code I see a possible type discrepancy. Are you joining with userNames (String) or userIds (Integer / Long)? Pls let me know so that I can update my answer – perissf Feb 17 '12 at 09:55
Hi perissf, i'm using String userNames. I'm just following our architecture which don't have the "_" underscore bean, so I'm not sure how to do it. But will still try to follow your link. thanks. – Jemru Feb 19 '12 at 16:09
thank you perissf, it worked for me. though I havent done the type-safe. I don't know how to auto-generate the class ScheduleRequest_. thank you very much! :) – Jemru Feb 20 '12 at 08:27
Good to know! For the auto-generated classes, that are called Metamodel, have you read the link I have given to you? If you have problems on that, make a new question with the details – perissf Feb 20 '12 at 08:29
I am getting cannot cast to string error again and again using this – user1735921 Dec 28 '18 at 10:22
It works for my case, thanks a lot. I have struggled for this problem the whole morning! – Zeyu Wang Oct 29 '19 at 10:45
This doesn't work. You can't use `in(List)`. – gene b. Jun 26 '20 at 17:43

score 21 · Answer 2 · answered Feb 17 '12 at 05:05

Not a perfect answer though may be code snippets might help.

public <T> List<T> findListWhereInCondition(Class<T> clazz,
            String conditionColumnName, Serializable... conditionColumnValues) {
        QueryBuilder<T> queryBuilder = new QueryBuilder<T>(clazz);
        addWhereInClause(queryBuilder, conditionColumnName,
                conditionColumnValues);
        queryBuilder.select();
        return queryBuilder.getResultList();

    }


private <T> void addWhereInClause(QueryBuilder<T> queryBuilder,
            String conditionColumnName, Serializable... conditionColumnValues) {

        Path<Object> path = queryBuilder.root.get(conditionColumnName);
        In<Object> in = queryBuilder.criteriaBuilder.in(path);
        for (Serializable conditionColumnValue : conditionColumnValues) {
            in.value(conditionColumnValue);
        }
        queryBuilder.criteriaQuery.where(in);

    }

Thanks a lot @gbagga, this thing helped me a lot! the above method (exp.in(myList)) will not work when you are using "CriteriaBuilder". Your method is the one that should be used for "IN" clause when using criteriaBuilder. Kudos! — roneo, Sep 04 '15 at 15:47

JPA CriteriaBuilder - How to use "IN" comparison operator

2 Answers2

Linked