Select record in batches Spring JDBCTemplate

Question

I have a table which has around 5 million records. I want to read all the records from this table and do some processing on them. Now I want to query these records in batches say 1000 in one go, process them and fetch next 1000 records and so on.

However JDBCTemplate.query method only returns List containing all the records in the table. Obviously I can not have 5 million records in memory.

Is there a way address my problem using Spring JDBC? Underlying database is going to be DB2 if that helps.

Solution 1

Try something like this

    jdbcTemplate.query(sql, new RowCallbackHandler() {
        public void processRow(ResultSet arg0) throws SQLException {
            // ...
        }
    });

this way you can process any number of records one by one

Solution 2

Read the javadoc of JdbcTemplate. There are plenty of other methods, also named query(), that don't return a list, and take a RowCallbackHandler or a ResultSetExtractor as argument. Use these ones.

To set the number of rows fetched at once by the resultset, override applyStatementSettings() and call Statement.setFetchSize()