StoredProcedureItemReader example

spring batch storedprocedureitemreader example
spring batch storedprocedureitemreader example

The Spring Batch StoredProcedureItemReader is similar to JdbcCursorItemReader where for common cases we replace the SQL property with procedure name. The JdbcCursorItemReader runs a query to obtain a cursor whereas Spring Batch StoredProcedureReader runs a stored procedure to get a cursor.

The stored procedure can return the cursor in three different ways:

  • As a returned¬†ResultSet¬† – This is used by SQL Server, Sybase, DB2, Derby, and MySQL.
  • As a ref-cursor returned as an out parameter – This is used by Oracle and Postgresql
  • As the return value of a stored function call.

Configuring spring batch StoredProcedureItemReader


Define RowMapper for StoredProcedureItemReader

public class StudentResultRowMapper implements RowMapper<Student> {
    @Override
    public Student mapRow(ResultSet rs, int i) throws SQLException {
        Student student = new Student();
        student.setId(rs.getLong("id"));
        student.setRollNumber(rs.getString("roll_number"));
        student.setName(rs.getString("name"));
        return student;
    }
}

Configure StoredProcedureItemReader

    @Bean
    public StoredProcedureItemReader reader() {
        StoredProcedureItemReader reader = new StoredProcedureItemReader();
        reader.setDataSource(dataSource);
        reader.setProcedureName("student_procedure");
        reader.setRowMapper(new StudentResultRowMapper());
        reader.setRefCursorPosition(1);
        return reader;
    }

The StoredProcedureItemReader class extends AbstractCursorItemReader class.


That’s all about the Spring batch StoredProcedureItemReader example.

See docs for more details.


Spring Data JPA tutorials.

Spring Data JPA and Hibernate tutorials using Spring Boot and Oracle.