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

问题描述

你能帮我解释一下如何将以下代码转换为使用标准生成器的in操作符？
我需要使用列表/数组的用户名来过滤使用in。

我也尝试使用JPA CriteriaBuilder进行搜索 - in方法但无法找到好的结果。
所以，如果你能给我这个主题的参考网址，我会很感激。

以下是我的代码：

  // usersList是我需要放入IN运算符的用户列表
 $ b $ CriteriaBuilder builder = getJpaTemplate（）。getEntityManagerFactory（）。getCriteriaBuilder（）; 
 CriteriaQuery< ScheduleRequest> criteria = builder.createQuery（ScheduleRequest.class）; 
 
 Root< ScheduleRequest> scheduleRequest = criteria.from（ScheduleRequest.class）; 
 criteria = criteria.select（scheduleRequest）; 
 
列表<谓词> 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。

 从ScheduleRequest中选择generatedAlias0作为generatedAlias0其中（generatedAlias0.createdBy =：param0）和（generatedAlias0.createdBy =：param1）order by generatedAlias0.trackingId asc 
  

解决方案

如果我理解的很好，你想用 User 加入 ScheduleRequest ，并将在实体用户中的 userName 属性的子句中。

我需要在这个模式上工作一下。但是你可以尝试一下这个技巧，它比你发布的代码更可读，并且避免了 Join 部分（因为它处理 Join 条件查询以外的逻辑）。

  List< String> myList = new ArrayList< String> （）; 
 for（User u：usersList）{
 myList.add（u.getUsername（））; 
} 
表达式< String> exp = scheduleRequest.get（createdBy）; 
谓词谓词= exp.in（myList）; 
 criteria.where（predicate）; 
  

为了编写更多类型安全的代码，您也可以使用Metamodel替换此行： p>

 表达式< String> exp = scheduleRequest.get（createdBy）; 
  

用这个：

 表达式来;字符串> exp = scheduleRequest.get（ScheduleRequest_.createdBy）; 
  

如果有效，那么您可以尝试添加加入逻辑放入 Criteria Query 中。但现在我无法测试它，所以我更喜欢看别人是否想尝试。

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 

解决方案

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.

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