Stream result of JDBC query with JdbcTemplate

Question

I'm using Spring with Java.

I need to return a Stream of objects from the database query (I use ObjectMapper to map them to JSON).

The query results can be quite large (over 500k objects) so I don't want to store them in memory.

I've done it with JpaRepository.

I'd like to know how it can be done with JdbcTemplate and if there would be an advantage to do so?

I.e ... can we optimize the throughput and memory usage using JdbcTemplate or perhaps even other libraries.

My goal is really at the end to find the optimal way to run a query and print all objects to an output stream in terms of memory/time/processing.

Ligon

Upgrade to Spring 5.3 and use the newly added queryForStream methods. Or use a RowCallbackHandler if your intention is to write to an OutputStream. – Einsteinium

Answer 1

Yes, there will be an advantage with streams since it is a common abstraction for processing data without all of it being in memory. E.g. passing the stream to a HTTP response.

Spring 5.3

If you use Spring 5.3, there is a convenient method JdbcTemplate.queryForStream() that can be used like this:

String sql = "select * from table";

Stream<Person > stream = jdbcTemplate.queryForStream(sql, (resultSet, rowNum) -> {
    return new Person(resultSet.getInt(1), resultSet.getString(2));
});

Previous Spring versions

Older versions of JDBCTemplate does not have functionality for streams directly. But you can make a stream by using the underlying database connection:

String sql = "select * from table";

Connection connection = jdbcTemplate.getDataSource().getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();

PersonMapper personMapper = new PersonMapper();

Spliterator<Person> spliterator =
    Spliterators.spliteratorUnknownSize(
        new Iterator<Person>() {
            @Override public boolean hasNext() {
                try {
                    return !resultSet.isAfterLast();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            @Override public Person next() {
                try {
                    if (resultSet.isBeforeFirst()) {
                        resultSet.next();
                    }

                    Person result = new Person(resultSet.getInt(1), resultSet.getString(2));

                    resultSet.next();
                    return result;
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        },
        Spliterator.IMMUTABLE);

Runnable closer = () -> {
    try {
        resultSet.close();
        statement.close();
        connection.close();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
};

Stream<Person> = StreamSupport.stream(spliterator, false).onClose(closer);

Clevey

It's really worth mentioning that result of queryForStream needs to be closed once fully processed (e.g. through a try-with-resources clause) – Zindman

Answer 2

Look at all the query() methods of JdbcTemplate.

The ones that has a return type of void obviously don't build up the full data set in memory. They actually all take a RowCallbackHandler:

An interface used by JdbcTemplate for processing rows of a ResultSet on a per-row basis.

The processRow() callback method can then add the data to the streaming JSON text, ensuring minimal memory use.

That of course assumes that the JDBC driver doesn't load the entire ResultSet into memory, but that's a different issue, so solution (if needed) entirely depends on the JDBC driver in use.

Unhandled