6

To make best use of the java8 stream and Spring4, I use the Stream API as follows on a JDBC resultSet that comes from Springs jsdbRestTemplate (code shortened and simplified):

public <T> T consumeResultStream(
    String query, 
    Function<Stream<String>, T> extractorFunction
) {
    return jdbcTemplate.query(
        query, 
        resultSet -> {
            Spliterator<String> spliterator = 
                Spliterators.spliteratorUnknownSize(
                    new Iterator<String>() {
                      @Override public boolean hasNext() {
                        return !resultSet.isAfterLast();
                      }
                      @Override public String next() {
                        String result = resultSet.getString(0);
                        resultSet.next();
                        return result;
                      }
                    }, 
                    Spliterator.IMMUTABLE);
           resultStream = StreamSupport.stream(
               spliterator, /*parallel*/ false);
       }
       return extractorFunction.apply(resultStream);
    });
}

This seems to work fine. Clients can use the stream Api like this without worrying about jdbc classes

List<T> myResult = consumeResultStream("SELECT ...", stream -> 
    stream.filter((String s) -> ...)
        .map(String s -> toT(s))
        .collect(toList()));

However, when I refactor (trying to provide the stream to client methods instead), like this:

    final Stream<String> stream = 
        jdbcTemplate.query(query, resultSet -> {
          // ... same as above
          return resultStream;
        });
    return extractorFunction.apply(stream);

I get 

org.springframework.jdbc.InvalidResultSetAccessException: 
  The object is already closed [90007-199]

So it seems the data can only be read within the jdbcTemplate.query() method. Is there a clean way I can circumvent this and return a lazy stream of the elements coming from the DB? Assume materializing the result and streaming that is not an option because of the size of the results (though pagination might be a better pattern).

tkruse
  • 10,222
  • 7
  • 53
  • 80
  • `jdbcTemplate.query(...)` encapsulates an operation which processes the result set and closes it. It can't return a lazy stream. On the other hand, when you provide an SQL statement and process the `ResultSet` manually, why not work with `Statement` or `PreparedStatement` directly? Besides that, it's straight-forward to implement a `Spliterator` rather than an `Iterator`. See https://stackoverflow.com/q/32209248/2711488 – Holger Jul 05 '19 at 09:11
  • I guess my question here can be closed as duplicate of the linked one. Now I worry if it's smart leave closing the streams to others. – tkruse Jul 05 '19 at 09:36
  • That's how, e.g `Files.lines()` and `Files.list()`, work. – Holger Jul 07 '19 at 21:19

1 Answers1

0

JdbcTemplate doesn't handle transaction beyond its invocation contrary to Spring JPA feature.
To not allow that the DB connection be closed, open a transaction from the client side that manipulates the returned lazy result.
Annotating it with @Transactional is generally enough : 

@Transactional
public void findLazyData(){
   Stream<String> result = dataAccessService.find(...);
   // where find() contains the JdbcTemplate invocation
}

Note the package origin : org.springframework.transaction.annotation.Transactional.

davidxxx
  • 125,838
  • 23
  • 214
  • 215
  • I tried, but still same result. I verified Transactions and tests are set up correctly running `TransactionAspectSupport.currentTransactionStatus()` in the method, which return a valid object (if I remove annotations I get NoTransactionException). – tkruse Jul 05 '19 at 07:03