Can you please help me how to convert the following codes 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 codes:
//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
Best Answer
If I understand well, you want to Join
ScheduleRequest
withUser
and apply thein
clause to theuserName
property of the entityUser
.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 theJoin
logic outside the Criteria Query).In order to write more type-safe code you could also use Metamodel by replacing this line:
with this:
If it works, then you may try to add the
Join
logic into theCriteria Query
. But right now I can't test it, so I prefer to see if somebody else wants to try.