Java 将参数设置为 IN 表达式的列表

Setting a parameter as a list for an IN expression

提问by perilandmishap

Whenever I try to set a list as a parameter for use in an IN expression I get an Illegal argument exception. Various posts on the internet seem to indicate that this is possible, but it's certainly not working for me. I'm using Glassfish V2.1 with Toplink.

每当我尝试将列表设置为用于 IN 表达式的参数时，我都会收到非法参数异常。互联网上的各种帖子似乎表明这是可能的，但它肯定对我不起作用。我正在使用带有 Toplink 的 Glassfish V2.1。

Has anyone else been able to get this to work, if so how?

有没有其他人能够让这个工作，如果是这样，如何？

here's some example code:

这是一些示例代码：

List<String> logins = em.createQuery("SELECT a.accountManager.loginName " +
    "FROM Account a " +
    "WHERE a.id IN (:ids)")
    .setParameter("ids",Arrays.asList(new Long(1000100), new Long(1000110)))
    .getResultList();

and the relevant part of the stack trace:

以及堆栈跟踪的相关部分：

java.lang.IllegalArgumentException: You have attempted to set a value of type class java.util.Arrays$ArrayList for parameter accountIds with expected type of class java.lang.Long from query string SELECT a.accountManager.loginName FROM Account a WHERE a.id IN (:accountIds).
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.setParameterInternal(EJBQueryImpl.java:663)
at oracle.toplink.essentials.internal.ejb.cmp3.EJBQueryImpl.setParameter(EJBQueryImpl.java:202)
at com.corenap.newtDAO.ContactDaoBean.getNotificationAddresses(ContactDaoBean.java:437)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1011)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:175)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2920)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4011)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:203)
... 67 more

采纳答案by James

Your JPQL is invalid, remove the brackets

您的 JPQL 无效，请删除括号

List<String> logins = em.createQuery("SELECT a.accountManager.loginName " +
    "FROM Account a " +
    "WHERE a.id IN :ids")
    .setParameter("ids",Arrays.asList(new Long(1000100), new Long(1000110)))
    .getResultList();

回答by perilandmishap

I found the answer, providing a list as a parameter is not supported in JPA 1.0; however, it is supported in JPA 2.0.

我找到了答案，在 JPA 1.0 中不支持提供列表作为参数；但是，它在 JPA 2.0 中受支持。

The default persistence provider for Glassfish v2.1 is Toplink which implements JPA 1.0, to get JPA 2.0 you need EclipseLink which is the default for the Glassfish v3 preview or can be plugged into v2.1.

Glassfish v2.1 的默认持久性提供程序是 Toplink，它实现了 JPA 1.0，要获得 JPA 2.0，您需要 EclipseLink，它是 Glassfish v3 预览版的默认值，或者可以插入 v2.1。

- Loren

- 罗兰

回答by perilandmishap

Oh, and if you can't use EclipseLink for some reason then here is a method you can use to add the needed bits to your query. Just insert the resulting string into your query where you would put "a.id IN (:ids)".

哦，如果由于某种原因您不能使用 EclipseLink，那么您可以使用这里的方法将所需的位添加到您的查询中。只需将结果字符串插入您的查询中，您将在其中放置“a.id IN (:ids)”。

     /** 
     /* @param field The jpql notation for the field you want to evaluate
     /* @param collection The collection of objects you want to test against
     /* @return Jpql that can be concatenated into a query to test if a feild is in a 
     */
collection of objects
    public String in(String field, List collection) {
        String queryString = new String();
        queryString = queryString.concat(" AND (");
        int size = collection.size();
        for(int i = 0; i > size; i++) {
            queryString = queryString.concat(" "+field+" = '"+collection.get(i)+"'");
            if(i > size-1) {
                queryString = queryString.concat(" OR");
            }
        }
        queryString = queryString.concat(" )");
        return queryString;
    }

回答by dillip

Hope this helps some one. I have faced the issue and did the following to resolve (using eclipselink 2.2.0)

希望这对某人有所帮助。我遇到了这个问题并做了以下解决（使用 eclipselink 2.2.0）

1. I had JavaEE jar as well as jpa 2 jar(javax.persistence*2*) in the class path. Removed the JavaEE from the class path.

2. I was using something like " idItm IN ( :itemIds ) "which was throwing the exception :

1. 我在类路径中有 JavaEE jar 和 jpa 2 jar(javax.persistence*2*)。从类路径中删除了 JavaEE。

2. 我正在使用类似" idItm IN ( :itemIds ) "抛出异常的东西 ：

type class java.util.ArrayList for parameter itemIds with expected type of class java.lang.String from query string

类型 java.util.ArrayList 的参数 itemIds 与来自查询字符串的类 java.lang.String 的预期类型

Solution: I just changed the in condition to " idItm IN :itemIds ", i.e. I removed the brackets ().

解决方案：我只是将 in 条件更改为 " idItm IN :itemIds "，即我删除了括号 ()。

回答by Szymon Tarnowski

You can also try this syntax.

你也可以试试这个语法。

static public String generateCollection(List list){
    if(list == null || list.isEmpty())
        return "()";
    String result = "( ";
    for(Iterator it = list.iterator();it.hasNext();){
        Object ob = it.next();
        result += ob.toString();
        if(it.hasNext())
            result += " , ";
    }
    result += " )";
    return result;
}

And put into query, "Select * from Class where field in " + Class.generateCollection(list);

并放入查询中， "Select * from Class where field in " + Class.generateCollection(list);

回答by Robert Mark Bram

Try this code instead of the one supplied by @Szymon Tarnowski to add the OR list.. warningif you have hundreds of IDs though, you might break whatever limit is in place regarding the max length of a query.

尝试使用此代码而不是由 @Szymon Tarnowski 提供的代码来添加 OR 列表...警告，如果您有数百个 ID，则可能会破坏有关查询最大长度的任何限制。

/**
 * @param field
 *           The jpql notation for the field you want to evaluate
 * @param collection
 *           The collection of objects you want to test against
 * @return Jpql that can be concatenated into a query to test if a feild is
 *         in a collection of objects
 */
public static String in(String field, List<Integer> idList) {
  StringBuilder sb = new StringBuilder();
  sb.append(" AND (");
  for(Integer id : idList) {
    sb.append(" ").append(field).append(" = '").append(id).append("'").append(" OR ");
  }
  String result = sb.toString();
  result = result.substring(0, result.length() - 4); // Remove last OR
  result += ")";
  return result;
}

To test this:

要测试这个：

public static void main(String[] args) {
  ArrayList<Integer> list = new ArrayList<Integer>();
  list.add(122);
  list.add(132);
  list.add(112);
  System.out.println(in("myfield", list));
}

Which gave output: AND ( myfield = '122' OR myfield = '132' OR myfield = '112')

这给出了输出： AND ( myfield = '122' OR myfield = '132' OR myfield = '112')

回答by Abhishek Chatterjee

Simply, the parameter will be List and set it as

简单地说，参数将是 List 并将其设置为

"...WHERE a.id IN (:ids)")
.setParameter("ids", yourlist)

This works for JPA 1.0

这适用于 JPA 1.0

回答by toquart

Use NamedQuery instead:

改用 NamedQuery：

List<String> logins = em.createNamedQuery("Account.findByIdList").setParameter("ids", Arrays.asList(new Long(1000100), new Long(1000110))).getResultList();

Add the named query to your entity

将命名查询添加到您的实体

@NamedQuery(name = "Account.findByIdList", query = "SELECT a.accountManager.loginName FROM Account a WHERE a.id IN :ids")

回答by Atul Jain

IN :idsinstead of IN (:ids)- will work.

IN :ids而不是 IN (:ids)- 会起作用。