APNIC’s software team often come up with useful solutions to programming problems that they like to share with developers. In this post, APNIC’s Byron Ellacott shares a simple approach to expose the new techniques of Spring Framework’s JdbcTemplate, without exposing the costs of in-memory data caches or resource management.
Spring Framework’s JdbcTemplate is a workhorse of simple database access in a Spring application. However, it was designed in the days of Java 6, and doesn’t easily lend itself to modern techniques such as Java 8’s Stream API.
Here’s a simple approach to expose those new techniques, without exposing the costs of in-memory data caches or resource management.
The goals of the code are to offer the expressiveness of a Stream interface, without losing the resource management capabilities of JdbcTemplate or loading an entire result set into memory before processing it.
The code snippet below shows how the final implementation can be used.
In this case, the SQL statement itself could easily have done the filter expression, and could have done a DISTINCT select. But often the transformations to be done on an SQL query’s output are either difficult to do in SQL, or would require generation of moderately complex SQL on the fly.
Most developers are familiar with SQL, but few of us are experts in it. Using complex queries, trades performance for maintainability, and may be a premature optimisation.
https://gist.github.com/codebje/d9c852aff15a42f6017c
The approach taken should be fairly obvious from this example. Rather than entirely avoiding the callback driven nature of JdbcTemplate with the benefits that brings, this approach simply invokes a stream inside the callback, and returns the stream’s results from the enclosing method.
The code implements Spring’s ResultSetExtractor interface, which is a Single Abstract Method (SAM) interface, allowing the use of a lambda expression to implement it.
The implementation wraps the SQL ResultSet in an iterator, constructs a stream using the Spliterators and StreamSupport utility classes, and applies that to a Function taking a stream of row sets and returning a generic result.
https://gist.github.com/codebje/58d1b12e7a2d0ed31b3a
The end result is a simple way to use the Java 8 Stream API on SQL results in Spring Framework.
The views expressed by the authors of this blog are their own and do not necessarily reflect the views of APNIC. Please note a Code of Conduct applies to this blog.
This is a very useful class. But when there are no records in the database, it just throws and exception.. is it possible to handle empty list scenario..
Should just be able to put a try-catch block around the streamQuery call to catch and handle any scenarios that return an empty list. You could also edit streamQuery so as not to immediately return the jdbcTemplate.query() results, check the list size first and only return them if the list isn’t empty.
Thanks for finding that issue. I updated the gist to handle this – I’d be grateful for suggestions on how to do it more gracefully, though.
The full code with test suite is on the APNIC GitHub repository:
https://github.com/APNIC-net/spring-jdbctemplate-streams
Great idea, thanks Byron. Would be great to see Spring build in their own implementation using java 8’s streams sometime in the future.
Spring Boot latest 1.3.0.release already has streams and it is ok, but not great. The piece of the code you have written is much better and light weight, keep up the good work.
Which class specifically in Spring Boot 1.3.0 (or above) that has jdbc that returns stream?
Code above works only once. After first row is extracted !first always true and exception is throwed for second row.
It should be like
if (!rowSet.next() && !first) {
throw new NoSuchElementException();
}
first = false;
return rowSet;
I am getting invalid cursor state exception at
!rowSet.isLast() line
i tried with last method also but result was same exception.
could you please check it.
I’m wondering why don’t you return Stream instead?
Hi Winarto,
Thanks for the comment!
The JdbcOperations interface of Spring is intended to avoid resource leaks. It achieves this by disallowing the use of a database cursor beyond the lifetime of a call on an instance of JdbcOperations.
This means that a Stream constructed during a call to a JdbcOperations method won’t have access to an active database cursor object after the JbcdOperations method has returned. Any cursor object retained will already have been closed by the time the Stream is lazily evaluated.
A Stream could read all results eagerly into a buffer, but for large result sets this consumes a large amount of memory.
How to use it with one-to-many relationship?