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
$ cd spring-jdbc
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
}
Thanks for pointing, was having a hard time trying to figure what was wrong.
hasNext() then call next()
No hasNext method is available.
you are wrong, you don’t need to use while loop again, beause jdbctemplate has used while loop, you only need write code about prcessing resultSet
How does it solve the OOM error? For ex: I have 3L records which are mapped to a java object using rowmapper. Now, if we use like how it mentioned here, it will still create 3L java object and add to a list. Wouldn’t it still give OOM error – java heap size? Thanks!
while loop is not needed here,and you should see what spring does in this metod below:
At the end of “JdbcTemplate.java” ,here is why :
Can we able to write Junit Mock test to test the inner class for the logic present inside RowCallbackHandler ??
Use Do-While because using while will skip 1st row always.
Yes you are right, I faced issue as used while only. Do-While fixed my issue.
Why people disliking this? I don’t get it.