Oracle jdbc "createArray"在尝试将数组传递给预准备语句时抛出"Unsupported feature"异常

Question

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

val array = Array("1165006001","1165006002")
val sqlArray = con.createArrayOf("VARCHAR",array) // getting the exception here 
val prep = con.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (?)")
prep.setArray(1,sqlArray)

val rs = prep.executeQuery()
while (rs.next()) {
println(rs.getObject(1))
}

但是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)

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

Answer 1

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

TYPE ARRAY_OF_FOO IS TABLE OF FOO;

然后，您可以通过调用来创建阵列

oracleConnection.createOracleArray("ARRAY_OF_FOO", elements);

---

编辑：正如评论中提到的@gouesej，您甚至可以在Oracle数据库数据盒带扩展中使用内置类型。它变得更加简单：

val sqlArray = oracleConnection.createOracleArray("SYS.ODCIVARCHAR2LIST", Array("1165006001","1165006002"))
val prep = oracleConnection.prepareStatement("select * from SOA_WEB_USER.VOPEX where CMF_PPK_NBR in (select * from TABLE(?))")
prep.setArray(1, sqlArray)
val rs = prep.executeQuery()
while (rs.next()) {
println(rs.getObject(1))
}

Answer 2

我不会回答你问的问题("我如何将数组传递给准备好的语句")，因为即使你能弄清楚如何传递数组，你的代码也很可能仍然无法工作。

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

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

并期望数据库将其解释为

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

在我看来，你希望它。

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

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

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

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

Answer 3

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

使用手工编制的in (?,...)子句

这里解释了解决方案。其想法是添加与三个数组元素一样多的?，并单独绑定每个项。这会让你放心，因为值会被正确翻译、转义等。

除非数组的大小通常相同，否则这种方法会破坏准备语句的目的。

如果数组中有很多项，也可能会超出允许的查询长度。

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

这里解释了(PL/SQL的)解决方案，但它适用于一般的SQL语句。它可以归结为使用这样的子句

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

这里的参数被传递为varchar2，与"1, 2, 3"一样，被解析为内存中的表，并且是从in子句中选择的。

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

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