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
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();
Instead of:
nativeQuery.setParameter("selectedValues", params);
I had to use:
nativeQuery.setParameterList("selectedValues", params);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With