queryForObject() throws EmptyResultDataAccessException when record not found

By mkyong | Updated: April 30, 2014

Viewed: 6,814 (+21 pv/w)

Tags: spring jdbc

Reviewing a legacy project, and found this Spring JDBC code snippets :

  public User getUser(String username) {

	String sql = "SELECT * FROM USER WHERE username = ?";

	return getJdbcTemplate().queryForObject(
                sql,
                new Object[] { username },
		new RowMapper<UserAttempts>() {
		public UserAttempts mapRow(ResultSet rs, int rowNum) throws SQLException {

			User user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setAge(rs.getInt("age"));
			user.setLastModified(rs.getDate("lastModified"));

			return user;
		}

	});

  }

Problem

The developer assumes it will return a null when record not found.

	User user = abc.getUser("mkyong");
	if(user == null){
		//...do something
	}

The problem is, Spring throws an EmptyResultDataAccessException, instead of returning a null when record not found.

package org.springframework.jdbc.core;

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {

     //...
    public <T> T queryForObject(String sql, Object[] args, 
        RowMapper<T> rowMapper) throws DataAccessException {
	List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
	return DataAccessUtils.requiredSingleResult(results);
    }

package org.springframework.dao.support;

public abstract class DataAccessUtils {

    //...
    public static <T> T requiredSingleResult(Collection<T> results) 
         throws IncorrectResultSizeDataAccessException {
	int size = (results != null ? results.size() : 0);
	if (size == 0) {
		throw new EmptyResultDataAccessException(1);
	}
	if (results.size() > 1) {
		throw new IncorrectResultSizeDataAccessException(1, size);
	}
	return results.iterator().next();
    }

P.S Spring version 3.2.8.RELEASE

Solution

Returning null is pretty standard, wonder why Spring wants to throw an EmptyResultDataAccessException? To fix it, just catch the exception and return null.

  public User getUser(String username) {

	String sql = "SELECT * FROM USER WHERE username = ?";
		
	try {	
	      User user = getJdbcTemplate().queryForObject(
                 sql, 
                 new Object[] { username },
		 new RowMapper<UserAttempts>() {
		 public UserAttempts mapRow(ResultSet rs, int rowNum) throws SQLException {

			User user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setAge(rs.getInt("age"));
			user.setLastModified(rs.getDate("lastModified"));

			return user;
		 }
                });
	      return user;

	} catch (EmptyResultDataAccessException e) {
		return null;
	}		
  }