How to execute a stored procedure in postgresql with spring batch?

Question

I'm doing batch processing with spring batch. First, It reads a csv file, after that, I want to update in the database, for that I use a stored procedure to do different action per row. The code is this:

Reader:

    @Bean
    @StepScope
    public ItemReader<Comprobante> csvFileReader( @Value("#{jobParameters['filename']}") String filename) throws UnexpectedInputException, ParseException{
        FlatFileItemReader<Comprobante> reader = new FlatFileItemReader<>();
        reader.setResource(new FileSystemResource(fileDir + filename));
        reader.setName("CSV-Reader");
        reader.setLinesToSkip(1);
        reader.setLineMapper(lineMapper());
        reader.open(new ExecutionContext());
        return reader;
    }


 @Bean
    public LineMapper<Comprobante> lineMapper() {
        DefaultLineMapper<Comprobante> defaultLineMapper = new DefaultLineMapper<>();
        DelimitedLineTokenizer delimitedLineTokenizer = new DelimitedLineTokenizer();
        delimitedLineTokenizer.setDelimiter(",");
        delimitedLineTokenizer.setStrict(false);
        delimitedLineTokenizer.setNames(new String[] {"cid", "ruc", "tipo", "serie", "numero","fecha", "monto"});
        BeanWrapperFieldSetMapper<Comprobante> beanWrapperFieldSetMapper = new BeanWrapperFieldSetMapper<>();
        beanWrapperFieldSetMapper.setTargetType(Comprobante.class);
        defaultLineMapper.setLineTokenizer(delimitedLineTokenizer);
        defaultLineMapper.setFieldSetMapper(beanWrapperFieldSetMapper);
        return  defaultLineMapper;
    }

Writer: 

 @Bean
    public ItemWriter<Comprobante> csvFileWrite (DataSource dataSource, NamedParameterJdbcTemplate jdbcTemplate){
        JdbcBatchItemWriter<Comprobante> writer = new JdbcBatchItemWriter<>();
        writer.setDataSource(dataSource);
        writer.setJdbcTemplate(jdbcTemplate);
        writer.setSql("select validateVoucher(? , ?);"); // This is the code with error.
        ItemPreparedStatementSetter<Comprobante> valueSetter = new comprobantePreparedStatementSetter();
        writer.setItemPreparedStatementSetter(valueSetter);
        return writer;
    }

The log shows this message:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select validateVoucher(? , ?);]; nested exception is java.sql.BatchUpdateException: Batch entry 0 select validateVoucher('40883884' , '0') was aborted: ERROR: function validatevoucher(character varying, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 8  Call getNextException to see other errors in the batch.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646) ~[spring-jdbc-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]

.......

Michael Minella · Accepted Answer · 2019-04-02 17:38:31Z

1

You are going to need to create your own ItemWriter implementation that handles this call. There is nothing within the framework out of the box that supports writing to a stored procedure.

edited Apr 2, 2019 at 17:38

answered Apr 2, 2019 at 15:34

Michael Minella

21.6k4 gold badges61 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cjvvasquez Over a year ago

But in SQL Server the processing with a stored procedure doesn't shows problems. When I use ( execute validateVoucher ?, ?; ) run good.

Michael Minella Over a year ago

Just because it works on one provider does not mean it will everywhere. I'm not saying you cannot run a stored proc from that ItemWriter, you may be able to. I'm saying that it's not something the ItemWriter is built to universally handle.

Collectives™ on Stack Overflow

How to execute a stored procedure in postgresql with spring batch?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related