JPA @namedQuery with "IN" expression

Question

Im having the following namedQuery:

@NamedQuery(name = Department.getDepartmentsByIds, query = "SELECT tbl FROM Department tbl where tbl.id in (:departmentsIds)")

I would like to pass the parameter: departmentsIds = "1,2,3" like this:

query.setParameter("departmentsIds","1,2,3");

but i get an error:

java.lang.IllegalArgumentException: Parameter value [1,2,3] was not matching type [java.lang.Long]

any ideas why?

Possible duplicate: http://stackoverflow.com/questions/2772305/jpql-in-clause-java-arrays-or-lists-sets — perissf, Aug 14 '13 at 10:06

score 6 · Accepted Answer · answered Aug 14 '13 at 10:07

Pass a List to the setParameter method instead of a String. The generic type argument of List should correspond with the type of your departmentIds field.

List<Integer> ids = new ArrayList<Integer>(); //this should be your id column's type
ids.add(1);
ids.add(2);
ids.add(3);
query.setParameter("departmentsIds",ids);

score 0 · Answer 2 · answered Aug 14 '13 at 10:19

The column id in Department is of type java.lang.Long and in your setParameter you are trying to set String [1,2,3] thats why you see java.lang.IllegalArgumentException, the correct way to implements is as @Kevin suggested create a List of Long type and add your arguments.

JPA @namedQuery with "IN" expression

2 Answers2