CODEWITHSUNDEEP
javaspringspring-dataspring-batch
Daniel C.
Daniel C.

Reputation: 40

Spring batch - use JdbcCursorItemReader to read from a DataSource and work with the ResultSet

I'm trying to use Spring Batch to create a Job that uses a DataSource (configure before) and runs a query. I want to be able to iterate through the returned ResultSet to create a new table with the returned data.

I can create a reader like this, but i don´t know how to iterate the results.

@Bean
public JdbcCursorItemReader<ResultSet> reader(String query, DataSource dataSource) {
    JdbcCursorItemReader<ResultSet> itemReader = new JdbcCursorItemReader<>();
    itemReader.setDataSource(dataSource);
    itemReader.setSql(query);
    return itemReader;
}

What should my ItemProcessor receive? This?

public class ExtractionItemProcessor implements ItemProcessor<ResultSet, String>{    
@Override
    public String process(ResultSet item) throws Exception {
        // transform the resultSet into a SQL INSERT
  }
}

EDIT: the only way I know the results of the query is by the ResultSet Metadata, so I can´t create a POJO and set the properties.

Upvotes: 0

Views: 12402

Answers (1)

Andrew S
Andrew S

Reputation: 2756

Create a POJO class to represent a record, such as Foo:

public class Foo {
     private final long id;
     // more properties

     public Foo(long id // ,...) {
         this.id = id;
         // set other properties
     }

     public long getId() {
         return id;
     }
}

so the reader will be a JdbcCursorItemReader<Foo>.

And create a RowMapper<Foo> such as:

public FooRowMapper implements implements RowMapper<Foo> {

    @Override
    public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
        long id = rs.getLong("id");
        // more properties 
        return new Foo(id // more properties);
    }
}

and set it on the reader: itemReader.setRowMapper(new FooRowMapper()).

The ExtractionItemProcessor will receive a Foo, and would look something like:

public class ExtractionItemProcessor implements ItemProcessor<Foo, String>{    
@Override
    public String process(Foo item) throws Exception {
    int someValue = transform(foo);
    return "INSERT INTO blah blah blah..." + someValue + "...";  // potentially dangerous - see SQL injection attack
  }
}

To take it further, perhaps ExtractionItemProcessor transformation of a Foo should create a Bar. Then it would look like:

public class ExtractionItemProcessor implements ItemProcessor<Foo, Bar>{    
@Override
    public Bar process(Foo item) throws Exception {
    int someValue = transform(foo);
    // more values....
       return new Bar(someValue // more values);
  }
}

So then the ItemWriter impelementation will take a List<Bar>, which knows how to safely insert Bars to another table.

Upvotes: 1

Related Questions