如何从 Oracle 中的 JDBC 批量插入中获取生成的密钥？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15684297/
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 to get generated keys from JDBC batch insert in Oracle?
提问by atripathi
I am inserting many records using JDBC batch inserts.
Is there any way to get the generated key for each record?
Can I use ps.getGeneratedKeys()with batch inserts?
我正在使用 JDBC 批量插入插入许多记录。有没有办法为每条记录获取生成的密钥？我可以ps.getGeneratedKeys()与批量插入一起使用吗？
I am using oracle.jdbc.OracleDriver
我在用 oracle.jdbc.OracleDriver
final String insert = "Insert into Student(RollNumber, Name, Age) values(StudentSEQ.nextval, ? , ?)";
final int BATCH_SIZE = 998;
int count = 0;
Connection con = null;
PreparedStatement ps = null;
try {
con = getConnection();
ps = con.prepareStatement(insert);
for (Student s : students) {
ps.setString(1, s.getName());
ps.setInt(2, s.getAge());
ps.addBatch();
count++;
if (count % BATCH_SIZE == 0) {
// Insert records in batches
ps.executeBatch();
}
}
// Insert remaining records
ps.executeBatch();
} finally {
if(ps != null)
ps.close();
release(con);
}
I am thinking of using ps.executeUpdate()along with ps.getGeneratedKeys()inside the loop to get the desired result. Any other solutions?
我正在考虑在循环内使用ps.executeUpdate()withps.getGeneratedKeys()来获得所需的结果。还有其他解决方案吗？
采纳答案by Philip O.
It appears that Oracle 12c does not support combining auto-generated keys with batch update according to the following page:
根据以下页面，Oracle 12c 似乎不支持将自动生成的密钥与批量更新相结合：
http://docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm
http://docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm
See the subsection labeled "Limitations" under the section "Retrieval of Auto-Generated Keys"
请参阅“检索自动生成的密钥”部分下标有“限制”的小节
回答by Mark Rotteveel
The JDBC 4.1 specification, section 13.6 Retrieving Auto Generated Valuessays:
在JDBC 4.1规范，部分13.6检索自动生成的值表示：
It is implementation-defined as to whether
getGeneratedKeyswill return generated values after invoking theexecuteBatchmethod.
getGeneratedKeys调用executeBatch方法后是否返回生成值是实现定义的。
So you will need to check if your driver actually supports it for batch updates. As indicated in the answer by Philip O., retrieval of generated keys is not supported with batch updates as documented in Oracle 12 JDBC Standards Support:
因此，您需要检查您的驱动程序是否真的支持批量更新。如Philip O. 的回答所示，如Oracle 12 JDBC Standards Support 中所述，批量更新不支持检索生成的密钥：
You cannot combine auto-generated keys with batch update.
您不能将自动生成的密钥与批量更新结合使用。
In any case if it is supported by your driver than your statement prepare should be changed to the code below to instruct the driver to retrieve generated keys:
在任何情况下，如果您的驱动程序支持它，那么您的语句 prepare 应更改为以下代码以指示驱动程序检索生成的密钥：
ps = con.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
Note: you may need to use one of the other generated keys prepare methods (prepareStatement(sql, columnIndexes)or prepareStatement(sql, columnNames)) as Oracle will return the ROW_IDwith the method in my example.
注意：您可能需要使用其他生成的键准备方法之一（prepareStatement(sql, columnIndexes)或prepareStatement(sql, columnNames)），因为ROW_ID在我的示例中，Oracle 将使用该方法返回。
