Spring Batch Dynamic Insert and Update with DB to DB

Question

There is scenario where we want to load data from DB to DB . But we want to check if data is already present in target system then update it otherwise insert it into DB.

We are using below approach:

 @Bean
ItemWriter<Student> onosItemWriter1() {
    JdbcBatchItemWriter<Student> databaseItemWriter = new JdbcBatchItemWriter<>();
    databaseItemWriter.setDataSource(dataSource);
    databaseItemWriter.setJdbcTemplate(namedParameterJdbcTemplate);
    databaseItemWriter.setSql(INSERT_QUERY);
   ItemPreparedStatementSetter<Student> valueSetter = new ItemPreparedStatementSetter<Student>() {

        @Override
        public void setValues(Student student, PreparedStatement statement) throws SQLException {
            if (Student.getId() < 0) {
                log.info("Inserting!");
                databaseItemWriter.setSql(INSERT_QUERY);
                statement.setString(1, student.getName());
                statement.setString(2, student.getEmail());
            } else {
                log.info("updateing!!!!");
                databaseItemWriter.setSql(UPDATE_QUERY);
                statement.setString(1, student.getName());
                statement.setString(2, student.getEmail());
            }
        }
    };
    databaseItemWriter.setItemPreparedStatementSetter(valueSetter);
    return databaseItemWriter;
}

above its working only for insert. How I can do it with one JDBC batch Item writer to update it as well dynamically. if record is already present in chunks.

score 1 · Answer 1 · answered Dec 09 '22 at 08:23

The code you shared won't work, because you are configuring the query on the item writer based on a runtime information from the item itself (Student.getId() < 0).

we want to check if data is already present in target system then update it otherwise insert it into DB

I would keep it simple and write a custom item writer like follows:

public class StudentItemWriter implements ItemWriter<Student> {
    
    private static final String INSERT_QUERY = "insert into student ...";
    private static final String UPDATE_QUERY = "update student set ... where id = ?";
    
    private JdbcTemplate jdbcTemplate;
    
    public StudentItemWriter(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void write(List<? extends Student> students) throws Exception {
        for(Student student : students) {
            int updated = jdbcTemplate.update(UPDATE_QUERY,...);
            if(updated == 0) {
                jdbcTemplate.update(INSERT_QUERY,...);  
            }                               
        }
    }

}

Will it work in case of chunk as we want to process it in Chunk — Gaurav Khandelwal, Dec 09 '22 at 15:40
Yes. There is a `for` loop in the `write` method iterating over all items of the chunk. So all items will be inserted/updated in a single transaction or none of them will be inserted/updated (meaning the "all or nothing" semantics will be applied with that writer). — Mahmoud Ben Hassine, Dec 09 '22 at 16:04

Spring Batch Dynamic Insert and Update with DB to DB

1 Answers1