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.

BookRepository.java

    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

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar