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

问题:

Is there a possiblity in JPA 2.0 to set a collection for in-clause in jpql-query? (I'm using EclipseLink)

The next 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 another way to do it?

回答1:

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

4.6.9 In Expressions

The syntax for the use of 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

...

So according to the specification, the correct syntax when passing a collection_valued_input_parameter is without parenthesis:

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

And this works with EclipseLink.

回答2:

Using EclipseLink, you need to use a name to your paramether.

Ex.:

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 clause "in". It was tested in EclipseLink 2.5.1.