多个属性上的Hibernate IN子句

Question

我有一个包含列user_id、activity_id和comments的表user_activities。activity_id和user_id都是外键，活动表具有另一个属性activity_name。要获取一行，使用特定的user_id和activity_name，我可以使用：

FROM UserActivityMapping AS mapping
    INNER JOIN mapping.activityId AS activity
    WHERE userId = <something> AND activity.activityName = <something>

到目前为止，一切都很好。现在，给定<UserId, ActivityName>的Pairs的列表，我想删除所有这样的行。这个清单可能很大。。像600-700件大的东西。目前，我可以生成一个混乱的查询：

DELETE ...
    WHERE (userId = userId[0] AND activity.activityName = activityName[0]) OR
          (userId = userId[1] AND activity.activityName = activityName[1]) OR
          ...

这显然不是最好的方法。我该怎么做？像这样的东西会很神奇：

"WHERE (userId, activityId) in :userActivityList".setParameterList(
    new List<Pair<String, String>>()
);

非常感谢您的帮助。

Answer 1

我以前遇到过类似的问题，并编写了一个相当简单的函数来动态构建hql

private String buildInClause(List<Integer> keys)  {
   String inClause = "object.id in (";
   for (Integer key : keys) {
     inClause += key + ',";
   }
   inClause = inClause.substring(0,inClause.length -1) + ")";  // strip off last comma and add a closing paren.
   return inClause;
}

对于您的问题，您可以稍微修改这种方法

inClause = " userId || '-' || activity.activityName in (";
// loop over your pairs however they are passed in
inClause += "'" + userId + "-" + activityName + "',";
// strip off trailing coman and add closing paren
inClause = inClause.substring(0,inClause.length -1) + ")";  
// end loop