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

SqlOrmJpaJpqlNamed Query

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

I'm not sure for JPA 1.0 but you can pass a Collection in JPA 2.0:

String qlString = "select item from Item item where 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();

assertEquals(2, actual.size());

Tested with EclipseLInk. With Hibernate 3.5.1, you'll need to surround the parameter with parenthesis:

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

But this is a bug, the JPQL query in the previous sample is valid JPQL. See HHH-5126.

Solution 2 - Sql

The oracle limit is 1000 parameters. The issue has been resolved by hibernate in version 4.1.7 although by splitting the passed parameter list in sets of 500 see JIRA HHH-1123

Solution 3 - Sql

I had a problem with this kind of sql, I was giving empty list in IN clause(always check the list if it is not empty). Maybe my practice will help somebody.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBernd HaugView Question on Stackoverflow
Solution 1 - SqlPascal ThiventView Answer on Stackoverflow
Solution 2 - SqlAshish ThukralView Answer on Stackoverflow
Solution 3 - SqlVahan YeghyanView Answer on Stackoverflow