如何通过不具有ArrayIndexOutOfBoundsException的executeBatch获取生成的键? [英] How to get generated keys by executeBatch without ArrayIndexOutOfBoundsException?
问题描述
以下方法我想同时插入几条记录.
The following method I want to INSERT several records simultaneously.
public void insert() {
try {
this.connection.setAutoCommit(false);
PreparedStatement ps = this.connection.prepareStatement(
"INSERT INTO COMPANY (NAME,Address) Values (?,?)", new String[]{"ID"});
ps.setString(1, "X01");
ps.setString(2, "Address1");
ps.addBatch();
ps.setString(1, "Y01");
ps.setString(2, "Address2");
ps.addBatch();
//EXCEPTION OCCURS HERE
int[] numUpdates = ps.executeBatch();
for (int i = 0; i < numUpdates.length; i++) {
System.out.println("Execution " + i +
"successful: " + numUpdates[i] + " rows inserted");
}
ResultSet resultSet =
(ps).getGeneratedKeys();
while (resultSet.next()) {
String deptNoKey = resultSet.getString(1);
System.out.println("Automatically generated key value = "
+ deptNoKey);
}
} catch (BatchUpdateException b) {
// process BatchUpdateException
} catch (SQLException e) {
e.printStackTrace();
}
}
这时,当我希望获得每个INSERT生成的PK时,就会收到此异常
At this point, when I expect to get the PK's generated for each INSERT, I get this Exception
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 22
at oracle.jdbc.driver.T4CNumberAccessor.unmarshalOneRow(T4CNumberAccessor.java:250)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1250)
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:14264)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:14379)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:589)
at dbpro.SqlHelper.insert2(SqlHelper.java:988)
at dbpro.SqlHelper.main(SqlHelper.java:1023)
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:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
异常后: 在表COMPANY中,正确添加了两条记录,但是我希望对每个执行的插入操作获得一个ResultSet一行,这样我就可以生成每个PK.
After Exception: In The table COMPANY , two records are added correctly but I expected to get a ResultSet with one row for each insert performed, so I could get each PK generated.
推荐答案
这似乎是Windows上Oracle中的错误,或者是驱动程序JAR ojdbc6.jar或(都知道Oracle)这两个错误.
This appears to be a bug in Oracle on Windows, the driver JAR ojdbc6.jar, or (knowing Oracle) both.
您提供的代码没有重大问题.尽管我每次运行时插入的行数都返回为-2(=
There's no significant problem with the code you have presented. It should work, although when I ran it the numbers of rows inserted each time was returned as -2 (= Statement.SUCCESS_NO_INFO
), so you may be better off ignoring these numbers.
您的代码对Linux上具有四个版本的Oracle JDBC驱动程序JAR的Oracle 11g XE 11.2.0.2.0来说运行良好.但是,如果我在具有相同版本的Oracle XE和ojdbc6.jar的Windows 10上运行它,它将失败,并显示与您得到的相同的ArrayIndexOutOfBoundsException.如果我使用ojdbc7.jar而不是ojdbc6.jar,问题就解决了.
Your code runs fine for me with Oracle 11g XE 11.2.0.2.0 on Linux with four versions of the Oracle JDBC driver JAR. However, if I run it on Windows 10 with the same version of Oracle XE and with ojdbc6.jar, it fails with the same ArrayIndexOutOfBoundsException you are getting. The problem goes away if I use ojdbc7.jar instead of ojdbc6.jar.
因此,我建议将ojdbc6.jar替换为ojdbc7.jar,您可以从
Therefore, I would recommend replacing ojdbc6.jar with ojdbc7.jar, which you can download from here.
这篇关于如何通过不具有ArrayIndexOutOfBoundsException的executeBatch获取生成的键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!