Java – JDBC PreparedStatement, Batch Update and generated Keys

javajdbcprepared-statement

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

The code : 

        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");
        }
        }

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

The error : 

[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…

Thanks for your help,

F

Best Answer

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

Check out the accepted answer to the following question for details: PreparedStatement with 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 .

Related Topic