How to use dynamic parameter in IN section of JPA request with name?

Question

my problem is with this type of request:

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

The previous code works fine in Sql Developer. The same static query also works fine and returns me some results;

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

But when I try to parameterize this, I run into 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 have no result (but errors in the console). And when I look at the magazine, I see this:

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

I also tried using quotation marks (with a similar result) or an unordered parameter (: selectedValues), which leads to this error:

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

I enventually tried to have the brackets set directly in the parameter, instead of the request, but that didn't work either ...

I could create my request at runtime to fit the first (working) case, but I would rather do it appropriately; that way, if anyone has an idea, I will read them with great interest!

FYI: JPA Version 1.0 Oracle 11G

Answer 1

JPA only supports using a collection as a list literal parameter in JPQL queries, and not in its own queries. Some JPA vendors support it as a proprietary function, but are not part of the JPA specification (see https://stackoverflow.com/a/167189/ ).

Named parameters in custom queries are also not part of the JPA specification. Their behavior depends on the persistence provider and / or the JDBC driver.

Hibernate with the JDBC driver for Oracle supports 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(); 

Answer 2

Replace this:

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

from

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

Answer 3

Instead:

 nativeQuery.setParameter("selectedValues", params); 

I had to use:

 nativeQuery.setParameterList("selectedValues", params); 

Answer 4

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