Spring JdbcTemplate Handle Large ResultSet

Spring JdbcTemplate example to get a large ResultSet and process it.

P.S Tested with Java 8 and Spring JDBC 5.1.4.RELEASE

1. Get large ResultSet

1.1 Below is a classic findAll to get all data from a table.

    public List<Book> findAll() {
        return jdbcTemplate.query(
                "select * from books",
                (rs, rowNum) ->
                        new Book(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
                        )
        );

    }

Run it, for small data, no problem.

	List<Book> list = bookRepository.findAll();

	for (Book book : list) {
		//process it
	}

If the table contains over millions of data, the RowMapper in findAll method will busy converting objects and put all objects into a List, if the object size is larger than the Java heap space, see below error:

java.lang.OutOfMemoryError: Java heap space

2. Solution

We can increase the heap size, but a better solution is to use RowCallbackHandler to process the large ResultSet on a per-row basis.

import org.springframework.jdbc.core.RowCallbackHandler;

	jdbcTemplate.query("select * from books", new RowCallbackHandler() {
		public void processRow(ResultSet resultSet) throws SQLException {
			while (resultSet.next()) {
				String name = resultSet.getString("Name");
				// process it
			}
		}
	});

Download Source Code

References

• RowCallbackHandler JavaDocs
• Using the Java 8 Stream API with Spring’s JdbcTemplate
• Spring JDBC docs
• Spring Boot JDBC Examples_
• Java JDBC Tutorial