SQL 如何在 JPA 命名查询的 IN 子句中使用动态参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21484176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a dynamic parameter in a IN clause of a JPA named query?
提问by Marvin
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;
前面的代码在 Sql Developer 中运行良好。相同的静态查询也可以正常工作并返回一些结果；
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 :
我也尝试不使用引号（具有类似的结果）或非有序参数（：selectedValues），这会导致这样的错误：
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 1.0 版 Oracle 11G
回答by Marc-André
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 仅支持在 JPQL 查询中使用集合作为列表文字参数，而不支持在本机查询中使用。一些 JPA 提供商支持它作为专有功能，但它不是 JPA 规范的一部分（请参阅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.
本机查询中的命名参数也不属于 JPA 规范的一部分。它们的行为取决于持久性提供程序和/或 JDBC 驱动程序。
Hibernate with the JDBC driver for Oracle support both of these features.
带有 Oracle JDBC 驱动程序的 Hibernate 支持这两个特性。
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();
回答by Brian
Instead of:
代替：
nativeQuery.setParameter("selectedValues", params);
I had to use:
我不得不使用：
nativeQuery.setParameterList("selectedValues", params);
回答by Sabuj Hassan
Replace this:
替换这个：
nativeQuery.setParameter("selectedValues","'STRING1','STRING2'");
with
和
List<String> params;
nativeQuery.setParameter("selectedValues",params);
回答by Pradeep
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();
回答by Anand
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.
现在您可以在params.
