Setting a parameter as a list for an IN expression

Question

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.

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

score 52 · Accepted Answer · edited Mar 23 '18 at 14:46

52

Your JPQL is invalid, remove the brackets

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

edited Mar 23 '18 at 14:46

Benny Bottema

11,111
10
71
96

answered Nov 01 '11 at 13:58

James

17,965
11
91
146

1

Unfortunately not - I had the same error.. we have Java EE 5, therefore EJB 3.0. From the Spec: "JSR 220: Enterprise JavaBeans,Version 3.0 - Java Persistence API" Section I see that "In" Expression requires brackets.. but then I was getting IllegalArgumentException when I set the parameter as a List.. so I am using the awful hack below of expanding the IDs into a string of ORs (until I can get the proper JPA mappings in place with a DB View to avoid that). – Robert Mark Bram Jul 25 '12 at 01:43
Didn't work for me with .setParameter(), I had to use setParameterList() – lukas84 Mar 23 '17 at 10:45
How can a string like `var ids = "a1","b1",...;` be passed to java function and in turn to the IN operator? – Venky Jul 22 '17 at 13:50
3

@lukas84 That's because you're most likely using [hibernate Query](https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Query.html) and not [persistence Query](http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html) – Draken Sep 28 '17 at 11:13

score 20 · Answer 2 · answered Oct 13 '09 at 16:48

20

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.

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.

- Loren

answered Oct 13 '09 at 16:48

Loren_

2,597
3
20
29

3

Wrong. It depends upon implementation with JPA 1.0 implemented by Hibernate it work, but need brackets. – Guaido79 Jan 14 '15 at 18:11
1

This used to be a bug in Hibernate: https://hibernate.atlassian.net/browse/HHH-5126 – Kawu May 30 '17 at 14:38

score 15 · Answer 3 · edited Sep 28 '17 at 11:24

15

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

I had JavaEE jar as well as jpa 2 jar(javax.persistence*2*) in the class path. Removed the JavaEE from the class path.
I was using something like " idItm IN ( :itemIds ) " which was throwing the exception :

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

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

edited Sep 28 '17 at 11:24

Draken

3,134
13
34
54

answered Jul 07 '11 at 10:30

dillip

151
1
3

thanks a lot @dillip you saved my day. I just had to delete the parentheses around the parameter – Sofiane Jun 10 '16 at 13:48

score 2 · Answer 4 · answered Jul 25 '13 at 06:10

2

Simply, the parameter will be List and set it as

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

This works for JPA 1.0

answered Jul 25 '13 at 06:10

Abhishek Chatterjee

1,962
2
23
31

score 2 · Answer 5 · answered Jan 29 '19 at 11:02

2

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

answered Jan 29 '19 at 11:02

Atul Jain

1,035
2
16
24

score 0 · Answer 6 · edited Oct 28 '21 at 21:09

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

    /** 
     * @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 &gt size; i++) {
            queryString = queryString.concat(" "+field+" = '"+collection.get(i)+"'");
            if(i &gt size-1) {
                queryString = queryString.concat(" OR");
            }
        }
        queryString = queryString.concat(" )");
        return queryString;
    }

score 0 · Answer 7 · edited Nov 30 '15 at 15:33

0

Use NamedQuery instead:

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

edited Nov 30 '15 at 15:33

Daniel Olszewski

13,995
6
58
65

answered Mar 24 '15 at 17:18

toquart

394
2
8

score -1 · Answer 8 · answered Jul 25 '12 at 01:33

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

/**
 * @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')

Repeat after me: "I will never ever use StringBuilders or other similar techniques to build SQL queries as this enables SQL injection. I will only use prepared statements and such." — siebz0r, Aug 28 '12 at 06:01
Thank you for the warning about potential vulnerabilities with SQL @siebz0r. However I do not believe this is valid here. Prepared statements can't be used as the number of args are not known and the version of JPA doesn't allow list args. Also, the arguments here are not from public input and they are cast as Integer objects. — Robert Mark Bram, Aug 28 '12 at 21:08
I understand the risks are limited, but still, weird things can happen. `java.sql.PreparedStatement` can be used for such rare cases. This interface supports collections as a parameter. — siebz0r, Aug 28 '12 at 21:39

score -3 · Answer 9 · answered Nov 01 '11 at 13:37

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

No, no, no. This is will enable SQL injection possibilities. — siebz0r, Aug 28 '12 at 05:58

Setting a parameter as a list for an IN expression

9 Answers9

Linked

Related