Jpql IN clause: Java-Arrays (or Lists, Sets...)

java

prompt-engineering

best-practices

sql

byAlex Kataev·Feb 27, 2025

To use Java collections like Lists in a JPQL IN clause, bind them directly into the query. Define a named parameter and assign it your collection, then run your query.

List<Long> ids = ...;
Query query = entityManager.createQuery("SELECT e FROM Entity e WHERE e.id IN :ids").setParameter("ids", ids);
List<Entity> result = query.getResultList();

The setParameter function connects the ids with :ids in the query, easily translating it into an SQL IN clause.

Working with various collection types

Although Lists are frequently used with IN clauses, other collection types like Sets or Arrays can be used as well. JPA handles these collections correctly keeping your code neat.

Set<String> names = ...;
Query query = entityManager.createQuery("SELECT e FROM Entity e WHERE e.name IN :names").setParameter("names", names);

If you're dealing with Arrays, convert them to a list using Arrays.asList(yourArray) first:

String[] namesArray = ... ;
List<String> namesList = Arrays.asList(namesArray);
Query query = entityManager.createQuery("SELECT e FROM Entity e WHERE e.name IN :names").setParameter("names", namesList);

Circumventing potential pitfalls

Heading off empty collections

Ensure your collections aren't empty when using IN clauses; you wouldn't want to generate syntactically invalid queries:

if (ids.isEmpty()) {
    // Maybe go on a break, your query needs more data.
}

Heeding Hibernate

In Hibernate 3.5.1, you might need parentheses around IN parameters:

"SELECT e FROM Entity e WHERE e.id IN (:ids)"

Additionally, the infamous Hibernate bug HHH-5126 might bring some drama to your parameter handling within the IN clause.

Dealing with Oracle's parameters limit

If the Oracle database is your stage, remember there's a character limit of 1000 for an IN clause. You could split your list and run multiple queries or rely on Hibernate versions beyond 4.1.7, which automatically split parameter lists larger than 500.