Paged queries with JdbcTemplate

7
votes

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

2
javaspringjdbctemplatespring-jdbc
please do not ask why I'm going through a Java application for this, it's what the customer requires. It's also a good idea. – Sean Patrick Floyd
Could you elaborate on this please? – thomaux
Basically: I had Spring Batch in mind. A lot of what it does: chunk processing, transaction management, restartability etc. would not be possible with just SQL. – Sean Patrick Floyd
Ah of course, regarding your answer that's a logical statement :). Thanks for your time and help! – thomaux

2 Answers

6
votes

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

1
votes

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")));
            }

        }
);