Fix EmptyResultDataAccessException When Using JdbcTemplate

Last updated: January 8, 2024

Written by: Abderrahim Azhrioun

Reviewed by: Luis Javier Peris

Spring Persistence

JDBC

Refactor Java code safely — and automatically — with OpenRewrite.

Refactoring big codebases by hand is slow, risky, and easy to put off. That’s where OpenRewrite comes in. The open-source framework for large-scale, automated code transformations helps teams modernize safely and consistently.

Each month, the creators and maintainers of OpenRewrite at Moderne run live, hands-on training sessions — one for newcomers and one for experienced users. You’ll see how recipes work, how to apply them across projects, and how to modernize code with confidence.

Join the next session, bring your questions, and learn how to automate the kind of work that usually eats your sprint time.

In distributed systems, managing multi-step processes (e.g., validating a driver, calculating fares, notifying users) can be difficult. We need to manage state, scattered retry logic, and maintain context when services fail.

Dapr Workflows solves this via Durable Execution which includes automatic state persistence, replaying workflows after failures and built-in resilience through retries, timeouts and error handling.

In this tutorial, we'll see how to orchestrate a multi-step flow for a ride-hailing application by integrating Dapr Workflows and Spring Boot:

>> Dapr Workflows With PubSub

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.

The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.