queryForObject() throws EmptyResultDataAccessException when record not found

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.

JdbcTemplate .java

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);
    }
DataAccessUtils.java

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;
	}		
  }

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
11 Comment threads
4 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
13 Comment authors
sunil GuptaRuslanchirag soniArielCyril Recent comment authors
newest oldest most voted
Ariel
Guest
Ariel

Do a regular query() and use DataAccessUtils.singleResult() instead, much easier, and does exactly what you need.

If you are returning a single column then SingleColumnRowMapper.newInstance() can be helpful.

Example:

return DataAccessUtils.singleResult(jdbcTemplate.query(query, SingleColumnRowMapper.newInstance(Integer.class)));

This returns an Integer or NULL if there are no rows.

Dan
Guest
Dan

I tend to prefer just doing query() and getting a list, then returning the first item if it exists, else null.

Fabio Almeida
Guest
Fabio Almeida

MKyoung… you need to read the Clean Code by Robert Martin. Never return null. The Spring Framework is correct. Null can be returned by the JDBC Driver error ou a OutOfMemory. Y’Know… You only need to catch this exception and handle.

Alessandro M.
Guest
Alessandro M.

// INSERT INTO a_table (column_a, nullable_column, column_b) VALUES (‘a’, NULL, ‘b’);

jdbc.queryForObject(“SELECT nullable_column FROM a_table”, params, String.class);

This is (also) why it does not return null.

Matt
Guest
Matt

There is a trend away from the over use of null. Especially in languages/frameworks that include or are based on functional principles. When you return null, you still have to do a check outside the call for null, aka: if (myVar == null) {…} or if (myVar != null) {…}. So why not just catch the exception and handle it accordingly somewhere up the call stack? Otherwise you would have to catch the exception, return null (thus losing the context of the exception), check for null somewhere up the stack, handle accordingly if you can figure out what happened. Chances… Read more »

Midos
Guest
Midos

In the solution above; what if i do not want to return null and I want to return an appropriate message in a json object response?

Alon
Guest
Alon

If it’s something that may occur often and is not really exception but a logical check, in my opinion it will be better to remove the catch and add check in mapRow

chirag soni
Guest
chirag soni

Hi, bro, you are handling EmptyResultDataAccessException thrown by queryForObject() method but I want to know that when we use spring JDBC then handling the exception will be taken care by spring only then why you are handling that in your example.

Ruslan
Guest
Ruslan

good answer!! thanks!!

Ruslan
Guest
Ruslan

Very usefull answer!!! thanks!

sunil Gupta
Guest
sunil Gupta

IN my case i am trying to mock test case but every time its returning null not able to mock an object , please take an above solution as reference and let me know