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?

Thunderbolt

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<String>() {
    public String 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 element
         // either return 1st element or throw an exception
}

Enroll

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. – Gillespie

Hi @Rakesh, why not only return null in catch(EmptyResultDataAccessException exception){ return null; } ? – Menology

@Rakesh This is not same for all methods like queryForXXX. In case of queryForList it returns a empty list. – Conto

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 :) – Etude

"but make sure the query you are using should return only one row". This is crazy. It's like dictating or forcing the user instead it should have been handled in framework. I see this as a gap in framework or it is not handled in framework well.. You cannot decide how my query should look like.. If I give a query to framework , it should handle basic stuff. That's what it is meant for after all.. – Greenbelt

Nice explanation, this definitely makes it clear what the issue is, The enforcement of 1 row return or your server throws exceptions is beyond stupid but that's just me. – Manyplies

Answer 2

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

Gillespie

Answer 3

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

Preferment

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. – Thunderbolt

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. – Gillespie

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. – Counterword

@Counterword comment on this PR github.com/spring-projects/spring-framework/pull/724 – Preferment

Answer 4

Ok, I figured it out. I just wrapped it in a try catch and send back null.

    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'";
            try {
                Object o = (String) jdbc.queryForObject(sql, String.class);
                cert = (String) o;
            } catch (EmptyResultDataAccessException e) {
                e.printStackTrace();
            }
            return cert;
    }

Thunderbolt

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. – Counterword

This comment is in 2020 and your answer is still valid.. Such a huge gap in framework.. – Greenbelt

Answer 5

Using Java 8 or above you can use an Optional and Java Streams.

So you can simply use the JdbcTemplate.queryForList() method, create a Stream and use Stream.findFirst() which will return the first value of the Stream or an empty Optional:

public Optional<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.queryForList(sql, String.class)
            .stream().findFirst();
}

To improve the performance of the query you can append LIMIT 1 to your query, so not more than 1 item is transferred from the database.

Isleana

Answer 6

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.

Cruciate

@Abdull UserMapper implements RowMapper<String>. – Gillespie

DataAccessUtils.singleResult(...) is what I was looking for. Thx – Weary

Answer 7

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?

Elater

It is, and it should be in Spring – Counterword

Answer 8

You can do this way:

String cert = DataAccessUtils.singleResult(
    jdbcTemplate.queryForList(
        "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN where ID_STR_RT = :id_str_rt and ID_NMB_SRZ = :id_nmb_srz",
        new MapSqlParameterSource()
            .addValue("id_str_rt", "999")
            .addValue("id_nmb_srz", "60230009999999"),
        String.class
    )
)

DataAccessUtils.singleResult + queryForList:

• returns null for empty result
• returns single result if exactly 1 row found
• throws exception if more then 1 rows found. Should not happen for primary key / unique index search

DataAccessUtils.singleResult

Okajima

Answer 9

Since getJdbcTemplate().queryForMap expects minimum size of one but when it returns null it shows EmptyResultDataAccesso fix dis when can use below logic

Map<String, String> loginMap =null;
try{
    loginMap = getJdbcTemplate().queryForMap(sql, new Object[] {CustomerLogInInfo.getCustLogInEmail()});
}
catch(EmptyResultDataAccessException ex){
    System.out.println("Exception.......");
    loginMap =null;
}
if(loginMap==null || loginMap.isEmpty()){
    return null;
}
else{
    return loginMap;
}

Septavalent

Answer 10

You could use a group function so that your query always returns a result. ie

MIN(ID_NMB_SRZ)

Scat

Answer 11

In Postgres, you can make almost any single value query return a value or null by wrapping it:

SELECT (SELECT <query>) AS value

and hence avoid complexity in the caller.

Disposed

Answer 12

We can use query instead of queryForObject, major difference between query and queryForObject is that query return list of Object(based on Row mapper return type) and that list can be empty if no data is received from database while queryForObject always expect only single object be fetched from db neither null nor multiple rows and in case if result is empty then queryForObject throws EmptyResultDataAccessException, I had written one code using query that will overcome the problem of EmptyResultDataAccessException in case of null result.

----------


public UserInfo getUserInfo(String username, String password) {
      String sql = "SELECT firstname, lastname,address,city FROM users WHERE id=? and pass=?";
      List<UserInfo> userInfoList = jdbcTemplate.query(sql, new Object[] { username, password },
              new RowMapper<UserInfo>() {
                  public UserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                      UserInfo user = new UserInfo();
                      user.setFirstName(rs.getString("firstname"));
                      user.setLastName(rs.getString("lastname"));
                      user.setAddress(rs.getString("address"));
                      user.setCity(rs.getString("city"));

                      return user;
                  }
              });

      if (userInfoList.isEmpty()) {
          return null;
      } else {
          return userInfoList.get(0);
      }
  }

Moye

Answer 13

I dealt with this before & had posted in the spring forums.

http://forum.spring.io/forum/spring-projects/data/123129-frustrated-with-emptyresultdataaccessexception

The advice we received was to use a type of SQlQuery. Here's an example of what we did when trying to get a value out of a DB that might not be there.

@Component
public class FindID extends MappingSqlQuery<Long> {

        @Autowired
        public void setDataSource(DataSource dataSource) {

                String sql = "Select id from address where id = ?";

                super.setDataSource(dataSource);

                super.declareParameter(new SqlParameter(Types.VARCHAR));

                super.setSql(sql);

                compile();
        }

        @Override
        protected Long mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getLong(1);
        }

In the DAO then we just call...

Long id = findID.findObject(id);

Not clear on performance, but it works and is neat.

Alleviation

Answer 14

For Byron, you can try this..

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> li = jdbcTemplate.queryForList(sql,String.class);
                return li.get(0).toString();
        }

Caliban

Answer 15

to make

    jdbcTemplate.queryForList(sql, String.class)

work, make sure your jdbcTemplate is of type

    org.springframework.jdbc.core.JdbcTemplate

Acalia

Answer 16

IMHO returning a null is a bad solution because now you have the problem of sending and interpreting it at the (likely) front end client. I had the same error and I solved it by simply returning a List<FooObject>. I used JDBCTemplate.query().

At the front end (Angular web client), I simply examine the list and if it is empty (of zero length), treat it as no records found.

Milreis

Answer 17

I got same exception while using

if(jdbcTemplate.queryForMap("select * from table").size() > 0 ) /* resulted exception */ 

when changed to list it resolved

if(jdbcTemplate.queryForList("select * from table").size() > 0) /* no exception */

Upheaval

jdbcTemplate.queryForMap() expects a single row. You'd have to add something like LIMIT 1 for your example to work. – Commensurate

Answer 18

I just catch this "EmptyResultDataAccessException"

public Myclass findOne(String id){
    try {
        Myclass m = this.jdbcTemplate.queryForObject(
                "SELECT * FROM tb_t WHERE id = ?",
                new Object[]{id},
                new RowMapper<Myclass>() {
                    public Myclass mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Myclass m = new Myclass();
                        m.setName(rs.getString("name"));
                        return m;
                    }
                });
        return m;
    } catch (EmptyResultDataAccessException e) { // result.size() == 0;
        return null;
    }
}

then you can check:

if(m == null){
    // insert operation.
}else{
    // update operation.
}

Wizard

We can use query instead of queryForObject – Moye

Usually considered bad practice to abuse exceptions like this. Exceptions are not for predictable program logic flow, they are for exceptional situations. – Specialism