Can JPQL's IN Clause Handle Collections Directly?

JPQL IN Clause: Unrolling Arrays, Lists, and Other Collections

In Java Persistence Query Language (JPQL), the IN clause allows you to specify a list of specific values for comparison. However, if you need to compare a collection of values instead of listing them out individually, you might wonder if there's a way to do it in JPQL.

Answer:

Yes, in JPA 2.0 and later, you can specify a Collection in the IN clause to be unrolled to its individual values. To do this:

1. Define the JPQL Query: Create a JPQL query with the IN clause and a named parameter, for example:

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

2. Create a Collection: Create a Java Collection (e.g., a List or Set) containing the values you want to check against.
3. Set the Parameter: Use the setParameter() method on the Query object to pass the Collection as the value for the named parameter:

Query q = em.createQuery(qlString, Item.class);
List<String> names = Arrays.asList("foo", "bar");
q.setParameter("names", names);

Example:

The following example in EclipseLink retrieves all Item objects whose names match either "foo" or "bar":

String qlString = "select item from Item item where item.name IN :names";
Query q = em.createQuery(qlString, Item.class);

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

q.setParameter("names", names);
List actual = q.getResultList();

assertNotNull(actual);
assertEquals(2, actual.size());

Note for Hibernate:

Prior to Hibernate 4, you need to surround the parameter in parentheses in the JPQL query:

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

However, this is a Hibernate-specific extension and is not part of the standard JPQL syntax.

The above is the detailed content of Can JPQL's IN Clause Handle Collections Directly?. For more information, please follow other related articles on the PHP Chinese website!