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…

Accepted Answer

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

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

Solution

Problem

Related problems