Spring JDBC: Returning 0 or 1 rows

Question

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!

Answer 1

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.

Answer 2

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.