[FIXED] Executing query inside spring batch processor

Issue

In my project I need to execute a query inside Spring Batch processor in order to validate some field.

How can I do that?

EDIT: Adding sources:

This is definition of step:

@Bean
public Step step1(JdbcBatchItemWriter<CaricoDTO> step1Writer) {
    return stepBuilderFactory.get("step1").<CaricoDTO, CaricoDTO>chunk(10).reader(multiResourceItemReader())
            .processor(processorStep1()).writer(step1Writer).build();

}

This is definition of multiResourceItemReader:

@Bean
public MultiResourceItemReader<CaricoDTO> multiResourceItemReader() {
    MultiResourceItemReader<CaricoDTO> resourceItemReader = new MultiResourceItemReader<CaricoDTO>();
    ArrayList<Integer> indexesToRemove = new ArrayList<Integer>();
    Resource[] inputResources = null;
    PathMatchingResourcePatternResolver patternResolver = new PathMatchingResourcePatternResolver();
    try {
        inputResources = patternResolver.getResources(inputPath);
    } catch (IOException e) {
        e.printStackTrace();
    }

    resourceItemReader.setResources(inputResources);
    resourceItemReader.setDelegate(step1Reader());
    resourceItemReader.setComparator(new FileComparator());
    return resourceItemReader;
}

This is step1Reader:

@Bean
public FlatFileItemReader<CaricoDTO> step1Reader() {
    FlatFileItemReader<CaricoDTO> reader = new FlatFileItemReader<CaricoDTO>();
    reader.setLinesToSkip(1);
    reader.setLineMapper(new DefaultLineMapper<CaricoDTO>() {
        {
            setLineTokenizer(new DelimitedLineTokenizer("|") {
                {
                    setNames(new String[] { ..... });
                }
            });
            setFieldSetMapper(new BeanWrapperFieldSetMapper<CaricoDTO>() {
                {
                    setTargetType(CaricoDTO.class);
                }
            });
        }
    });
    return reader;
}

This is my processor bean:

@Bean
public CaricoDTOItemProcessorStep1 processorStep1() {
    CaricoDTOItemProcessorStep1 processorStep1 = new CaricoDTOItemProcessorStep1();
    return processorStep1;
}

This is my processor definition:

public class CaricoDTOItemProcessorStep1 implements ItemProcessor<CaricoDTO, CaricoDTO> {

    private String fileName;
    private static final Logger log = LoggerFactory.getLogger(CaricoDTOItemProcessorStep1.class);
    
    @Override
    public CaricoDTO process(CaricoDTO carico) throws Exception {

        carico.setDataCaricamento(new Date(System.currentTimeMillis()));
        carico.setFileName(carico.getResource().getFilename());
        return carico;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

}

And this is my writer:

@Bean
public JdbcBatchItemWriter<CaricoDTO> step1Writer(DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder<CaricoDTO>()
            .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
            .sql(....)
            .dataSource(dataSource).build();
}

Solution

Given little details in the main OP, I am assuming that the general solution would be accepted.

Given a classic Spring Batch project setup, you should have access to a org.springframework.jdbc.core.JdbcTemplate bean connected to the target javax.sql.DataSource that you can simply inject in one of your batch collaborator components:

• org.springframework.batch.item.ItemReader
• org.springframework.batch.item.ItemWriter
• org.springframework.batch.item.ItemProcessor

Assuming you need to perform some validation before proceeding with an item processing (and optionally discarding it), you can inject the JdbcTemplate bean, perform you query to validate your model invariants, then proceed accordingly:

public class CaricoDTOItemProcessorStep1 implements ItemProcessor<CaricoDTO, CaricoDTO> {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private String fileName;
    
    private static final Logger log = LoggerFactory.getLogger(CaricoDTOItemProcessorStep1.class);
    
    @Override
    public CaricoDTO process(CaricoDTO item) throws Exception {
        boolean someValue = jdbcTemplate.queryForObject("SELECT some_field FROM some_table WHERE some_other_field = 0", (rs, rowNum) -> rs.getBoolean(0));
        if (someValue) {
            carico.setDataCaricamento(new Date(System.currentTimeMillis()));
            carico.setFileName(carico.getResource().getFilename());
            return carico;
        } else {
            return null; // causes the item to be discarded from processing
        }
    }
}

Needless to say that your query would depend on your domain use-case and needs to be updated.

Answered By - tmarwen
Answer Checked By - David Marino (JavaFixing Volunteer)