postgresql 如何将 String[] 参数设置为本机查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12042181/
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 can I set a String[] parameter to a native query?
提问by Bernardo Vale
This is my PostgreSQL function:
这是我的 PostgreSQL 函数：
salvarArquivoGeometricoCasoZeroPOINT
(dimensao text,tableName text,tuplas text[],srid text)
It has a text[]parameter, and I want to pass a Java String[]to it from my JPQL:
它有一个text[]参数，我想String[]从我的 JPQL传递一个 Java给它：
public String salvarGeometriaCaso0(String[] tuplas,FileDto arquivo){
Query query =
em().createNativeQuery("select
salvarArquivoGeometricoCasoZeroPOINT(?1,?2,?3,?4)");
query.setParameter(1,arquivo.getGeo());//String
query.setParameter(2,arquivo.getTable());/String
query.setParameter(3,tuplas);//String[]
query.setParameter(4,arquivo.getSrid());//String
return (String) query.getSingleResult();//function returns a Text, so cast to String
}
The above code fails with the exception:
上面的代码失败，但有异常：
ERROR] Internal Exception: org.postgresql.util.PSQLException: Can not infer a SQL
type to use for an instance of [Ljava.lang.String;.
Use setObject () with an explicit Types value to specify the type to use.
so I'm not sure how to call my function from EclipseLink.
所以我不确定如何从 EclipseLink 调用我的函数。
采纳答案by Craig Ringer
Testing by passing a Java array of type String[] to PreparedStatement.setObject(...)results in the behaviour you report. It appears that PgJDBC doesn't accept a Java array as an argument to PreparedStatement.setObject(), with or without a Types.ARRAYparameter.
通过传递 String[] 类型的 Java 数组来测试PreparedStatement.setObject(...)您报告的行为。看来 PgJDBC 不接受 Java 数组作为 的参数PreparedStatement.setObject()，无论是否带有Types.ARRAY参数。
Compliance
遵守
The JDBC spec, 16.5 "Array Objects", suggests that the JDBC Arrayexists partly so the client doesn't have to copy big arrays into memory, they can be used by reference. I'm not too sure whether the JDBC driver is required to accept raw Java arrays as parameters. All the spec code refers to java.sql.Arrayand the spec makes it clear that arrays are mapped via the Arrayinterface in Appendix B and elsewhere. In a quick search/reading I could find nomention of passing raw Java arrays other than byte[]as parameters or returning them as results.
JDBC 规范16.5 "Array Objects"表明 JDBCArray部分存在，因此客户端不必将大数组复制到内存中，它们可以通过引用使用。我不太确定 JDBC 驱动程序是否需要接受原始 Java 数组作为参数。所有规范代码都引用java.sql.Array并且规范清楚地表明数组是通过Array附录 B 和其他地方的接口映射的。在快速搜索/阅读中，我没有发现除了byte[]作为参数传递原始 Java 数组或将它们作为结果返回之外，没有提到传递原始 Java 数组。
However, in §16.5.4 the JDBC4.2 draft spec reads:
然而，在 §16.5.4 JDBC4.2 规范草案中写道：
A Java array may be passed as an input parameter by calling the method
PreparedSatement.setObject.
though all the rest of the code there refers to Arrayobjects. Do they mean Arrayby "a Java array"? Or do they mean a raw native Java array like String[]?
尽管那里的所有其余代码都引用了Array对象。他们的意思Array是“Java 数组”吗？或者它们是指原始的原生 Java 数组，例如String[]？
It looks to me like clients are supposed to use the java.sql.Arrayinterface via Connection.createArrayOf(...), so EclipseLink is probably doing the wrong thing.
在我看来，客户应该通过 使用该java.sql.Array接口Connection.createArrayOf(...)，因此 EclipseLink 可能做错了事情。
What do do about it
怎么办
Try updating EclipseLink to 2.4 in the hopes it uses the commonly specified method of passing arrays to JDBC via a java.sql.Array object.
尝试将 EclipseLink 更新到 2.4，希望它使用通过 java.sql.Array 对象将数组传递给 JDBC 的常用指定方法。
You may also need to annotate the mapping with @Array, an EclipseLink extension. See also this forum thread re 2.3and bug 361701.
您可能还需要使用@ArrayEclipseLink 扩展对映射进行注释。另请参阅此论坛主题 re 2.3和错误 361701。
It appears you may have to implement your own type handler for EclipseLink to override its behaviour. To correctly set an array parameter via PgJDBC you must use:
看来您可能必须为 EclipseLink 实现自己的类型处理程序以覆盖其行为。要通过 PgJDBC 正确设置数组参数，您必须使用：
Array sqlArray = conn.createArrayOf("text", strArray);
pstmt.setArray(1, sqlArray);
pstmt.executeUpdate();
... where connand pstmtare a java.sql.Connectionand a PreparedStatementrespectively, and strArrayis a String[]instance.
... 其中conn和pstmt分别是 ajava.sql.Connection和 a PreparedStatement，并且strArray是一个String[]实例。
See Custom data types in the eclipselink wiki.
请参阅eclipselink wiki 中的自定义数据类型。
On a side note, the use of a string type name to specify the array's data type in createArrayOfseems kind of insane given the existence of java.sql.Types. It makes portability much harder; the above code won't run on (say) Oracle, because Oracle wants VARCHARnot textas a type name.
附带说明一下，createArrayOf考虑到java.sql.Types. 它使便携性变得更加困难；上面的代码将无法在（比方说）运行的Oracle，因为Oracle希望VARCHAR不text作为类型名称。
Note: The unit test org/postgresql/test/jdbc2/ArrayTest.javahas ArrayTest.testSetArray(), which at line 166 tests:
注意：单元测试org/postgresql/test/jdbc2/ArrayTest.java有ArrayTest.testSetArray()，在第 166 行测试：
pstmt.setObject(1, arr);
pstmt.executeUpdate();
... however the type of arris java.sql.Array, not int[]. It's a JDBC array type, not a regular Java array.
... 但是类型arr是java.sql.Array，不是int[]。它是一种 JDBC 数组类型，而不是常规的 Java 数组。
回答by Dhruvil Thaker
I am very late to answer it.
我很晚才回答。
This solution is kind of workaround using postgreSQL built-in function, which definitely worked for me.
这个解决方案是一种使用 postgreSQL 内置函数的解决方法，这对我来说绝对有用。
1) Convert String Array to Comma Separated String
1) 将字符串数组转换为逗号分隔的字符串
If you are using Java8, it's pretty easy. other options are here
如果您使用的是 Java8，这很容易。其他选项在这里
String commaSeparatedString = String.join(",",stringArray); // Java8 feature
2) PostgreSQL built-in function string_to_array()
2) PostgreSQL 内置函数 string_to_array()
you can find other postgreSQL array functions here
您可以在此处找到其他 postgreSQL 数组函数
// tableName ( name text, string_array_column_name text[] )
String query = "insert into tableName(name,string_array_column_name ) values(?, string_to_array(?,',') )";
int[] types = new int[] { Types.VARCHAR, Types.VARCHAR};
Object[] psParams = new Object[] {"Dhruvil Thaker",commaSeparatedString };
jdbcTemplate.batchUpdate(query, psParams ,types); // assuming you have jdbctemplate instance
回答by Bernardo Vale
It seems EclipseLink doesn't fix the bug 361701.mentioned by @Craig Ringer.
EclipseLink 似乎没有修复 错误 361701。@Craig Ringer 提到。
The only way to pass a String[] as a parameter is using the JDBC without EclipseLink. Check the code.
将 String[] 作为参数传递的唯一方法是使用没有 EclipseLink 的 JDBC。检查代码。
Connection con = ConnectionHelper.getConnection();
Array tArray = con.createArrayOf("text", tuplas);
PreparedStatement pstm =
con.prepareStatement("select salvarArquivoGeometricoCasoZeroPOINT(?,?,?,?)");
pstm.setString(1,arquivo.getGeoType());
pstm.setString(2,arquivo.getTable());
pstm.setArray(3,tArray);
pstm.setString(4,arquivo.getSrid());
rs = pstm.executeQuery();
ConnectionHelper it's my java.sql.Connection class.
ConnectionHelper 它是我的 java.sql.Connection 类。
I appreciate the help of you guys: @Craig Ringer and @Matt Ball, Thanks.
感谢你们的帮助：@Craig Ringer 和@Matt Ball，谢谢。
回答by Chicowitz
I ran into this error when accidentally using jdbcTemplate.updateinstead of jdbcTemplate.batchUpdate
我在不小心使用jdbcTemplate.update而不是使用时遇到了这个错误jdbcTemplate.batchUpdate
