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;
}
}
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.
// 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.
I tend to prefer just doing query() and getting a list, then returning the first item if it exists, else null.
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?
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 are that context is lost if null is returned for whatever exception. Null Pointer Exceptions should mean programmer error – something bad happened that is outside the normal execution of the program – aka an indicator of a bug. Exceptions provide context, null can and often is ambiguous as to what happened unless it has a single purpose – that purpose being a programmer error.
http://www.infoq.com/presentations/Null-References-The-Billion-Dollar-Mistake-Tony-Hoare
Returning exception is not good when getting empty result is not an ‘exceptional case’.
In terms of functional programming, it should return something like Scala’s Option object.
Programmer error being: “You idiot, you forgot to initialize me somewhere…”
you are Great ! thx 🙂
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
Very usefull answer!!! thanks!
good answer!! thanks!!
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.
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.
There is the Optional class for that. You also shouldn’t be throwing exceptions for expected behavior.
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
The exception is thrown by Spring when queryForObject is executed (before it maps the results), so you can’t handle it in the row mapper.