How to get generated id in spring batch template insert

In Spring Batch, when you perform an insert operation using a JdbcBatchItemWriter or a similar component, you might want to retrieve the generated ID for each inserted record. This is often the case when you are working with databases that generate IDs (such as auto-incrementing primary keys) upon insertion.

Here is an example of how you can achieve this using Spring Batch:

1. Configure your Job:Define your Spring Batch job with appropriate steps. In this example, we assume you have a FlatFileItemReader to read data from a flat file, a YourItemProcessor to process the data, and a JdbcBatchItemWriter to insert records into the database.

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private DataSource dataSource;

    @Autowired
    private YourItemProcessor yourItemProcessor;

    @Autowired
    private ItemReader<YourInputType> yourItemReader;

    @Bean
    public JdbcBatchItemWriter<YourOutputType> jdbcBatchItemWriter() {
        JdbcBatchItemWriter<YourOutputType> writer = new JdbcBatchItemWriter<>();
        writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
        writer.setSql("INSERT INTO your_table (column1, column2, ...) VALUES (:property1, :property2, ...)");
        writer.setDataSource(dataSource);
        return writer;
    }

    @Bean
    public Step yourStep() {
        return stepBuilderFactory.get("yourStep")
                .<YourInputType, YourOutputType>chunk(10)
                .reader(yourItemReader)
                .processor(yourItemProcessor)
                .writer(jdbcBatchItemWriter())
                .build();
    }

    @Bean
    public Job yourJob() {
        return jobBuilderFactory.get("yourJob")
                .start(yourStep())
                .build();
    }
}

Custom ItemWriter with Generated Key:

Create a custom ItemWriter that extends JdbcBatchItemWriter and overrides the write method to handle the generated keys.

public class CustomJdbcBatchItemWriter<T> extends JdbcBatchItemWriter<T> {

    @Override
    public void write(List<? extends T> items) throws Exception {
        List<Number> generatedKeys = new ArrayList<>();

        // Use a custom ItemPreparedStatementSetter to handle generated keys
        ItemPreparedStatementSetter<T> itemPreparedStatementSetter = getItemPreparedStatementSetter();

        for (T item : items) {
            SqlParameterSource parameterSource = itemPreparedStatementSetter.createPreparedStatement(item);
            KeyHolder keyHolder = new GeneratedKeyHolder();

            getJdbcTemplate().update(getSql(), parameterSource, keyHolder);

            Number generatedKey = keyHolder.getKey();
            if (generatedKey != null) {
                generatedKeys.add(generatedKey);
            }
        }

        // Process generated keys as needed
        processGeneratedKeys(generatedKeys);
    }

    // Custom method to process generated keys
    private void processGeneratedKeys(List<Number> generatedKeys) {
        // Implement your logic to handle the generated keys (e.g., logging, further processing)
        for (Number key : generatedKeys) {
            System.out.println("Generated Key: " + key);
        }
    }
}

Use Custom ItemWriter in Step Configuration:

Replace the JdbcBatchItemWriter bean in your step configuration with the custom CustomJdbcBatchItemWriter.

@Bean
public CustomJdbcBatchItemWriter<YourOutputType> customJdbcBatchItemWriter() {
    CustomJdbcBatchItemWriter<YourOutputType> writer = new CustomJdbcBatchItemWriter<>();
    writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
    writer.setSql("INSERT INTO your_table (column1, column2, ...) VALUES (:property1, :property2, ...)");
    writer.setDataSource(dataSource);
    return writer;
}

@Bean
public Step yourStep() {
    return stepBuilderFactory.get("yourStep")
            .<YourInputType, YourOutputType>chunk(10)
            .reader(yourItemReader)
            .processor(yourItemProcessor)
            .writer(customJdbcBatchItemWriter())  // Use the custom writer here
            .build();
}

With this configuration, the custom CustomJdbcBatchItemWriter will handle the generated keys during the insert operation. The processGeneratedKeys method can be customized based on your requirements for handling the generated keys.