如何为Spring Batch的JdbcBatchItemWriter添加计数器与日期字段

Hey there! Let's figure out how to add that counter and date field to your batch writer. I'll walk you through two practical approaches depending on your needs:

Option 1: Preprocess with an ItemProcessor (Control in Java) #

If you want full control over the counter and date values within your application, using an ItemProcessor is the way to go. Here's how to set it up:

Step 1: Update the Employee Class #

First, add the missing fields to your Employee model along with their getters and setters:

public class Employee {
    private Integer counter;
    private String firstName;
    private String lastName;
    private LocalDate loadDate; // Use LocalDateTime if you need timestamp instead of date

    // Getters and setters for all fields
}

Step 2: Create a Custom ItemProcessor #

This processor will handle incrementing the counter and setting the date for each item:

@Component
public class EmployeeBatchProcessor implements ItemProcessor<Employee, Employee> {
    // Use AtomicInteger for thread safety if using multi-threaded steps
    private final AtomicInteger batchCounter = new AtomicInteger(0);

    @Override
    public Employee process(Employee employee) throws Exception {
        // Increment counter for each item
        employee.setCounter(batchCounter.incrementAndGet());
        // Set current date (adjust to LocalDateTime.now() for time)
        employee.setLoadDate(LocalDate.now());
        return employee;
    }
}

Note: If you want the counter to reset for each batch (instead of being global), implement StepExecutionListener to reset the counter at the start of each step.

Step 3: Wire the Processor into Your Step #

Add the processor to your batch step configuration:

@Bean
public Step employeeProcessingStep(ItemReader<Employee> employeeReader,
                                   EmployeeBatchProcessor processor,
                                   JdbcBatchItemWriter<Employee> writer,
                                   JobRepository jobRepository,
                                   PlatformTransactionManager transactionManager) {
    return new StepBuilder("employeeProcessingStep", jobRepository)
            .<Employee, Employee>chunk(10, transactionManager)
            .reader(employeeReader)
            .processor(processor)
            .writer(writer)
            .build();
}

Step 4: Update the Writer's SQL #

Now you can reference the fields directly in your INSERT statement using parameter names:

@Bean
public JdbcBatchItemWriter<Employee> writer(DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder<Employee>()
            .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
            .sql("INSERT INTO t(counter, firstname, lastname, loaddate) VALUES (:counter, :firstName, :lastName, :loadDate)")
            .dataSource(dataSource)
            .build();
}

Option 2: Let the Database Handle It (Simpler Setup) #

If you don't need to control the counter/date in your application, you can let the database manage these fields automatically:

Step 1: Adjust Your Database Table #

Modify your table to use an auto-incrementing counter and a default date value:

CREATE TABLE t (
    counter INT AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    loaddate DATE DEFAULT CURRENT_DATE() -- Use DATETIME DEFAULT CURRENT_TIMESTAMP() for timestamp
);

Step 2: Simplify the Writer's SQL #

You no longer need to include the counter and loaddate in your INSERT statement—let the database handle them:

@Bean
public JdbcBatchItemWriter<Employee> writer(DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder<Employee>()
            .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
            .sql("INSERT INTO t(firstname, lastname) VALUES (:firstName, :lastName)")
            .dataSource(dataSource)
            .build();
}

Bonus: Batch-Specific Counter #

If you want the counter to start at 1 for each batch run (instead of a global increment), update the processor to implement StepExecutionListener:

@Component
public class EmployeeBatchProcessor implements ItemProcessor<Employee, Employee>, StepExecutionListener {
    private int batchCounter;

    @Override
    public void beforeStep(StepExecution stepExecution) {
        // Reset counter at the start of each step
        batchCounter = 0;
    }

    @Override
    public Employee process(Employee employee) throws Exception {
        batchCounter++;
        employee.setCounter(batchCounter);
        employee.setLoadDate(LocalDate.now());
        return employee;
    }

    @Override
    public ExitStatus afterStep(StepExecution stepExecution) {
        return ExitStatus.COMPLETED;
    }
}

Don't forget to register the listener in your step:

@Bean
public Step employeeProcessingStep(ItemReader<Employee> employeeReader,
                                   EmployeeBatchProcessor processor,
                                   JdbcBatchItemWriter<Employee> writer,
                                   JobRepository jobRepository,
                                   PlatformTransactionManager transactionManager) {
    return new StepBuilder("employeeProcessingStep", jobRepository)
            .<Employee, Employee>chunk(10, transactionManager)
            .reader(employeeReader)
            .processor(processor)
            .writer(writer)
            .listener(processor) // Register the listener here
            .build();
}

内容的提问来源于stack exchange，提问作者Ibrahim