java - Can you use Springs JdbcTemplate to stream data

Question

Can Springs JdbcTemplate be used to query a database and make the result available as a Stream?

Answer 1

Yes - but not out of the box.

I found this very helpful article on how to do it: Using the Java 8 Stream API with Spring’s JdbcTemplate.

Inspired by this article I have made an improved implementation:

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Spliterator;
import java.util.Spliterators;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

@Slf4j
@Component
@RequiredArgsConstructor
public class QueryStreamer {

  private final NamedParameterJdbcTemplate jdbcTemplate;

  /**
   * Execute query and make result available for a Stream{@literal <T>} consumer
   * @param sql
   * @param parameters
   * @param clazz
   * @param consumer
   * @param <T>
   */
  public <T> void queryForStream(
    String sql,
    MapSqlParameterSource parameters,
    Class<T> clazz,
    java.util.function.Consumer<Stream<T>> consumer
  ) {
    queryForStream(sql, parameters, resultSetStream -> {
      BeanPropertyRowMapper<T> mapper = new TrimmingBeanPropertyRowMapper<>(clazz);
      consumer.accept(resultSetStream.map(r -> mapIt(r, mapper)));
      return null;
    });
  }

  // Build a Stream<ResultSet>
  private void queryForStream(
    String sql,
    MapSqlParameterSource parameters,
    java.util.function.UnaryOperator<Stream<ResultSet>> operator
  ) {
    jdbcTemplate.query(sql, parameters, resultSet -> {
      final ResultSetWrappingSqlRowSet rowSet = new ResultSetWrappingSqlRowSet(resultSet);
      final boolean parallel = false;

      Spliterator<ResultSet> spliterator = Spliterators.spliteratorUnknownSize(new Iterator<>() {
        @Override
        public boolean hasNext() {
          return rowSet.next();
        }

        @Override
        public ResultSet next() {
          return resultSet;
        }
      }, Spliterator.IMMUTABLE);
      return operator.apply(StreamSupport.stream(spliterator, parallel));
    });
  }

  private static <T> T mapIt(ResultSet resultSet, BeanPropertyRowMapper<T> mapper) {
    try {
      return mapper.mapRow(resultSet, 0);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

}

And this is how you could use it in a DAO - implemented in Kotlin because of the nice multiline string literal support:

import MyEntity
import QueryStreamer
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource
import org.springframework.stereotype.Component
import java.util.function.Consumer
import java.util.stream.Stream

@Component
open class MyEntityDAO(private val queryStreamer: QueryStreamer) {

  val sql = """
          SELECT column_1,
                 column_2,
                 column_3
            FROM my_entity_table
           WHERE some_criteria = 'met' 
          """.trimIndent()

  fun streamIt(consumer: Consumer<Stream<MyEntity>>) {
    queryStreamer.queryForStream(sql, MapSqlParameterSource(), MyEntity::class.java, consumer)
  }
}

That's it - have fun :-)