Oracle jdbc“createArray”在尝试将数组传递给准备好的语句时抛出“不支持的功能”异常

Oracle jdbc "createArray" throws "Unsupported feature" exception while trying to pass array to prepared statement

提问by sravan kumar

I am trying to pass an array to my prepared statement by doing createArrayOf

我正在尝试通过执行createArrayOf将数组传递给我准备好的语句

val prep: PreparedStatement = con.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)")

val array :Array[Object]=Array("1165006001","1165006002")

val sqlArray = con.createArrayOf("VARCHAR",array) //getting the exception here 

prep.setArray(1,sqlArray)

val rs = prep.executeQuery()

while (rs.next()) {

    println(rs.getObject(1))

}

But createArrayOf method throws an error saying

但是 createArrayOf 方法抛出一个错误说

Exception  thread "main" java.sql.SQLFeatureNotSupportedException:Unsupported feature   
at Oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:8707)
at com.testpackage.Main$.main(Main.scala:109)
at com.testpackage.Main.main(Main.scala)    
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)     
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

I am using the ojdbc7.jar for the jdbc connection. Is there anything I can do differently to pass the array to the prepared statement?

我使用 ojdbc7.jar 进行 jdbc 连接。有什么我可以做的不同的事情来将数组传递给准备好的语句吗？

采纳答案by Luke Woodward

I am not going to answer the question you've asked ("how can I pass the array to the prepared statement"), because even if you could figure out how to pass the array, your code would quite probably still not work.

我不会回答您提出的问题（“我如何将数组传递给准备好的语句”），因为即使您能弄清楚如何传递数组，您的代码也很可能仍然无法工作。

The problem is that with JDBC you can't pass an array of two values ("1165006001","1165006002")into the query

问题是使用 JDBC 不能将两个值的数组传递("1165006001","1165006002")到查询中

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)

and expect it to be interpreted by the database as

并期望它被数据库解释为

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in ('1165006001','1165006002')

as it seems to me you want it to.

在我看来，你想要它。

If you could pass the array in, the query would return all rows for which the column CMF_PPK_NBRcontained a nested table with those two values. Oracle would interpret the array as one value, rather than two. I'm guessing that column has type either VARCHAR2, and so you'd only end up with a type error as Oracle tries to compare an array of strings to a single string.

如果您可以传入数组，则查询将返回列CMF_PPK_NBR包含具有这两个值的嵌套表的所有行。Oracle 会将数组解释为一个值，而不是两个值。我猜该列的类型为 or VARCHAR2，因此当 Oracle 尝试将字符串数组与单个字符串进行比较时，您只会得到类型错误。

If you want to pass multiple values in an INclause, then the simplest way is to build up a SQL string with a number of ?marks, and set values for the parameters individually. In other words, for the example above, the SQL string for two parameters would be

如果要在一个IN子句中传递多个值，那么最简单的方法是构建一个带有多个?标记的 SQL 字符串，并分别为参数设置值。换句话说，对于上面的例子，两个参数的 SQL 字符串将是

select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?, ?)

and you would have two calls to prep.setString(...), one for each array element. Similarly, if there were, say, 5 items in the array, you'd build a SQL string with 5 ?marks, and call prep.setString(...)5 times, and so on.

并且您将有两次调用prep.setString(...)，每个数组元素调用一次。类似地，如果数组中有 5 个项目，您将构建一个带有 5 个?标记的 SQL 字符串，并调用prep.setString(...)5 次，依此类推。

回答by 9000

There's no completely nice way to do that, but still two reasonable ways exist.

没有完全好的方法可以做到这一点，但仍然存在两种合理的方法。

Use a hand-crafted in (?,...)clause.

使用手工制作的in (?,...)条款。

The solution is explained here. The idea is to add as many ?s as threre are array elements, and bind each item individually. This gives you safety of mind because values will be properly translated, escaped, etc.

该解决方案在此说明。这个想法是添加与?数组元素一样多的s，并单独绑定每个项目。这让您安心，因为值将被正确翻译、转义等。

This sort of defeats the purpose of preparing a statement, unless your arrays are normally the same size.

这种方式违背了准备语句的目的，除非您的数组通常具有相同的大小。

You can also run out of the allowed query length if you have great many items in the array.

如果数组中有很多项目，您也可能用完允许的查询长度。

Use a cast and pass the array as a string.

使用强制转换并将数组作为字符串传递。

The solution (for PL/SQL) is explained here, but applies to general SQL statements. It boils down to using a clause like

解决方案（用于 PL/SQL）在此处解释，但适用于一般 SQL 语句。它归结为使用像这样的子句

...in (select cast(in_list(?) as some_table_type) from dual)

The parameter here is passed as varchar2, like "1, 2, 3", is parsed as an in-memory table, and is selected from, all inside the inclause.

这里的参数被传递为varchar2，像"1, 2, 3"，被解析为内存表，并从中选择，所有在in子句中。

This allows you to have a query of a fixed length, and possibly efficiently reuse the prepared statement. It also allows you to pass great many items in the array.

这允许您进行固定长度的查询，并可能有效地重用准备好的语句。它还允许您在数组中传递大量项目。

It, OTOH, requires you to concatenate and pass the data as a string,which can potentially lead to an SQL injection.I'd still suppose it to be safe if your array values are numeric.

它，OTOH，要求您将数据作为字符串连接并传递，这可能会导致SQL 注入。如果您的数组值是数字，我仍然认为它是安全的。

回答by Douglas Surber

Oracle Database JDBC drivers do not support Connection.createArrayOf because the Oracle Database does not support anonymous array types. A type ARRAY OF FOO is an anonymous type. The array type has no name though the base type does. Oracle Database does not support anonymous array types. You have to define a named type

Oracle 数据库 JDBC 驱动程序不支持 Connection.createArrayOf，因为 Oracle 数据库不支持匿名数组类型。类型 ARRAY OF FOO 是匿名类型。数组类型没有名称，但基类型有。Oracle 数据库不支持匿名数组类型。你必须定义一个命名类型

TYPE ARRAY_OF_FOO IS TABLE OF FOO;

You can then create an Array by calling

然后您可以通过调用创建一个数组

oracleConnection.createOracleArray("ARRAY_OF_FOO", elements);