Affects: \

Please refer to the demo project that replicates the issue. When using JdbcTemplate.queryForObject() and subsequently calling the .findFirst() method, the connection is not getting closed, hense the connection pool runs dry very quickly.

jdbcTemplate.queryForStream("SELECT VALUE FROM TEST WHERE KEY = ?",
                (rs, i) -> rs.getString("VALUE"), key)
                .findFirst();

The result of the execution of the following code is:

2022-01-30 13:00:38.783 DEBUG 19628 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=1)
2022-01-30 13:00:38.783 DEBUG 19628 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Fill pool skipped, pool is at sufficient level.
2022-01-30 13:00:39.585 DEBUG 19628 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=0)
2022-01-30 13:00:39.585  INFO 19628 --- [           main] ConditionEvaluationReportLoggingListener : 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2022-01-30 13:00:39.616 ERROR 19628 --- [           main] o.s.boot.SpringApplication               : Application run failed

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:772) ~[spring-boot-2.6.3.jar:2.6.3]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:753) ~[spring-boot-2.6.3.jar:2.6.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:309) ~[spring-boot-2.6.3.jar:2.6.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303) ~[spring-boot-2.6.3.jar:2.6.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292) ~[spring-boot-2.6.3.jar:2.6.3]
    at demo.Demo.main(Demo.java:21) ~[main/:na]
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30018ms.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:83) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at org.springframework.jdbc.core.JdbcTemplate.queryForStream(JdbcTemplate.java:834) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at org.springframework.jdbc.core.JdbcTemplate.queryForStream(JdbcTemplate.java:863) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at demo.JdbcRepo.getFromStream(JdbcRepo.java:32) ~[main/:na]
    at demo.JdbcRepo$$FastClassBySpringCGLIB$$87a3d4d8.invoke(<generated>) ~[main/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783) ~[spring-aop-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) ~[spring-aop-5.3.15.jar:5.3.15]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) ~[spring-aop-5.3.15.jar:5.3.15]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698) ~[spring-aop-5.3.15.jar:5.3.15]
    at demo.JdbcRepo$$EnhancerBySpringCGLIB$$c0949ac4.getFromStream(<generated>) ~[main/:na]
    at demo.Demo.run(Demo.java:49) ~[main/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:769) ~[spring-boot-2.6.3.jar:2.6.3]
    ... 5 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30018ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117) ~[spring-jdbc-5.3.15.jar:5.3.15]
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-5.3.15.jar:5.3.15]
    ... 21 common frames omitted

Comment From: sbrannen

As stated in the Javadoc for org.springframework.jdbc.core.JdbcOperations.queryForStream(String, RowMapper<T>, Object...), the caller is responsible for closing the stream.

Returns: the result Stream, containing mapped objects, needing to be closed once fully processed (e.g. through a try-with-resources clause)

As you can see in the following code listing, the Stream returned has an onClose() callback associated with it that will close the connection.

https://github.com/spring-projects/spring-framework/blob/e3529453d1682257162fdfeaa0e3f0c12008a67e/spring-jdbc/src/main/java/org/springframework/jdbc/core/JdbcTemplate.java#L840-L847

In light of that, I am closing this issue as "works as designed".

As a side note, if you are retrieving a single value from the database, have you considered using queryForObject(...) with a limit 1 clause in your SQL statement (or whatever syntax is supported by your database to return a single result)?

In other words, why are you using a stream for a single result?

Comment From: arciszewski

Thanks for explanation. I thought that the stream terminal methods also close the internal connection. When I modified my example code to use try-with-resources it worked as expected. I tried to use the queryForStream(...) and findFirst() because in my particular case I know there could be either one or none results, hense I wanted to use Optional as a return type. And Stream > Optional seemed a reasonable choice.

Comment From: jeanxu1

I am seeing the same issue. it is not that intuitive and I thought it is a bug for connection leak as well.

What is the use case for queryForStream() then? can you provide example of the right way of using it?

@sbrannen

Comment From: ryber

I agree with other posters that this violates the principle of least surprise, I would never expect that I would have to close the connection because I don't do that with any other of the methods in that class.

Comment From: lcy2317

Adding Transaction annotation also helps, but I still think queryForStream() should be avoided using, using jdbctemplate.query() instead.

Comment From: sbrannen

I tried to use the queryForStream(...) and findFirst() because in my particular case I know there could be either one or none results, hense I wanted to use Optional as a return type. And Stream > Optional seemed a reasonable choice.

If you insist on having Optional semantics when using JdbcTemplate, see all comments in #30927 for details.

Note, however, that as of Spring Framework 6.1 the recommend way to have Optional semantics is via the fluent API in the new JdbcClient. For a concrete example, see https://github.com/spring-projects/spring-framework/issues/30931#issuecomment-1649988290.

Comment From: sbrannen

What is the use case for queryForStream() then? can you provide example of the right way of using it?

I don't believe we have an actual example in the documentation; however, you can see an example in our test suite:

https://github.com/spring-projects/spring-framework/blob/232225b2aab29910f62d7d6a441cdb0af4269177/spring-jdbc/src/test/java/org/springframework/jdbc/core/JdbcTemplateQueryTests.java#L383-L392

Feel free to open a new ticket to suggest that an example be included in the reference manual.

Comment From: sbrannen

I agree with other posters that this violates the principle of least surprise, I would never expect that I would have to close the connection because I don't do that with any other of the methods in that class.

Although it may be surprising, it is sometimes the case that one must close a Stream in Java.

For example, the Stream returned from java.nio.file.Files.walk(Path, int, FileVisitOption...) must also be manually closed (or used in try-with-resources block).

queryForStream(...) is also one of those cases, and it's documented as such.