12

I tried to google this question but could not find: Is there a recommended method in Spring jdbcTemplate which should be used when we expect 0 or 1 rows to be returned. queryForObject() will throw exception when no rows returned. queryForList() will require iterating through list, which is not a problem though. But am curious if there is a preferred/recommended method for 0 or 1 rows returned. thanks!

Krzysztof Krasoń
  • 26,515
  • 16
  • 89
  • 115
JavaTec
  • 965
  • 3
  • 15
  • 30

2 Answers2

23

There is

DataAccessUtils.singleResult(jdbcTemplate.queryForList(...));

which I believe is made exactly for these situations. It will return null if the collection is empty and throw an IncorrectResultSizeDataAccessException if more than 1 element found.

Roman
  • 6,486
  • 2
  • 23
  • 41
  • 1
    I wonder why spring jdbc don't have solution for this.. Design gap.. – Stunner Nov 05 '20 at 11:02
  • I do want to mention that i asked chatgpt to show me an example for DataAccessUtils.singleResult() and it produced an example which was catching exception when no result found. The documentation link you posted above helped me to be sure that chatgpt's answer is incorrect. And i asked it to verify. It apologized LOL. And confirmed that starting 4.2x, this method does not throw an exception if no result found – JavaTec Aug 03 '23 at 15:45
5

The options you listed are the only available. At least until there is Optional support in Spring (see this bug report):

Add Optional Support to JdbcTemplate

From time to time I find myself working on queries that may return either one for no row. I 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

Java 8 Optionals are ideally suited for this problem.

I would personally use queryForList and check isEmpty(), as putting logic inside catch is not clean.

Community
  • 1
  • 1
Krzysztof Krasoń
  • 26,515
  • 16
  • 89
  • 115
  • Thank you krzyk, This is good information, but within Spring JDBC, DataUtils (as suggested by Roman should solve my problem as well) – JavaTec Jun 07 '16 at 18:35
  • i see here queryForList solution has better advantage compared to DataUtils since if by mistake it returns more than one row , you do not want your program to throw error and end ubruptly.. – Stunner Nov 05 '20 at 11:06