How to solve EmptyResultDataAccessException?

Question

@Spring: I wrote a Dao which find a id by another id. when it get the data its fine but when not found shows an exception such like this.

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

Here is the dao:

public Integer getIdByMerchantId(Integer merchantId) {
        String query = "SELECT id FROM transaction_history WHERE merchant_id=? ";
        try {
            return serviceJdbcTemplate.queryForObject(query, new Object[]{merchantId}, Integer.class);

        } catch (EmptyResultDataAccessException e) {
            log.error("Following query execution failed: ");
            log.error(Utils.getLoggerFriendlyQuery(query), merchantId);
            log.error("{} failed for merchant id {}. Error: {}", query, merchantId, e.getLocalizedMessage());
            return null;
        }
    }

pls check this: https://www.mkyong.com/spring/queryforobject-throws-emptyresultdataaccessexception-when-record-not-found/ — Ajit Soman, May 21 '17 at 12:59

score 6 · Accepted Answer · edited May 23 '17 at 11:54

6

I don't see any problem with your code. You are asking JdbcTemplate to find a single row for you, however, the merchantId you are querying does not exist probably on the database. The queryForObject() requires only one row to be returned. Best practice would be to append 'LIMIT 1' to your query, so that not more than one row is returned.

Please read this documentation reference. Notice the Throws section there, where IncorrectResultSizeDataAccessException is thrown in case the resultset contains 0 or more than 1 rows.

Additionally, read this post here.

edited May 23 '17 at 11:54

Community

1
1

answered May 22 '17 at 03:36

sbsatter

591
3
22

Thanks for your help.... Now i got it. i thought, it might be a problem when showed error on logger. – Sayed Mahmud Raihan May 22 '17 at 06:01

How to solve EmptyResultDataAccessException?

Here is the dao:

1 Answers1