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

Related searches

I am using Jdbctemplate to retrieve a single String value from the db. Here is my method.

    public String test() {
        String cert=null;
        String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN 
             where id_str_rt = '999' and ID_NMB_SRZ = '60230009999999'";
        cert = (String) jdbc.queryForObject(sql, String.class); 
        return cert;
    }

In my scenario it is complete possible to NOT get a hit on my query so my question is how do I get around the following error message.

EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

It would seem to me that I should just get back a null instead of throwing an exception. How can I fix this? Thanks in advance.

In JdbcTemplate , queryForInt, queryForLong, queryForObject all such methods expects that executed query will return one and only one row. If you get no rows or more than one row that will result in IncorrectResultSizeDataAccessException . Now the correct way is not to catch this exception or EmptyResultDataAccessException, but make sure the query you are using should return only one row. If at all it is not possible then use query method instead.

List<String> strLst  = getJdbcTemplate().query(sql,new RowMapper {

  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        return rs.getString(1);
  }

});

if ( strLst.isEmpty() ){
  return null;
}else if ( strLst.size() == 1 ) { // list contains exactly 1 element
  return strLst.get(0);
}else{  // list contains more than 1 elements
  //your wish, you can either throw the exception or return 1st element.    
}

EmptyResultDataAccessException: Incorrect result size: expected 1 , When using jdbcTemplate的queryForObject() method in spring, the appears if the query result is empty: org.springframework.dao. EmptyResultDataAccessException: Incorrect result size: expected 1, result size : expected 1, actual 0 org.springframework.dao.support. getPassword()},String. class); "EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0" is a common error when working with Spring JdbcTemplate queryForObject() operation. In

You may also use a ResultSetExtractor instead of a RowMapper. Both are just as easy as one another, the only difference is you call ResultSet.next().

public String test() {
    String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN "
                 + " where id_str_rt = '999' and ID_NMB_SRZ = '60230009999999'";
    return jdbc.query(sql, new ResultSetExtractor<String>() {
        @Override
        public String extractData(ResultSet rs) throws SQLException,
                                                       DataAccessException {
            return rs.next() ? rs.getString("ID_NMB_SRZ") : null;
        }
    });
}

The ResultSetExtractor has the added benefit that you can handle all cases where there are more than one row or no rows returned.

UPDATE: Several years on and I have a few tricks to share. JdbcTemplate works superbly with java 8 lambdas which the following examples are designed for but you can quite easily use a static class to achieve the same.

While the question is about simple types, these examples serve as a guide for the common case of extracting domain objects.

First off. Let's suppose that you have an account object with two properties for simplicity Account(Long id, String name). You would likely wish to have a RowMapper for this domain object.

private static final RowMapper<Account> MAPPER_ACCOUNT =
        (rs, i) -> new Account(rs.getLong("ID"),
                               rs.getString("NAME"));

You may now use this mapper directly within a method to map Account domain objects from a query (jt is a JdbcTemplate instance).

public List<Account> getAccounts() {
    return jt.query(SELECT_ACCOUNT, MAPPER_ACCOUNT);
}

Great, but now we want our original problem and we use my original solution reusing the RowMapper to perform the mapping for us.

public Account getAccount(long id) {
    return jt.query(
            SELECT_ACCOUNT,
            rs -> rs.next() ? MAPPER_ACCOUNT.mapRow(rs, 1) : null,
            id);
}

Great, but this is a pattern you may and will wish to repeat. So you can create a generic factory method to create a new ResultSetExtractor for the task.

public static <T> ResultSetExtractor singletonExtractor(
        RowMapper<? extends T> mapper) {
    return rs -> rs.next() ? mapper.mapRow(rs, 1) : null;
}

Creating a ResultSetExtractor now becomes trivial.

private static final ResultSetExtractor<Account> EXTRACTOR_ACCOUNT =
        singletonExtractor(MAPPER_ACCOUNT);

public Account getAccount(long id) {
    return jt.query(SELECT_ACCOUNT, EXTRACTOR_ACCOUNT, id);
}

I hope this helps to show that you can now quite easily combine parts in a powerful way to make your domain simpler.

UPDATE 2: Combine with an Optional for optional values instead of null.

public static <T> ResultSetExtractor<Optional<T>> singletonOptionalExtractor(
        RowMapper<? extends T> mapper) {
    return rs -> rs.next() ? Optional.of(mapper.mapRow(rs, 1)) : Optional.empty();
}

Which now when used could have the following:

private static final ResultSetExtractor<Optional<Double>> EXTRACTOR_DISCOUNT =
        singletonOptionalExtractor(MAPPER_DISCOUNT);

public double getDiscount(long accountId) {
    return jt.query(SELECT_DISCOUNT, EXTRACTOR_DISCOUNT, accountId)
            .orElse(0.0);
}

queryForObject() throws EmptyResultDataAccessException when , Reviewing a legacy project, and found this Spring JDBC code snippets : implements JdbcOperations { // public <T> T queryForObject(String sql, Object[] rowMapper) throws DataAccessException { List<T> results = query(sql, args, new if (size == 0) { throw new EmptyResultDataAccessException(1); }� When the result of the query is empty, size is assigned to 0 and throws a EmptyResultDataAccessException exception, this is the reason. My guess is that this design of Spring may be in order to prevent the user does not judge the null value. At the same time, when the the size of results is greater than 1, the exception will be also thrown to ensure the return is a single Object. Solution: catch the exception on this method

That's not a good solution because you're relying on exceptions for control flow. In your solution it's normal to get exceptions, it's normal to have them in the log.

public String test() {
    String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN where id_str_rt = '999' and ID_NMB_SRZ = '60230009999999'";
    List<String> certs = jdbc.queryForList(sql, String.class); 
    if (certs.isEmpty()) {
        return null;
    } else {
        return certs.get(0);
    }
}

JdbcTemplate queryForObject Incorrect result size: expected 1 , java Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0? List<String> strLst = getJdbcTemplate().query( sql� Just to clarify more, by my recent experience -> it clearly mentions: "EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0" which means it is expecting only 1 row, as a result of your SQL query execution, not 0 and not more than 1. So, make sure execution of your SQL query results in only one row.

Actually, You can play with JdbcTemplate and customize your own method as you prefer. My suggestion is to make something like this:

public String test() {
    String cert = null;
    String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN
        where id_str_rt = '999' and ID_NMB_SRZ = '60230009999999'";
    ArrayList<String> certList = (ArrayList<String>) jdbc.query(
        sql, new RowMapperResultSetExtractor(new UserMapper()));
    cert =  DataAccessUtils.singleResult(certList);

    return cert;
}

It works as the original jdbc.queryForObject, but without throw new EmptyResultDataAccessException when size == 0.

JdbcTemplate queryForObject Incorrect result size: expected 1, actual 0, public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object args) throws wedge Use spring jdbcTemplate query given data package list Reference EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 1� As per Spring's javadocs the exception is thrown when it expects the SQL to return 1 row but it got 0 rows. It says so in the stack trace that you posted Incorrect result size: expected 1, actual 0 is ot returning any records.

Since returning a null when there is no data is something I want to do often when using queryForObject I have found it useful to extend JdbcTemplate and add a queryForNullableObject method similar to below.

public class JdbcTemplateExtended extends JdbcTemplate {

    public JdbcTemplateExtended(DataSource datasource){
        super(datasource);
    }

    public <T> T queryForNullableObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
        List<T> results = query(sql, rowMapper);

        if (results == null || results.isEmpty()) {
            return null;
        }
        else if (results.size() > 1) {
            throw new IncorrectResultSizeDataAccessException(1, results.size());
        }
        else{
            return results.iterator().next();
        }
    }

    public <T> T queryForNullableObject(String sql, Class<T> requiredType) throws DataAccessException {
        return queryForObject(sql, getSingleColumnRowMapper(requiredType));
    }

}

You can now use this in your code the same way you used queryForObject

String result = queryForNullableObject(queryString, String.class);

I would be interested to know if anyone else thinks this is a good idea?

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)));

which may lead you to think that if the result set is empty it will return 0, however it throws an exception: org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0. so the the following implementation is essentially equivalent to the current one:

EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 Parece-me que eu deveria apenas voltar um nulo em vez de lançar uma exceção. Como posso consertar isso? Desde já, obrigado.

Comments
  • As mentioned below, the only drawback here is that if the return type was a complex type you would be building multiple objects and instantiating a list, also ResultSet.next() would be called unnecessarily. Using a ResultSetExtractor is a much more efficient tool in this case.
  • There's a missing parentheses in anonymous class definition - new RowMapper ()
  • I'm with Brett on this. ResultSetExtractor is cleaner :)
  • Hi @Rakesh, why not only return null in catch(EmptyResultDataAccessException exception){ return null; } ?
  • Hey! Can I just ask why 'Now the correct way is not to catch this exception', considering if you're using queryForObject? What would be wrong with catching an exception in the case of queryForObject? Thanks :)
  • my solution might not be the most elegant but at least mine works. You give an example of queryForObjectList which is not even an option with Jdbctemplate.
  • Only drawback here is that if the return type was a complex type you would be building multiple objects and instantiating a list, also ResultSet.next() would be called unnecessarily. Using a ResultSetExtractor is a much more efficient tool in this case.
  • and what if having no value is an option, but not having more than one ? I have this pattern often, and I would like to have a queryForOptionalObject in Spring for this purpose.
  • @Abdull UserMapper implements RowMapper<String>.
  • I think it is the best answer here as it gives the shortest syntax
  • DataAccessUtils.singleResult(...) is what I was looking for. Thx
  • It is, and it should be in Spring
  • I don't see why this is so bad and why you received so much downvote for it, appart for being a fundamentalist on the 'no program flow within exception' principle. I would have just replace the printstack trace with a comment explaining the case and do nothing else.
  • We can use query instead of queryForObject
  • Usually considered bad practice to abuse exceptions like this. Exceptions are not for predictable program logic flow, they are for exceptional situations.