Adding IN clause List to a JPA Query

java

jpa

query-optimization

hibernate

byAnton Shumikhin·Jan 28, 2025

TypedQuery<MyEntity> query = entityManager.createQuery(
    "SELECT e FROM MyEntity e WHERE e.myField IN :myList", MyEntity.class);
// Are you on the list, myList? Let's find out
query.setParameter("myList", myList);
List<MyEntity> result = query.getResultList(); // Get' em all!

Simply bind your List using setParameter where :myList signifies your list items in the IN clause of your JPQL query. It's clean, it's safe, it performs well!

Playing safe with Named Queries

JPQL Named Queries to the rescue! They are precompiled and parsed just once, lending your application better performance:

In your Entity class:

@Entity
@NamedQuery(name = "MyEntity.findByMyField",
            query = "SELECT e FROM MyEntity e WHERE e.myField IN :myList")
public class MyEntity { /* Insert entity content here */}

Fire off your NamedQuery like this:

TypedQuery<MyEntity> namedQuery = entityManager.createNamedQuery("MyEntity.findByMyField", MyEntity.class);
// myList, you've got a job to do!
namedQuery.setParameter("myList", myList);
// Presto! Magic happens here
List<MyEntity> result = namedQuery.getResultList();

Beware of the giant List

Large lists: good for party invitees, not so much for queries. Databases might grumble if you pass exceedingly large lists. But who said we can't find a workaround?

int batchSize = 1000; // Feel free to adjust this baby
List<MyEntity> allResults = new ArrayList<>();
for (int i = 0; i < myList.size(); i += batchSize) {
    List<MyEntity> batchList = myList.subList(i, Math.min(i + batchSize, myList.size()));
    query.setParameter("myList", batchList);
    // A small bite for the database, a mighty leap for your Query's performance
    allResults.addAll(query.getResultList());
}

Cooking up good code with old ingredients

It's all fun and games until an older version of Hibernate spoils the party. Something like this bug could pop up! Well, we're all about solutions, aren't we?

List<MyEntity> result = entityManager.createQuery(
    "SELECT e FROM MyEntity e WHERE e.myField IN (:myList)", MyEntity.class)
    .setParameter("myList", myList.isEmpty() ? Arrays.asList("null") : myList)
    .getResultList();

Poof! Our magic wand handles an empty list scenario by adding a harmless placeholder value.

The other side of the coin - Criteria API

If JPQL feels too last season, the JPA Criteria API is a flexible and typesafe way to create queries programmatically:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
Root<MyEntity> root = cq.from(MyEntity.class);
cq.select(root).where(root.get("myField").in(myList));
List<MyEntity> result = entityManager.createQuery(cq).getResultList();

It shines when queries are being cooked up during runtime based on dynamic conditions.

Keeping it clean in syntax and efficiency

When crafting JPQL, the minor details matter. Stick to JPQL syntax and the optimization gods shall reward you! Also:

Love TypedQuery. It adds type safety.
Fancy gathered wisdom? Use NamedQuery definitions over ad-hoc ones.
Keeping database access to a minimum is a good practice. Parameterizing queries and fetching results in batches do just that.