Jdbctemplate query: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

Question

I am using Jdbctemplate to retrieve a List of 'Spittles' from the db. Here is my method.

private static final String SQL_SELECT_SPITTLE =
                    "select id, spitter_id, spittleText, postedTime from spittle";

public List<Spittle> getRecentSpittles() {
                return jdbcTemplate.query(SQL_SELECT_SPITTLE, new RowMapper<Spittle>() {

    public Spittle mapRow(ResultSet resultSet, int i) throws SQLException {
                        Spittle spittle = new Spittle();
                        spittle.setId(resultSet.getLong(1));
                        spittle.setSpitter(getSpitterById(resultSet.getLong(2)));
                        spittle.setText(resultSet.getString(3));
                        spittle.setWhen(resultSet.getDate(4));
                        return spittle;
                    }
                });
            }

Here is my table from database

  id  spitter_id spittleText           postedTime
  1       0        Hello               2015-08-24
  2       4        I've been in London!2015-08-25
  3       3        I'm glad            2015-08-28

Here is my stack trace:

INFO: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@1b8d17c: startup date [Sun Aug 30 22:36:35 CEST 2015]; root of context hierarchy
sie 30, 2015 10:36:36 PM org.springframework.beans.factory.support.DefaultListableBeanFactory registerBeanDefinition
INFO: Overriding bean definition for bean 'jdbcTemplateSpitterDao' with a different definition: replacing [Generic bean: class [com.gnology.springdataaccess.implementations.JdbcTemplateSpitterDao]; scope=singleton; abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null; defined in file [C:\Users\Damian\jdbc-project\target\classes\com\gnology\springdataaccess\implementations\JdbcTemplateSpitterDao.class]] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=appConfiguration; factoryMethodName=jdbcTemplateSpitterDao; initMethodName=null; destroyMethodName=(inferred); defined in com.gnology.springdataaccess.AppConfiguration]
sie 30, 2015 10:36:36 PM org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor <init>
INFO: JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
Exception in thread "main" org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
    at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:71)
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:797)
    at com.gnology.springdataaccess.implementations.JdbcTemplateSpitterDao.getSpitterById(JdbcTemplateSpitterDao.java:74)
    at com.gnology.springdataaccess.implementations.JdbcTemplateSpitterDao$2.mapRow(JdbcTemplateSpitterDao.java:94)
    at com.gnology.springdataaccess.implementations.JdbcTemplateSpitterDao$2.mapRow(JdbcTemplateSpitterDao.java:90)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:459)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:480)
    at com.gnology.springdataaccess.implementations.JdbcTemplateSpitterDao.getRecentSpittles(JdbcTemplateSpitterDao.java:90)
    at com.gnology.springdataaccess.implementations.SpitterServiceImpl.getRecentSpittles(SpitterServiceImpl.java:32)
    at com.gnology.springdataaccess.Main.main(Main.java:19)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

Yesterday I haven't got any problems with that, I don't know what to do with this error. How can I fix this? Thanks in advance.

@Andreas I inserted new records into the table without any problems. But after that i've got still the same error when i retrieve a list from the database. — midryuk, Aug 30 '15 at 20:32
Are you *sure* the above code is throwing the error? Can you provide a stack trace? — schtever, Aug 30 '15 at 20:37
As the stack trace shows, the exception is thrown by JdbcTemplateSpitterDao.getSpitterById(), that is called inside the mapRow() method. It should return exactly one row, but returns 0, hence the exception. Just read the message and the stack trace. It's all in there. There is no spitter with ID 0, 3 or 4. You should seriously consider using a join in your query and select everything at once, instead of executing one additional query for each spittle in the resultset. You should also add a foreign key constraint to ensure that such a data incoherence does never happen. — JB Nizet, Aug 30 '15 at 21:00
seems your query not returned any rows, this https://stackoverflow.com/questions/10606229/jdbctemplate-query-for-string-emptyresultdataaccessexception-incorrect-result to understand more. — Hari, May 30 '17 at 10:42

Jdbctemplate query: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

0 Answers0