The Error Scenario
Imagine you're building a user profile page. You try to fetch a specific user from the database by their ID, but the record doesn't exist. If your code uses JdbcTemplate like the example below, your application likely just crashed.
User user = jdbcTemplate.queryForObject(
"SELECT * FROM users WHERE id = ?",
new Object[]{userId},
userRowMapper
);
Instead of a smooth 404 response, you get a stack trace and a 500 Internal Server Error. This happens most often with JdbcTemplate or legacy custom JPA queries when a search for a userId like #404 returns nothing.
org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
Why This Happens
The queryForObject method is strict. It operates on an "exactly one" contract. If the database returns zero rows, it doesn't return null—it throws an exception. Conversely, if it finds two rows for a unique query, it throws IncorrectResultSizeDataAccessException.
Spring designed it this way to prevent NullPointerExceptions further down your service layer. However, in modern web development, a missing record is a common business case, not a system failure. Treating every missed lookup as an exception adds unnecessary overhead to your application's stack.
Quick Fix: Handling JdbcTemplate
If you need to stick with JdbcTemplate, you can avoid the crash with two different strategies.
1. The Try-Catch Block
This is the most direct approach. It works well if a missing record is actually a rare, exceptional state in your business logic.
try {
return jdbcTemplate.queryForObject(sql, params, mapper);
} catch (EmptyResultDataAccessException e) {
// Log the event and return null or a custom 404 exception
return null;
}
2. The Stream Approach (Recommended)
A cleaner alternative is to use the query method instead. Since query returns a List, it will simply return an empty list if nothing is found, avoiding the exception entirely. You can then use Java Streams to grab the first result.
return jdbcTemplate.query(sql, params, mapper)
.stream()
.findFirst()
.orElse(null);
Permanent Fix: Using Optional with Spring Data JPA
If you're using Spring Data JPA, you should stop returning raw entities. Since Spring Data 2.0, the repository pattern has built-in support for Optional<T>, which is the industry standard for handling potentially missing data.
The Modern Repository Pattern
Update your repository to return an Optional. This forces the calling code to acknowledge that the user might not exist.
public interface UserRepository extends JpaRepository<User, Long> {
// This method returns Optional.empty() instead of throwing an exception
Optional<User> findByEmail(String email);
}
Now your service layer logic becomes much more readable:
User user = userRepository.findByEmail(email)
.orElseThrow(() -> new UserNotFoundException("No user found with email: " + email));
By default, repository.findById(id) already returns an Optional. Use it to bypass the legacy EmptyResultDataAccessException entirely.
Best Practice: Global Exception Handling
Don't litter your codebase with identical try-catch blocks. If you're working on a large legacy project, handle the exception globally using @RestControllerAdvice. This ensures that every time a record is missing, the client receives a clean 404 status code instead of a broken connection.
@RestControllerAdvice
public class GlobalExceptionHandler {
@ExceptionHandler(EmptyResultDataAccessException.class)
public ResponseEntity<Map<String, String>> handleEmptyResult(EmptyResultDataAccessException ex) {
return ResponseEntity.status(HttpStatus.NOT_FOUND)
.body(Map.of("error", "Resource not found"));
}
}
Verification Steps
Verify your fix with an integration test. Use @DataJpaTest to check a non-existent ID, such as 999L, and ensure the Optional is empty.
@Test
void shouldReturnEmptyWhenUserIsMissing() {
Optional<User> result = userRepository.findById(999L);
assertTrue(result.isEmpty());
}
If you've implemented the JdbcTemplate fix, run your query against an empty table. The method should now return null or your custom exception without any stack traces appearing in your logs.