When using SimpleJdbcInsert with the MySQL JDBC driver and executeBatch(), you can retrieve the generated keys after batch execution by setting the RETURN_GENERATED_KEYS flag in the Statement object. Here's how you can do it:

  1. Create a SimpleJdbcInsert Object: First, create a SimpleJdbcInsert object with the required DataSource and the table name for which you want to insert data.

    java
    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource) .withTableName("your_table_name") .usingGeneratedKeyColumns("id"); // Specify the column name of the generated key
  2. Prepare Batch Data: Prepare a list of maps or SqlParameterSource objects containing the data to be inserted.

    java
    List<Map<String, Object>> batchData = new ArrayList<>(); // Add data to batchData (each map represents a row to be inserted)
  3. Execute the Batch: Use the executeBatch() method of SimpleJdbcInsert to perform the batch insert.

    java
    int[] insertedRows = simpleJdbcInsert.executeBatch(batchData.toArray(new Map[0]));
  4. Retrieve Generated Keys: After executing the batch insert, you can retrieve the generated keys for each row inserted. To do this, get the underlying PreparedStatement used in the batch insert and set the RETURN_GENERATED_KEYS flag.

    java
    // Get the underlying PreparedStatement PreparedStatement preparedStatement = simpleJdbcInsert.getJdbcTemplate().getDataSource().getConnection() .prepareStatement(simpleJdbcInsert.getInsertString()); // Set the RETURN_GENERATED_KEYS flag preparedStatement.setQueryTimeout(10); preparedStatement.setFetchSize(1); // Fetch only one row (the generated key) per fetch preparedStatement.setGeneratedKeys(); // Execute the PreparedStatement ResultSet generatedKeys = preparedStatement.executeQuery(); // Process the generated keys (one generated key per inserted row) int index = 0; while (generatedKeys.next()) { long generatedKey = generatedKeys.getLong(1); // Assuming the generated key is a long System.out.println("Generated key for row " + index + ": " + generatedKey); index++; } // Close the ResultSet and PreparedStatement generatedKeys.close(); preparedStatement.close();

Make sure that the table has an auto-incrementing primary key column, and you specify that column name using the usingGeneratedKeyColumns() method when creating the SimpleJdbcInsert object.

Keep in mind that using executeBatch() to retrieve generated keys may not be the most efficient approach, especially for large batch inserts. If performance is a concern, consider using other approaches like individual insert statements with getGeneratedKeys() after each insert, or consider using a library like Spring Batch for efficient batch processing with generated keys retrieval.

Have questions or queries?
Get in Touch