Java 如何将 ArrayList 绑定到 Oracle 中的 PreparedStatement？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/303218/
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 do I bind an ArrayList to a PreparedStatement in Oracle?
提问by MetroidFan2002
I was wondering if there was a way to bind an ArrayList (or any kind of List, for that matter) to a PreparedStatement which will eventually be used to access an Oracle database. I found:
我想知道是否有办法将 ArrayList（或任何类型的 List，就此而言）绑定到最终将用于访问 Oracle 数据库的 PreparedStatement。我发现：
PreparedStatement IN clause alternatives?
And that seems similar to my issue, but this question is more specific: I'd like to bind an ArrayList to a PreparedStatement to be used in Oracle, if it is possible, how is this accomplished?
这似乎与我的问题相似，但这个问题更具体：我想将 ArrayList 绑定到要在 Oracle 中使用的 PreparedStatement，如果可能的话，这是如何实现的？
采纳答案by erickson
You can't bind a List to a single parameter in a prepared statement.
您不能将 List 绑定到准备好的语句中的单个参数。
Generate SQL with the a parameter marker for each element in the list, for example:
为列表中的每个元素生成带有参数标记的 SQL，例如：
SELECT NAME FROM ITEM WHERE ID IN (?, ?, ?, ?)
Even though you'll generate a new statement for each query, I'd still recommend using a PreparedStatement. If your list contains Stringinstances, you'll get the necessary escaping to protect from SQL injection.
即使您将为每个查询生成一个新语句，我仍然建议使用PreparedStatement. 如果您的列表包含String实例，您将获得必要的转义以防止 SQL 注入。
But even if it's a safe type, like Integerobjects, some drivers or middleware can cache PreparedStatements, and return a cached instance if the same form is requested. Of course, some testing would be necessary. If your lists vary widely in size, you'll have many different statements, and a poorly-implemented cache might not be prepared to handle so many.
但即使它是一个安全类型，比如Integer对象，一些驱动程序或中间件也可以缓存PreparedStatements，如果请求相同的表单，则返回缓存的实例。当然，一些测试是必要的。如果您的列表大小差异很大，您将有许多不同的语句，并且实现不佳的缓存可能无法处理这么多。
回答by Paul Tomblin
Well, judging by the answer to that one, especially the comments to my wrong answer in that question, you can't.
好吧，从那个问题的答案来看，尤其是对我在那个问题中的错误答案的评论，你不能。
See http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html#996857
见http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html#996857
回答by Dave Costa
You can't bind it directly. There is a way to pass an array as a parameter. I have no idea what you want to do with it on the database side so this may not help you.
不能直接绑定。有一种方法可以将数组作为参数传递。我不知道你想在数据库端用它做什么，所以这可能对你没有帮助。
Basically, you have to create a nested table type in the database; build a Java object based on that type, containing the data from your array; and pass that as the parameter.
基本上，您必须在数据库中创建一个嵌套表类型；基于该类型构建一个 Java 对象，其中包含来自数组的数据；并将其作为参数传递。
If you created these objects in the database:
如果您在数据库中创建了这些对象：
CREATE OR REPLACE TYPE my_nested_table IS TABLE OF VARCHAR2(20);
CREATE TABLE my_table (a my_nested_table) NESTED TABLE a STORE AS my_table_a;
Then you can write Java code like this:
然后你可以像这样编写Java代码：
String[] insertvalues = { "a", "b", "c" };
PreparedStatement p = conn.prepareStatement("INSERT INTO my_table VALUES( ? )");
ARRAY insertParameter = new ARRAY( a_desc, conn, insertvalues );
p.setArray( 1, insertParameter );
p.execute();
The results in Oracle look like this:
Oracle 中的结果如下所示：
dev> select * from my_table;
A
--------------------------------------------------------------------------------
MY_NESTED_TABLE('a', 'b', 'c')
