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

Question

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.

Answer 1

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.    
}