Stream result of JDBC query with JDBCTemplate

user_14583421 0
Viewed 5746

I'm using spring 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.

jdbc spring java
2 Answers

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);
user_12713590

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.

user_52211490
Related