How to Retrieve Generated Keys from executeBatch Without Encountering ArrayIndexOutOfBoundsException?

Question

What is the proper way to obtain generated keys from executeBatch in JDBC to prevent ArrayIndexOutOfBoundsException?

String sql = "INSERT INTO my_table (column1, column2) VALUES (?, ?)", con = DriverManager.getConnection(url, user, password); 
try (PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    // Add batch
    pstmt.setString(1, "value1");
    pstmt.setString(2, "value2");
    pstmt.addBatch();
    int[] affectedRows = pstmt.executeBatch();
    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
        for (int i = 0; i < affectedRows.length; i++) {
            if (affectedRows[i] == Statement.SUCCESS_NO_INFO) { 
                // Handle success with no generated key
            } else {
                if (generatedKeys.next()) {
                    long key = generatedKeys.getLong(1);
                    // Use key as needed
                }
            }
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Answer

When using JDBC's executeBatch method to perform batch inserts, you might encounter an ArrayIndexOutOfBoundsException if you're not managing generated keys correctly. This issue arises primarily when the number of generated key results does not align with the number of operations performed in the batch, especially if some operations do not produce keys. Here’s how to handle it.

String sql = "INSERT INTO my_table (column1, column2) VALUES (?, ?)", con = DriverManager.getConnection(url, user, password); 
try (PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    // Add batch
    pstmt.setString(1, "value1");
    pstmt.setString(2, "value2");
    pstmt.addBatch();
    int[] affectedRows = pstmt.executeBatch();
    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
        for (int i = 0; i < affectedRows.length; i++) {
            if (affectedRows[i] == Statement.SUCCESS_NO_INFO) {
                // Handle success without generated key
            } else {
                if (generatedKeys.next()) {
                    long key = generatedKeys.getLong(1);
                    // Use the key as needed
                }
            }
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Causes

Mismatch between the number of rows affected and returned generated keys when the batch contains operations that do not produce generated keys.
Attempting to access generated keys without checking the state of the operations in the batch.

Solutions

Always check the result status of each operation in the affectedRows array. Use only the indices of rows that successfully generated keys.
Implement a robust check before accessing the generated key ResultSet to ensure that there are keys to retrieve.

Common Mistakes

Mistake: Not checking the return status of affected rows before accessing generated keys.

Solution: Always loop through affectedRows and only access generated keys when the rows succeeded.

Mistake: Assuming that all executions in the batch will produce generated keys, causing out-of-bounds access.

Solution: Handle each row independently and check for SUCCESS_NO_INFO or other statuses.

Helpers

JDBC executeBatch
retrieve generated keys
ArrayIndexOutOfBoundsException
JDBC batch processing
handle generated keys