In this brief tutorial, we are going to shed light on the Spring JDBC EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 exception.

First, we’ll explain what causes the JdbcTemplate class to throw the exception.

Then, we’ll showcase how to avoid and fix EmptyResultDataAccessException in practice.

JdbcTemplate.queryForObject() Method

JdbcTemplate comes with a set of methods that we can use to connect to the database and execute SQL queries.

Among these methods, we find queryForObject(). The main purpose of this method is to execute an SQL query and return a single record.

With that being said, JdbcTemplate throws EmptyResultDataAccessException when the expected result contains zero records.

Hence the message: “EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0”.

Throwing EmptyResultDataAccessException

Now that we know the root reason behind the exception, let’s see how we can reproduce it using a practical example.

For instance, let’s consider the Person class:

    
        public class Person {
            private int id;
            private String firstName;
            private String lastName;
            public int getId() {
                return id;
            }
            public void setId(int id) {
                this.id = id;
            }
            public String getFirstName() {
                return firstName;
            }
            public void setFirstName(String firstName) {
                this.firstName = firstName;
            }
            public String getLastName() {
                return lastName;
            }
            public void setLastName(String lastName) {
                this.lastName = lastName;
            }
        }

Here, we are going to use H2 as a database. So, let’s add the necessary data source configuration:

    
        @Configuration
        public class SpringJdbcConfig {
            @Bean
            public DataSource dataSource() {
                return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2)
                    .addScript("classpath:schema.sql")
                    .addScript("classpath:data.sql")
                    .build();
            }
        }

The content of schema.sql and data.sql files:

    
        // schema.sql
        DROP TABLE IF EXISTS person;
        CREATE TABLE person(
            id INT AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(200),
            last_name VARCHAR(200)
        )
        // data.sql
        INSERT INTO person (first_name, last_name) VALUES('Jean', 'Depp');

Next, we will create a DAO class and inject JdbcTemplate as a dependency.

Then, we are going to create a method to return a Person object by id using the queryForObject() method:

    
        public class PersonDAO {
            private JdbcTemplate jdbcTemplate;
            public JdbcTemplate getJdbcTemplate() {
                return jdbcTemplate;
            }
            @Autowired
            public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
                this.jdbcTemplate = jdbcTemplate;
            }
            public Person getPersonById(int id) {
                String sqlQuery = "SELECT * FROM person WHERE id=?";
                return jdbcTemplate.queryForObject(sqlQuery, new PersonMapper(), id);
            }
        }

As shown above, queryForObject() relies on the RowMapper interface to map the returned ResultSet rows to a Person object:

    
        public class PersonMapper implements RowMapper<Person> {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                Person person = new Person();
                person.setId(rs.getInt("id"));
                person.setFirstName(rs.getString("first_name"));
                person.setLastName(rs.getString("last_name"));
                return person;
            }
        }

Lastly, let’s create an integration test for the getPersonById() method:

    
        @JdbcTest
        @Sql({ "schema.sql", "data.sql" })
        public class PersonDAOIT {
            @Autowired
            private JdbcTemplate jdbcTemplate;
            private PersonDAO personDAO;

            @BeforeEach
            public void init() {
                personDAO = new PersonDAO();
                personDAO.setJdbcTemplate(jdbcTemplate);
            }
            @Test
            void whenUsingValidPersonId_thenReturnPerson() {
                int validPersonId = 1;
                Person person = personDAO.getPersonById(validPersonId);
                assertEquals(validPersonId, person.getId());
            }
            @Test
            void whenUsingInvalidPersonId_thenThrowException() {
                int invalidPersonId = 10;
                Exception exception = assertThrows(EmptyResultDataAccessException.class, () -> {
                    personDAO.getPersonById(invalidPersonId);
                });
                String expectedMessage = "Incorrect result size: expected 1, actual 0";
                assertTrue(exception.getMessage()
                    .contains(expectedMessage));
            }
        }

As we can see in the second test case, EmptyResultDataAccessException is thrown when the getPersonById() fails to return a Person object with the specified id.

So, to use queryForObject() without worrying about EmptyResultDataAccessException, we need to make sure that the SQL query returns exactly one single record.

Fixing EmptyResultDataAccessException

The easiest solution to avoid EmptyResultDataAccessException is to use another method such as query().

query(), unlike queryForObject() is used to execute an SQL query that expects multiple rows. It does not throw EmptyResultDataAccessException when the expected result is empty.

So, let’s rewrite the getPersonById() method to use query() instead of queryForObject():

    
        public Person getPersonByIdV2(int id) {
            String sqlQuery = "SELECT * FROM person WHERE id=?";
            List<Person> result = jdbcTemplate.query(sqlQuery, new PersonMapper(), id);
            if (result.isEmpty()) {
                return null;
            }

            return result.get(0);
        }

As we can see, the query() method returns a list of objects. We add a test condition to return null if the returned list is empty. Otherwise, we return the first element.

Now, let’s add a test case to confirm that the new version of getPersonById() returns null and does not throw EmptyResultDataAccessException when there is no row to return:

    
        @Test
        void whenUsingInvalidPersonId_thenReturnNull() {
            int invalidPersonId = 10;
            Person person = personDAO.getPersonByIdV2(invalidPersonId);

            assertNull(person);
        }

Another solution would be capturing EmptyResultDataAccessException when using queryForObject() and then return null. However, capturing exceptions to return null is not always a good practice.

Conclusion

To sum it up, we covered in-depth the exception EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0.

We explained the main cause behind it, then we saw how to produce it using a practical example, and finally how to fix it.