JPA CriteriaBuilder - 如何使用“IN"比较运算符 [英] JPA CriteriaBuilder - How to use "IN" comparison operator

问题描述

您能帮我如何将以下代码转换为使用条件构建器的in"运算符吗?我需要通过使用in"的用户名列表/数组进行过滤.

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".

我也尝试使用 JPA CriteriaBuilder - "in" 方法进行搜索，但找不到好的结果.因此，如果您能给我提供此主题的参考 URL，我也将不胜感激.谢谢.

这是我的代码:

//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();

内部查询字符串被转换为下面的，所以我没有得到两个用户创建的记录，因为它使用了AND".

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 

推荐答案

如果我明白，你想加入 ScheduleRequest 和 User 并应用 in 子句添加到实体 User 的 userName 属性.

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

我需要在这个架构上做一些工作.但是你可以试试这个技巧，它比你发布的代码更具可读性，并且避免了 Join 部分(因为它处理 Criteria Query 之外的 Join 逻辑).

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);

为了编写更多类型安全的代码，您还可以通过替换这一行来使用 Metamodel:

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

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

这样:

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

如果有效，那么您可以尝试将Join 逻辑添加到Criteria Query 中.但现在我无法测试它，所以我更喜欢看看其他人是否想尝试.

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.

这篇关于JPA CriteriaBuilder - 如何使用“IN"比较运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！