Using the Java 8 Stream API with Spring’s JdbcTemplate

By on 5 Aug 2015

Category: Tech matters

Tags: , , ,

11 Comments

Blog home

Original image by Finetooth on Wikimedia.

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.

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.

The end result is a simple way to use the Java 8 Stream API on SQL results in Spring Framework.

Rate this article

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.

11 Comments

  1. Krishna

    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..

    Reply
    1. Jordan Tay

      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.

      Reply
  2. Jordan Tay

    Great idea, thanks Byron. Would be great to see Spring build in their own implementation using java 8’s streams sometime in the future.

    Reply
    1. Krishna

      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.

      Reply
  3. Alex

    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;

    Reply
  4. Arun

    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.

    Reply
    1. Byron Ellacott Post author

      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the math question * Time limit is exhausted. Please click the refresh button next to the equation below to reload the CAPTCHA (Note: your comment will not be deleted).

Top