Home Spring Boot Fix - EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

Fix – EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

“EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0” is a common error when working with Spring JdbcTemplate queryForObject() operation. In this guide we will explore common reason for this issue and and best way to fix the it.

1. Root Cause for Issue

1.1. To understand root cause for the issue “EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0”, let’s reproduce the issue.

	public User findByUserName(String name) {
		
		return jdbcTemplate.queryForObject("SELECT * FROM USER WHERE USERNAME = ?", new Object[] { name },
					(rs, rowNum) -> mapUserResult(rs));
		
	}
	private User mapUserResult(final ResultSet rs) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("ID"));
		user.setUserName(rs.getString("USERNAME"));
		user.setPassword(rs.getString("PASSWORD"));
		user.setCreatedTime(rs.getDate("CREATEDTIME"));
		user.setUpdatedTime(rs.getDate("UPDATEDTIME"));
		user.setUserType(UserType.valueOf(rs.getString("USERTYPE")));
		user.setDateofBirth(rs.getDate("DATEOFBIRTH"));

		return user;
	}

1.2. I am trying to call findByUserName() by providing a “userName” that do not exist in database table.

	@Autowired
	private UserJdbcTemplate userRepository;

        //......
		User user = userRepository.findByUserName("Karolina");
       //.....

1.3. When I ran above code, I got the following exception.

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
	at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:97)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:784)
	at com.javabydeveloper.template.UserJdbcTemplate.findByUserName(UserJdbcTemplate.java:77)
	at com.javabydeveloper.template.UserJdbcTemplate$$FastClassBySpringCGLIB$$3009292a.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
	at com.javabydeveloper.template.UserJdbcTemplate$$EnhancerBySpringCGLIB$$c47de804.findByUserName(<generated>)

2. Best way to fix the exception

  1. You just throw exception, client will handle it?
  2. Return null and log the error?
  3. Just return an error message as Json String?
  4. Return empty object?
  5. Return an Optional value?

One of the case from the above cases may be the requirement, but how the other frameworks like JPA or Spring Data JPA handled this situation is, they return null or Optional.empty. Returning null or Optional.empty() would be the most common fix.

	public Optional<User> findByUserName(String name) {
		try {
			return jdbcTemplate.queryForObject("SELECT * FROM USER WHERE USERNAME = ?", new Object[] { name },
					(rs, rowNum) -> Optional.of(mapUserResult(rs)));
		} catch (EmptyResultDataAccessException e) {
			log.debug("No record found in database for "+name, e);
			return Optional.empty();
		}
	}

Testing :

		Optional<User> user = userRepository.findByUserName("Karolina");
		user.ifPresent(System.out::println); // prints only if user exist

3. Conclusion

“EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0” is a one of the common when working with Spring JdbcTemplate queryForObject() operation. In this guide we have shown the common fix for the issue.

Other Spring JDBC Examples :

  1. Spring JdbcTemplate – Stored Procedure
  2. Spring JdbcTemplate – Query
  3. Spring JdbcTemplate – Pagination
  4. Spring JdbcTemplate – IN Clause
  5. Spring JdbcTemplate – Auto-generated Keys
  6. Spring Boot – Loading Initial Data
  7. Spring JdbcTemplate – Batch update

4. References

  1. Spring Document
  2. Spring JdbcTemplate Query

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories