JDBC PreparedStatement，批处理更新和生成的密钥

问题描述

我在批量使用jdbc prepareStatement并尝试从中创建生成的密钥时遇到问题.

I have a problem using jdbc preparedStatement in batch and trying to get the generated keys created from this.

代码:

        PreparedStatement stmt = null;
    ...
    connection.setAutoCommit(false);
    stmt = connection.prepareStatement(insertSuspiciousElement,new String[] {"external_id","element_id"});
final int elementBatchSize = 5000;
    int elementCount =0;
        for(BlSuspiciousElement element : elements){
        externalIds.add(element.getExternalId());
        stmt.setInt(1, element.getBlElementType().getElementTypeId());
        stmt.setString(2, element.getFirstname());
        stmt.addBatch();
        elementCount++;
        if(elementCount % elementBatchSize == 0){
            System.out.println("Running query with batch size for suspiciousElement");
            stmt.executeBatch();

            ResultSet keys = stmt.getGeneratedKeys();
            while(keys.next()){
                externalIdElementIdMapping.put(keys.getInt("external_id"),keys.getInt("element_id"));
            }
            keys.close();
            stmt.clearBatch();
            stmt.clearParameters();
            stmt.clearWarnings();
            System.out.println("Done query with batch size for suspiciousElement");
        }
        }

在第一个stmt.executeBatch()方法上失败.

it fails at the first stmt.executeBatch() method.

错误:

[30/01/12 15:54:41:684 CET] 00000029 RemoteExcepti E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "processFullFeedForPepAndRelationUpdateOnly" on bean "BeanId(CoRelateEar#AmlKycToolBO.jar#FactivaDBUpdater, null)". Exception data: java.lang.ArrayIndexOutOfBoundsException
at oracle.jdbc.driver.T4CNumberAccessor.unmarshalOneRow(T4CNumberAccessor.java:201)
at oracle.jdbc.driver.T4C8Oall.readRXD(T4C8Oall.java:696)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:340)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:10143)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10249)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:748)

对我来说不是很明显...

Not very obvious for me...

似乎不适用于批处理更新?仅用于陈述或准备陈述.在这种情况下，我认为最好尝试批量插入，然后运行另一个查询以查找每个创建的元素的生成键...

It seems that it doesn't work for Batch updates ? Only for statement or prepared statement. In this case, I think I'd better try to do my batch insert, and then run another query to find the generated keys for each created element...

感谢您的帮助，

F

推荐答案

我相信您应该告诉Oracle JDBC驱动程序您将要检索生成的密钥.

I believe you should tell Oracle JDBC driver that you'd be retrieving generated keys.

查看以下问题的已接受答案以获取详细信息:带有Statement.RETURN_GENERATED_KEYS的PreparedStatement .

Check out the accepted answer to the following question for details: PreparedStatement with Statement.RETURN_GENERATED_KEYS .

编辑1/31/12:如果该方法不适用于批处理(并且我没有对批处理进行尝试)，则可以代替批处理，只需关闭自动提交，对所有数据进行插入，然后然后提交.在我的实验中，这种方法的性能损失仅为3％.我还在SO上找到了另一个类似的建议: MySQL批处理stmt带有Statement.RETURN_GENERATED_KEYS .

EDIT 1/31/12: If this approach doesn't work with batch (and I didn't try it with batch), you can instead of batching, just turn autocommit off, do inserts for all your data, and then commit. In my experiments the performance penalty for this approach was only 3%. I've also found another similar recommendation on SO: MySQL batch stmt with Statement.RETURN_GENERATED_KEYS .

这篇关于JDBC PreparedStatement，批处理更新和生成的密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！