Spring Batch JdbcBatchItemWriter setSql never throws exception

Question

My simple flow of batch process reads from a CSV file and write into a MySQL database (batch configuration is ok and works).

I'm using a custom implementation of JdbcBatchItemWriter in order to do the job and I'm actually making an Update in my writer constructor.

CsvReader.java

@Component
@StepScope
public class EducationCsvReader extends FlatFileItemReader {    
    public final static String CSV_FILE_NAME = "education.csv.file";

    @Value("#{jobParameters['"+ CSV_FILE_NAME +"']}")
    public void setResource(final String csvFileName) throws Exception {
        setResource(
                new FileSystemResource(csvFileName)
        );

    }

    public EducationCsvReader() {
        setLinesToSkip(1);
        setEncoding("UTF-8");
        setStrict(true);
        setLineMapper((line, num) -> {
            String[] values = line.split(";");
            return new Education()
                    .setName(values[2].trim())
                    .setId(Integer.parseInt(values[0].trim()));
        });

    }

}

my custom JdbcBatchItemWriter : AbstractJdbcBatchItemWriter.java

public abstract class AbstractJdbcBatchItemWriter<T> extends JdbcBatchItemWriter<T>{

    @Autowired
    public AbstractJdbcBatchItemWriter(String SQL_QUERY) {
        setSql(SQL_QUERY);
    }

    @Autowired
    @Override
    public void setItemSqlParameterSourceProvider(
            @Qualifier("beanPropertyItemSqlParameterSourceProvider") ItemSqlParameterSourceProvider provider){
        super.setItemSqlParameterSourceProvider(provider);
    }

    @Autowired
    @Override
    public void setDataSource(@Qualifier("mysqlDataSource") DataSource dataSource){
        super.setDataSource(dataSource);
    }
}

And here is my writer implementation : MySQLWriter.java

@Component
public class EducationMysqlWriter extends AbstractJdbcBatchItemWriter<Education> {
    public EducationMysqlWriter(){
        super("");
        try {
            setSql("UPDATE ecole SET nom=:name WHERE id=:id");
        } catch (EmptyResultDataAccessException exception){
            setSql("INSERT INTO ecole (nom, id) VALUES (:name, :id");
        }
    }

}

I need to update rows but if it fails (EmptyResultDataAccessException) I need to do an Insert. But EmptyResultDataAccessException is shown on log console and kills the job but the exception catching is never reachable into MySQLWriter.java ...

JdbcBatchItemWriter#setSql doesn't throw an exception because it doesn't do anything but assign a string to an instance variable. — Nathan Hughes
Yes I know, but I thought the try was going deeper through the write method (called after).. A way to throw it ? Overriding write method ? — eento

Nathan Hughes Nathan Hughes · Accepted Answer · 2015-02-02T14:56:24

JdbcBatchItemWriter#setSql doesn't throw an exception because it doesn't do anything but assign a string to an instance variable. The try block in the constructor doesn't have anything to do with the write method, it is executed when the itemwriter is first instantiated, while the write method is executed once for each chunk of items being processed. If you read the stacktrace I expect you'll see the JdbcBatchtemWriter is executing its write method and throwing the exception.

The ItemWriter is not going to get instantiated for each row so, assuming you will have some rows that need to be inserted and some that need to be updated, setting the sql string in the constructor does not seem like a good idea.

You could override the ItemWriter#write method, using a separate sql string for the insert, but it would be easier to use one string using the mysql upsert syntax:

INSERT INTO ecol (nom, id) VALUES (:name, :id)
  ON DUPLICATE KEY UPDATE nom = :name;

Spring Batch JdbcBatchItemWriter setSql never throws exception

1 Answers