如何在 JPA 命名查询的 IN 子句中使用动态参数?

2021-12-14 00:00:00 sql oracle11g oracle jpa

我的问题是关于这种查询:

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.0 Oracle 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 支持这两个功能.

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

相关文章