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
References
About Author
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.
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:
/** * Work out column size if this is the first row, otherwise just count rows. * <p>Subclasses can perform custom extraction or processing * by overriding the {@code processRow(ResultSet, int)} method. * @see #processRow(java.sql.ResultSet, int) */ @Override public final void processRow(ResultSet rs) throws SQLException { if (this.rowCount == 0) { ResultSetMetaData rsmd = rs.getMetaData(); this.columnCount = rsmd.getColumnCount(); this.columnTypes = new int[this.columnCount]; this.columnNames = new String[this.columnCount]; for (int i = 0; i < this.columnCount; i++) { this.columnTypes[i] = rsmd.getColumnType(i + 1); this.columnNames[i] = JdbcUtils.lookupColumnName(rsmd, i + 1); } // could also get column names } processRow(rs, this.rowCount++); } /** * Subclasses may override this to perform custom extraction * or processing. This class's implementation does nothing. * @param rs ResultSet to extract data from. This method is * invoked for each row * @param rowNum number of the current row (starting from 0) */ protected void processRow(ResultSet rs, int rowNum) throws SQLException { }At the end of “JdbcTemplate.java” ,here is why :
@Override public Object extractData(ResultSet rs) throws SQLException { while (rs.next()) { this.rch.processRow(rs); } return null; }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.