Paged queries with JdbcTemplate

Question

I'm currently working on a migration project, to migrate data from the old db to the new one (please do not ask why I'm going through a Java application for this, it's what the customer requires).

There was some, initial, code which I'm updating now. One of the things I'm changing is using Spring's JdbcTemplate rather then the boiler-plate code which was there.

Unfortunately, I haven't found a way yet to execute paged queries on a JdbcTemplate, analogue to the old code:

Statement statement = getConnection().createStatement(
    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchDirection(ResultSet.FETCH_FORWARD);
statement.setFetchSize(1000);
return statement.executeQuery();

The getConnection() just return a Connection object, created in plain JDBC code (it's not part of a SessionFactory or a framework implementation).

I would then loop over the resultset, mapping the rows one at a time. Does anyone know if there's an easy way to achieve the same functionality with JdbcTemplate?

TIA

Answer 1

I think the natural choice for such an Application is Spring Batch (read the impressive Features page)

Here are the sections that should be relevant to you:

ItemReaders and ItemWriters > DataBase
and in particular JdbcPagingItemReader

Answer 2

Do you mean sth. like this?

SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);

List<String> result = template.query("SELECT name FROM people WHERE id > ?",
    new RowMapper<String>() {

        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString("name");
        }

    }, 666
);

Or this:

template.getJdbcOperations().query("SELECT name FROM people WHERE id > ?",
        new Object[] { 666 },
        new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                System.out.println(String.format(
                    "Got '%s'", rs.getString("name")));
            }

        }
);