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
3 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
zjnPreethamSunilYuri Recent comment authors
newest oldest most voted
Yuri
Guest
Yuri

There is an error in code sample in “2. Solution”. Line “while (resultSet.next()) {” is illegal here. Javadoc directly forbids to call next(): “This method should not call next() on the ResultSet; it is only supposed to extract values of the current row.

Correct sample must be such:
public void processRow(ResultSet resultSet) throws SQLException {

String name = resultSet.getString(“Name”);
// process it

}

zjn
Guest
zjn

hasNext() then call next()

Preetham
Guest
Preetham

Can we able to write Junit Mock test to test the inner class for the logic present inside RowCallbackHandler ??

Sunil
Guest
Sunil

Use Do-While because using while will skip 1st row always.

Featured Posts