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
}
Use Do-While because using while will skip 1st row always.