How to use a dynamic parameter in a IN clause of a JPA named query?

Question

my problem is about this kind of query :

select * from SOMETABLE where SOMEFIELD in ('STRING1','STRING2');

the previous code works fine within Sql Developer. The same static query also works fine and returns me a few results;

Query nativeQuery = em.createNativeQuery(thePreviousQuery,new someResultSet());
return nativeQuery.getResultList();

But when I try to parameterize this, I encounter a problem.

final String parameterizedQuery = "select * from SOMETABLE where SOMEFIELD in (?selectedValues)";
Query nativeQuery = em.createNativeQuery(parameterizedQuery ,new someResultSet());
nativeQuery.setParameter("selectedValues","'STRING1','STRING2'");
return nativeQuery.getResultList();

I got no result (but no error in console). And when I look at the log, I see such a thing :

select * from SOMETABLE where SOMEFIELD in (?)
bind => [STRING1,STRING2]

I also tried to use no quotes (with similar result), or non ordered parameter (:selectedValues), which leads to such an error :

SQL Error: Missing IN or OUT parameter at index:: 1

I enventually tried to had the parentheses set directly in the parameter, instead of the query, but this didn't work either...

I could build my query at runtime, to match the first (working) case, but I'd rather do it the proper way; thus, if anyone has an idea, I'll read them with great interest!

FYI : JPA version 1.0 Oracle 11G

score 47 · Accepted Answer · edited Nov 21 '17 at 12:42

47

JPA support the use of a collection as a list literal parameter only in JPQL queries, not in native queries. Some JPA providers support it as a proprietary feature, but it's not part of the JPA specification (see https://stackoverflow.com/a/3145275/1285097).

Named parameters in native queries also aren't part of the JPA specification. Their behavior depends on the persistence provider and/or the JDBC driver.

Hibernate with the JDBC driver for Oracle support both of these features.

List<String> selectedValues = Arrays.asList("STRING1", "STRING2");
final String parameterizedQuery = "select * from SOMETABLE where SOMEFIELD in (:selectedValues)";
return em.createNativeQuery(parameterizedQuery)
         .setParameter("selectedValues", selectedValues)
         .getResultList();

edited Nov 21 '17 at 12:42

Abdullah Khan

12,010
6
65
78

answered Feb 01 '14 at 05:01

Marc-André

846
6
8

Marc-André, thank you for pointing out the problem. Now I realize I'll have to either use hibernate -I won't be allowed to do that I fear- or perform a basic concatenation. Thanks anyway! – Marvin Feb 03 '14 at 08:59
1

From what I know, the best practice would be to dynamically add to the query a ? parameter for each of the elements in the list, then set each parameter individually. This allows Oracle to use its cache when the query is used many times with the same number of elements. – Marc-André Feb 04 '14 at 01:42
3

The parameter name must be `#selectedValues` for Native queries and not `:selectedValues` with EclipseLink JPA. – Rares Oltean Mar 11 '15 at 13:52
Hibernate with the jTDS JDBC driver for MSSQL also supports lists as parameters for native queries – Sebastian J. Nov 14 '16 at 19:59

score 3 · Answer 2 · edited Feb 03 '17 at 21:57

3

Instead of:

nativeQuery.setParameter("selectedValues", params);

I had to use:

nativeQuery.setParameterList("selectedValues", params);

edited Feb 03 '17 at 21:57

answered Feb 03 '17 at 21:22

Brian

153
1
9

1

Where does `setParameterList` method come from? – Buhake Sindi Aug 31 '17 at 13:30
See https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/Query.html#setParameterList(java.lang.String, java.util.Collection) – Brian Sep 01 '17 at 14:27
1

`setParameterList` is a Hibernate specific feature and not available in standard JPA – Jack Feb 08 '20 at 03:21

score 2 · Answer 3 · answered Nov 30 '17 at 11:14

This worked for me in derby. parameter without "()".

List<String> selectedValues = Arrays.asList("STRING1", "STRING2");
final String parameterizedQuery = "select * from SOMETABLE where SOMEFIELD in 
:selectedValues";
return em.createNativeQuery(parameterizedQuery)
         .setParameter("selectedValues", selectedValues)
         .getResultList();

score 1 · Answer 4 · answered Jan 31 '14 at 15:55

1

Replace this:

nativeQuery.setParameter("selectedValues","'STRING1','STRING2'");

with

List<String> params;
nativeQuery.setParameter("selectedValues",params);

answered Jan 31 '14 at 15:55

Sabuj Hassan

38,281
14
75
85

1

Thank you for your fast answer. Though, I forgot to mention I had tried this too, srry : error 17004 => invalid column type. The bind looks like this : bind => [[STRING1,STRING2]] – Marvin Jan 31 '14 at 16:29

score -1 · Answer 5 · edited Feb 17 '19 at 12:59

-1

I also faced the same issue.
This is what I did:

List<String> sample = new ArrayList<String>();
sample.add("sample1");
sample.add("sample2");

And now you, can set the sample in params.

edited Feb 17 '19 at 12:59

סטנלי גרונן

2,917
23
46
68

answered Feb 17 '19 at 12:11

Anand

97
1
2
7

How to use a dynamic parameter in a IN clause of a JPA named query?

5 Answers5

Linked