Postgres: getting an actual stream out of NamedParameterJdbcTemplate.

[garca7]

I'm using Postgres and I have a query whose result set is so large that does not fit into memory. When I call NamedParameterJdbcTemplate.queryForStream I get an out of memory error because the method attempts to read the whole result set before creating a stream based on the in-memory result.
Is there a way to get an actual stream out of the NamedParameterJdbcTemplate? I've managed to get something similar using a JDBC raw connection from Postgres (caveat: you need to disable auto commit for it to work) but in comparison is cumbersome not being able to use all the facilities provided by NamedParameterJdbcTemplate.

[quaff]

Have you tried jdbcTemplate.setFetchSize(fetchSize) ?

[jhoeller]

queryForStream performs on-demand ResultSet access behind that stream, so it's primarily up to the JDBC driver how it actually reads the data. Whatever you did to a raw JDBC connection, you should also be able to set up on NamedParameterJdbcTemplate to make it interact with the JDBC driver the same way.

[garca7]

Have you tried jdbcTemplate.setFetchSize(fetchSize) ?

I did. No difference

[garca7]

queryForStream performs on-demand ResultSet access behind that stream, so it's primarily up to the JDBC driver how it actually reads the data. Whatever you did to a raw JDBC connection, you should also be able to set up on NamedParameterJdbcTemplate to make it interact with the JDBC driver the same way.

To enable the behaviour on raw JDBC I just had to call setAutoCommit(false) on the raw JDBC connection returned by jdbcTemplate.getDataSource().getConnection()

I'd appreciate some guidance on how to do this. The first problem I see is that Spring's JdbcTemplate seems to get its own connection from the data source, so I don't have a chance to change auto commit on the connection.

[quaff]

I'm pretty sure it's controlled by fetchSize, maybe Postgres requires autoCommit=false as prerequisite, you could configure your DataSource to disable auto commit.

See https://jdbc.postgresql.org/documentation/query/#example52setting-fetch-size-to-turn-cursors-on-and-off

[garca7]

@quaff, you were in the end right. Setting the maxFetchSize value governs the creation of an actual stream of results from the database instead of streaming from an in-memory image of the results once all are retrieved.

However, simply setting the maxFetchSize value on an existing Spring-provided JdbcTemplate instance won't work when the NamedParameterJdbcTemplate is created based on the DataSource asscociated with the template, which is what I initially tried and the reason I said that there was no difference when changing that value.

You need to create the NamedParameterJdbcTemplate instance based on the that JDBC template instance. For example, given a Spring-provided JdbcTemplate instance template, the following code does create an actual stream of results fetching from Postgres on increments of 1000 rows:

jdbcTemplate.setMaxRows(1000);
var namedTemplate= new NamedParameterJdbcTemplate(jdbcTemplate);
var stream = namedTemplate.queryForStream(strQuery, params, this);

Closing the issue and thanks to everyone.