JPQL IN clause: Java-Arrays (or Lists, Sets…)?

Question

I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "IN" clause. JPQL allows for IN, but it seems to require me to specify every single parameter to IN directly (as in, "in (:in1, :in2, :in3)").

Is there some way to specify an array or a list (or some other container) that should be unrolled to the values of an IN clause?

Answer 1

You can try passing a Collection in JPA 2.0:

String qlString = "select item from Item item where item.name IN :names"; 

Query q = em.createQuery(qlString, Item.class);

List<String> names = Arrays.asList("foo", "bar");

q.setParameter("names", names);

List<Item> actual = q.getResultList();

assertNotNull(actual);

assertEquals(2, actual.size());

Tested with EclipseLInk. With Hibernate 3.5.1, you will have to surround the parameter with parenthesis:

String qlString = "select item from Item item where item.name IN (:names)";

But the above code occurs as a bug, the JPQL query in the previous sample is valid JPQL. 

For more information, refer to this:  HHH-5126.