Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
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. }
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);}
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); }}