JPA EntityManager createQuery() with IN not working

Question

This is failing:

List<String> names = new ArrayList<String>();
names.add("sold");
Query query = em.createQuery("FROM PropField propField WHERE propField.name IN (?)");
query.setParameter(1, names);
List<PropField> fields = query.getResultList();

And so is this:

List<String> names = new ArrayList<String>();
names.add("sold");
Query query = em.createQuery("FROM PropField propField WHERE propField.name IN (?)");
query.setParameter(1, names.toArray());
List<PropField> fields = query.getResultList();

This, too:

List<String> names = new ArrayList<String>();
names.add("sold");
Query query = em.createQuery("FROM PropField propField WHERE propField.name IN ?");
query.setParameter(1, names.toArray());
List<PropField> fields = query.getResultList();

And every other permutation of the above. Checked the docs and it says the first option should work. Here's the exception for the top one.

java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String
at org.hibernate.type.StringType.toString(StringType.java:67)

Hibernate's HQL uses setParameterList, but trying to stick with straight JPA here.

Answer 1

From the JPA 1.0 specification:

4.6.4.1 Positional Parameters

The following rules apply to positional parameters.

• Input parameters are designated by the question mark (?) prefix followed by an integer. For example: ?1.
• Input parameters are numbered starting from 1.
  Note that the same parameter can be used more than once in the query string and that the ordering of the use of parameters within the query string need not conform to the order of the positional parameters.

4.6.4.2 Named Parameters

A named parameter is an identifier that is prefixed by the ":" symbol. It follows the rules for identifiers defined in Section 4.4.1. Named parameters are case sensitive.

Example:

SELECT c
FROM Customer c
WHERE c.status = :stat

Section 3.6.1 describes the API for the binding of named query parameters

So either use (with a named parameter):

List<String> names = Arrays.asList("sold");
Query query = em.createQuery("FROM PropField propField WHERE propField.name IN (:names)");
query.setParameter("names", names)
List<PropField> fields = query.getResultList();

Or (with a positional parameter):

List<String> names = Arrays.asList("sold");
Query query = em.createQuery("FROM PropField propField WHERE propField.name IN (?1)");
query.setParameter(1, names)
List<PropField> fields = query.getResultList();

Both tested with Hibernate EM 3.4.0.GA.

Note that being forced to use parenthesis to surround the parameter of the IN clause is a bug (at least in JPA 2.0) as outlined in this previous answer.

Answer 2

I ran into a similar problem with Hibernate using a JPA named query with an IN clause. I got it working with the syntax: "propField.name IN (?1)"

Also see: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4922

and http://opensource.atlassian.com/projects/hibernate/browse/HHH-5126