Support `Optional` return value in `JdbcTemplate`

[making]

Since DataAccessUtils.optionalResult was added in 6.1.0-M3 #27735 , I would like JdbcTempalte/NamedParameterJdbcTemplate to have methods that return Optional.

[jhoeller]

This has been extensively discussed a couple of years ago already, and I'm afraid this won't be happening, at least not in JdbcTemplate itself. There are too many overloaded methods there already. See #17262 and #724, specifically #17262 (comment) and #724 (comment). This issue is effectively a duplicate, so I'll close it as such.

On a related note, we got the semantic question of requiredSingleResult vs nullableSingleResult in JdbcTemplate already, with the latter being the baked-in queryForObject behavior whereas the former might often be desirable from a usability perspective. The only way to get such "required result" enforcement is through DataAccessUtils. From that perspective, our new Optional-based utility methods are appropriately placed there now for yet another single-result access variant.

As yet another stylistic variant, one may use queryForStream with a subsequent findFirst step, that's pretty close to what a hypothetic queryForOptional method would do. So at the expense of the additional .findFirst() call in the sequence, queryForStream provides a straightforward Optional access variant already. And for singleResult style enforcement of no more than 1 row in the result set, DataAccessUtils.singleResult(Stream) can be applied instead of findFirst.

Last but not least, there is the idea expressed at the end of the thread in #17262 that we might introduce a new WebClient style accessor next to the R2DBC DatabaseClient, with a modern fluent API as an alternative to the template API style. In the light of our newly introduced RestClient (#29552), this may get some traction again, maybe as a JdbcClient which can also unify usage of indexed and named parameters (rather than two separate template classes as we currently have it).

[making]

Thanks for the explanation. I understand the conclusions of past discussions.

If I understand correctly, the result of queryForStream should be closed and findFirst() cannot be easily chained as queryForStream(...).findFirst() can leak the resource. DataAccessUtils.singleResult(Stream) closes the Stream, so it seems more appropriate to use with queryForStream.
Is that correct?

JdbcClient like DatabaseClient sounds vey attractive and that's exactly what I really wanted!

[jhoeller]

Granted, there is the stream closing topic. Even terminal operations do not close the stream, so there has to be a rather inconvenient try block against any shortcut terminal operations which makes it less attractive for chaining indeed. I suppose stylistically, that makes the DataAccessUtils.optionalResult helper the best present option (with no-more-than-1-row enforcement which we usually want), in combination with JdbcTemplate.queryForList since combining it with queryForStream does not provide additional value.

As for JdbcClient, we have no ticket for it yet but it is rather likely that we'll finally give it a try for 6.1 now...

[making]

Created the ticket for JdbcClient #30931