How Can I Use JPQL's IN Clause with Collections Effectively?

JPQL IN Clause for Collections

When retrieving data based on a set of values using an IN clause in SQL, it's convenient to pass an array or a collection as the values to be checked. JPQL also supports the IN clause, but it initially requires each parameter to be specified individually (e.g., "in (:in1, :in2, :in3)").

Solution for JPA 2.0 and Later

In JPA 2.0 and later, it's possible to pass a Collection as a parameter to the IN clause. Here's an example:

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();

Handling Collections in Hibernate 3.5.1 and Earlier

For Hibernate 3.5.1 and earlier, there's a slight quirk when using a Collection parameter in the IN clause. The following JPQL query is valid:

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

However, Hibernate doesn't handle this syntax correctly. To work around this issue, surround the parameter with parentheses:

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

This inconsistency is tracked as HHH-5126.

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