How to set collection items for in-clause in jpql?

Question

Is it possible in JPA 2.0 to install a set for in-clause in jpql-query? (I am using EclipseLink)

The following example fails:

TypedQuery<Person> q = em.createQuery("select p from Person p where p.name in (?1)", Person.class); List<String> names = Arrays.asList(new String[] { "Bill Gates", "Steve Jobs" }); // THIS FAILS q.setParameter(1, names); List<Person> persons = q.getResultList(); for (Person p: persons) { System.out.println(p.getName()); } 

Is there any other way to do this?

Answer 1

Here is what the JPA 2.0 specification says about IN expressions:

4.6.9 In expressions

The syntax for using the comparison operator [NOT] IN in a conditional expression is as follows:

 in_expression ::= {state_field_path_expression | type_discriminator} [NOT] IN { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter } in_item ::= literal | single_valued_input_parameter 

...

Therefore, according to the specification, the correct syntax when passing the collection_valued_input_parameter parameter without parentheses is:

 select p from Person p where p.name in ?1 

And it works with EclipseLink.

Answer 2

Using EclipseLink, you need to use a name for your parameterization.

Example:.

 TypedQuery<Person> q = em.createQuery("select p from Person p where p.name in :names", Person.class); List<String> names = Arrays.asList(new String[] { "Bill Gates", "Steve Jobs" }); q.setParameter("names", names); 

The symbol "?" does not work using the "in" clause. It has been tested in EclipseLink 2.5.1.