问题描述
我的问题是关于这种查询的:
my problem is about this kind of query :
select * from SOMETABLE where SOMEFIELD in ('STRING1','STRING2');
先前的代码在Sql Developer中可以正常工作.相同的静态查询也可以正常工作,并返回一些结果;
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]
我也尝试不使用引号(结果相似)或不使用顺序参数(:selectedValues),这会导致这样的错误:
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!
仅供参考:JPA 1.0版甲骨文11G
FYI :JPA version 1.0Oracle 11G
推荐答案
JPA仅在JPQL查询中而不在本机查询中支持将集合用作列表文字参数.一些JPA提供程序将其作为专有功能来支持,但它不是JPA规范的一部分(请参阅 https://stackoverflow.com/a/3145275/1285097 ).
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).
本机查询中的命名参数也不属于JPA规范.它们的行为取决于持久性提供程序和/或JDBC驱动程序.
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.
使用Oracle的JDBC驱动程序休眠时支持这两个功能.
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();
这篇关于如何在JPA命名查询的IN子句中使用动态参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!