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

Question

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?

score 238 · Accepted Answer · edited Jan 29 '18 at 21:08

238

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

assertNotNull(actual);
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 item.name IN (:names)";

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

edited Jan 29 '18 at 21:08

Arend v. Reinersdorff

4,110
2
36
40

answered May 08 '10 at 10:25

Pascal Thivent

562,542
136
1,062
1,124

5

is there a max number of names to use in "in clause"? – Gondim May 03 '11 at 13:37
3

Aforementioned bug in Hibernate seems to be fixed in version 3.6.1 – Denys Kniazhev-Support Ukraine Jun 02 '11 at 15:55
1

@pringlesinn the number of values in an IN-clause depends on your DBMS – Tim Büthe Oct 12 '16 at 08:32
works in JPA 1.0 (and its no mandatory surround with parenthesis, but for readability you should) – Javier Larios Mar 16 '18 at 16:08
what if you whant to => item.name in ( like :names) – dzgeek Oct 23 '19 at 13:47
1

Not to future self: If confused why it is still not working (e.g. `java.sql.SQLSyntaxErrorException: ORA-00936: missing expression`), do not forget, that the "in ()" clause is INVALID if the list is empty. – Pfiver May 04 '21 at 06:04
`and its no mandatory surround with parenthesis, but for readability you should` actually my query failed saying it expected String and got ArrayList when I used parenthesis. – mjaggard Nov 24 '21 at 13:51

score 3 · Answer 2 · answered Jun 27 '13 at 18:09

3

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

answered Jun 27 '13 at 18:09

Ashish Thukral

1,445
1
16
26

2

Unfortunately this was not resolved. The ticket was marked as resolved, but the problem (as shown by the comments) was not fixed by the Hibernate team. – Druckles Jul 06 '18 at 15:17
@Druckles um where? I don't see any comments older than 2016. And those were only two comments that say practically nothing other than the usual internet 'help plz!!!11!!!!'. You have given no reason to trust the bug report as resolved. – searchengine27 Jan 07 '20 at 21:19
@searchengine27 I said the report was *not* resolved, despite it being marked as resolved. The resolution was, as reported by Steve Ebersole: "The resolution to this is that we are simply going to warn users via logging when this condition is detected." The comments by Noel Trout back in 2012 expand on why this is not sufficient. – Druckles Jan 07 '20 at 23:18
In other words, this answer is wrong or, at best, misleading. – Druckles Jan 07 '20 at 23:19

score 3 · Answer 3 · answered Nov 13 '20 at 12:42

3

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.

answered Nov 13 '20 at 12:42

Vahan Yeghyan

487
5
7

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

3 Answers3

Linked