1. Overview
In this short tutorial, we’ll look at the Spring JdbcTemplate “EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0” exception.
First, we’ll discuss in detail the root cause of this exception. Then, we’ll see how to reproduce it using a practical example, and finally learn how to fix it.
2. The Cause
Spring JdbcTemplate class provides convenient ways to execute SQL queries and retrieve the results. It uses the JDBC API under the hood.
Typically, JdbcTemplate throws EmptyResultDataAccessException when a result was expected to have at least one row, but zero rows were actually returned.
Now that we know what the exception means, let’s see how to reproduce it and fix it using a practical example.
3. Reproducing the Exception
For instance, let’s consider the Employee class:
public class Employee {
private int id;
private String firstName;
private String lastName;
public Employee(int id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
// standard getters and setters
}
Next, let’s create a data access object (DAO) class that uses JdbcTemplate to handle SQL queries:
public class EmployeeDAO {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
It’s worth noting that since JdbcTemplate needs a DataSource object, we inject it in the setter method.
Now, we’ll add a method that retrieves an Employee object by id. To do so, let’s use the queryForObject() method provided by the JdbcTemplate class:
public Employee getEmployeeById(int id) {
RowMapper<Employee> employeeRowMapper = (rs, rowNum) -> new Employee(rs.getInt("ID"), rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"));
return jdbcTemplate.queryForObject("SELECT * FROM EMPLOYEE WHERE id=?", employeeRowMapper, id);
}
As we can see, the first parameter denotes the SQL query. The second parameter represents the RowMapper used to map the ResultSet into an Employee object.
As a matter of fact, queryForObject() expects exactly one row to be returned. So, if we pass an id that doesn’t exist, it simply throws EmptyResultDataAccessException.
Let’s confirm this using a test:
@Test(expected = EmptyResultDataAccessException.class)
public void whenIdNotExist_thenThrowEmptyResultDataAccessException() {
EmployeeDAO employeeDAO = new EmployeeDAO();
ReflectionTestUtils.setField(employeeDAO, "jdbcTemplate", jdbcTemplate);
Mockito.when(jdbcTemplate.queryForObject(anyString(), ArgumentMatchers.<RowMapper<Employee>> any(), anyInt()))
.thenThrow(EmptyResultDataAccessException.class);
employeeDAO.getEmployeeById(1);
}
Since there’s no employee with the given id, the specified query returns zero rows. As a result, JdbcTemplate fails with EmptyResultDataAccessException.
4. Fixing the Exception
The easiest solution would be catching the exception and then returning null instead:
public Employee getEmployeeByIdV2(int id) {
RowMapper<Employee> employeeRowMapper = (rs, rowNum) -> new Employee(rs.getInt("ID"), rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"));
try {
return jdbcTemplate.queryForObject("SELECT * FROM EMPLOYEE WHERE id=?", employeeRowMapper, id);
} catch (EmptyResultDataAccessException e) {
return null;
}
}
That way, we make sure to return null when the result of the SQL query is empty.
Now, let’s add another test case to confirm that everything works as expected:
@Test
public void whenIdNotExist_thenReturnNull() {
EmployeeDAO employeeDAO = new EmployeeDAO();
ReflectionTestUtils.setField(employeeDAO, "jdbcTemplate", jdbcTemplate);
Mockito.when(jdbcTemplate.queryForObject(anyString(), ArgumentMatchers.<RowMapper<Employee>> any(), anyInt()))
.thenReturn(null);
assertNull(employeeDAO.getEmployeeByIdV2(1));
}
5. Conclusion
In this short tutorial, we discussed in detail what causes JdbcTemplate to throw the exception “EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0”.
Along the way, we saw how to produce the exception and how to fix it using practical examples.
As always, the full source code of the examples is available over on GitHub.
