Paged queries with JdbcTemplate

java spring jdbctemplate spring-jdbc
thomaux picture thomaux · Nov 25, 2010 · Viewed 8.6k times · Source

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

Sean Patrick Floyd picture Sean Patrick Floyd · Nov 30, 2010

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

Related questions

Spring JDBC Template for calling Stored Procedures

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template? Say, I have a stored procedure that declares both IN and OUT parameters, something like this: mypkg.doSomething( id OUT int, name IN String, …

Read More
JPA vs Spring JdbcTemplate

For a new project is JPA always the recommended tool for handling relational data or are there scenarios where Spring JdbcTemplate is a better choice? Some factors to consider in your response: new database schema vs pre-existing schema and tables …

Read More
NamedParameterJdbcTemplate vs JdbcTemplate

I'm a beginner to Spring3.x , I'm learning Spring DAO support. I want to know the difference between NamedParameterJdbcTemplate and JdbcTemplate. Which one is the best by means of performance. And when to go for NamedParameterJdbcTemplate and when to go …

Read More