Philippe Marschall opened SPR-12662 and commented

Problem

From time to time we find ourselves working on queries that may return either one for no row. We feel this situation is currently not well addressed with JdbcTemplate. Current options include:

  • using #queryForObject and catching EmptyResultDataAccessException
  • using #query or #queryForList and checking the size of the list

Both of them feel rather cumbersome.

Proposal

We feel Java 8 Optionals are ideally suited for this problem. We therefore propose to add new queryForOptional methods with the following behaviour

  • no row: empty Optional
  • one row: present Optional
  • NULL: empty Optional
  • more than one row: IncorrectResultSizeDataAccessException

We feel these semantics are natural match for Optional. Mapping both no row and SQL NULL to an empty Optional may be a bit surprising at first however an Optional can not contain Java null and returning Java null would seem against the sprit of Optional. We feel the name fits well into the existing naming pattern and gives users a clear indication of the behaviour to expect without having to read the Javadoc first.

As the master in now on 5 which has a minimum of Java 8 these can be added directly to JdbcTemplate.

Issue Links: - #18474 Add iterable JDBC template to process large amount of data - #20599 Customize PreparedStatementCreatorFactory in getPreparedStatementCreator

Referenced from: pull request https://github.com/spring-projects/spring-framework/pull/724

13 votes, 15 watchers

Comment From: spring-projects-issues

Philippe Marschall commented

Here's the PR: https://github.com/spring-projects/spring-framework/pull/724

Comment From: spring-projects-issues

Pekka Männistö commented

Any news on this one?

Comment From: spring-projects-issues

Sam Brannen commented

Juergen Hoeller, what do you think about addressing this in the 5.0 time frame?

Comment From: spring-projects-issues

Philippe Marschall commented

I updated the PR since the master is now on 5 which has a minimum of Java 8.

Comment From: spring-projects-issues

Philippe Marschall commented

Any kind of feedback would be nice.

Comment From: spring-projects-issues

Stéphane Nicoll commented

My feedback is that there are a lot of method signatures in JdbcTemplate and adding a bunch of them isn't going to improve that. This is something that bugged me for quite a while and maybe we should address that first before adding more methods.

Comment From: spring-projects-issues

Kacper Stolc commented

I'm a huge supporter of this feature. Spring Framework and Spring Data JPA use Optionals, so JDBC Template should also use it, because it makes code more clear. Catching exception in order to handle "no result" case is not very elegant (assuming we are searching for at most 1 result).

@Stéphane Nicoll I agree that are many methods in JdbcTemplate and some of them could be removed. But in my view it shouldn't be the reason to skip implementing Optional support.

Comment From: spring-projects-issues

Stéphane Nicoll commented

Kacper Stolc I don't think I meant anywhere it was a reason to dismiss this.

Comment From: spring-projects-issues

Kacper Stolc commented

@Stéphane Nicoll I'm sorry, you are of course correct. What I meant was that if we want to remove unnecessary methods first (before implementing Optional support) then it could take many months/years before Optional support is finally added. And this ticket is already 2 years old.

It might be a good idea to create separate ticket for removing unnecessary methods.

Comment From: spring-projects-issues

Philippe Marschall commented

@Kacper Stolc I don't think there are any not needed methods. I understood Stéphane Nicoll that current functionality would be provided without so many overloaded methods.

Eg. something like

query(SQL)
 .withParameters(args)
 .withRowMapper(/* ... */)
 .asOptional()

but that would be a new class that replaces JdbcTemplate.

Comment From: spring-projects-issues

Philippe Marschall commented

I updated the PR and resolved the merge conflict. To summarize so far

  • The proposal does not overload or even change any of the existing queryForObject, queryForList, queryForMap, … methods.
  • Instead a new queryForOptional method is added. We believe this name fits well with the existing naming pattern used in JdbcTemplate and matches well with exceptions of users.
  • The semantics map easily and clearly to Optional
  • no row: empty Optional
  • one row: present Optional
  • more than one row: IncorrectResultSizeDataAccessException

Comment From: spring-projects-issues

Juergen Hoeller commented

On further review, instead of adding queryForOptional variants to an already long list of operations, I'm inclined to revisit this wholesale: as an alternative to JdbcTemplate as indicated above... along the lines of what we're currently doing with our reactive WebClient versus RestTemplate. We could not only do Optional return types by default there but also consider the use of Stream for list results.

However, we're running out of time for 5.0, so I'd rather tackle this as a 5.1 topic (late 2017).

Comment From: zimmi

@jhoeller If you don't mind, can you talk about the current plans on this? If a new abstraction is to be introduced, a possible inspiration could be fluent-jdbc. I find its API to be quite usable.

Comment From: rstoyanchev

Closing as discussed under #724. For the time being we have no plans to introduce a fluent alternative, that is other than the R2DBC DatabaseClient.