JDBC PreparedStatement, Batch Update and generated Keys

Question

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

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

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 .

edited May 23 '17 at 11:51

Community

1
1

answered Jan 30 '12 at 16:29

Olaf

6,249
1
19
37

This link is about using only a preparedStatement. My issue is when I'm trying to retrieve generated keys in a preparedStatement executed in batch ... Slightly different. – Farid Jan 31 '12 at 07:48
@Farid: Did you try specifying RETURN_GENERATED_KEYS when preparing the statement? Something like: stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); – Olaf Jan 31 '12 at 14:17
@Farid: Would you consider inserting records one by one and then just making 1 commit explicitly instead of the batching? – Olaf Jan 31 '12 at 18:13
I'm considering it. As I'm inserting around 1 million of records I suspect it's gonna be a performance issue to not use batch mode ... – Farid Feb 01 '12 at 07:27
Some time ago I was doing benchmarking of different ways of loading data in Oracle. I was loading records with 3 fields, total length of 100 byte. The performance seemed to be more dependent on the commit size, peaking between 2500 and 10K records per commit. I was using batching, but there were no discernible difference between batches of 30 and 1000 within a commit. A single batch of 10K records within commit performed noticeably worse. Another consideration: when inserting 1M records and using keys generated from a sequence, setting CACHE to 1000 on that sequence is remarkably useful. – Olaf Feb 01 '12 at 13:50

JDBC PreparedStatement, Batch Update and generated Keys

1 Answers1

Linked