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

User · Answer

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?

User · Answer

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

User · Answer

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 lt String gt  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

User · Answer

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 lt UserInfo gt  userInfoList   jdbcTemplate query sql  new Object     username  password                  new RowMapper lt UserInfo gt                        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

User · Answer

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 lt String gt  li   jdbcTemplate queryForList sql String class                   return li get 0  toString

User · Answer

Since getJdbcTemplate   queryForMap expects minimum size of one but when it returns null it shows EmptyResultDataAccesso fix dis when can use below logic  Map lt String  String gt  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

User · Answer

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 lt String gt               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 lt Account gt  MAPPER ACCOUNT            rs  i  - gt  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 lt Account gt  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 - gt  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  lt T gt  ResultSetExtractor singletonExtractor          RowMapper lt   extends T gt  mapper        return rs - gt  rs next     mapper mapRow rs  1    null      Creating a ResultSetExtractor now becomes trivial   private static final ResultSetExtractor lt Account gt  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  lt T gt  ResultSetExtractor lt Optional lt T gt  gt  singletonOptionalExtractor          RowMapper lt   extends T gt  mapper        return rs - gt  rs next     Optional of mapper mapRow rs  1     Optional empty        Which now when used could have the following   private static final ResultSetExtractor lt Optional lt Double gt  gt  EXTRACTOR DISCOUNT           singletonOptionalExtractor MAPPER DISCOUNT    public double getDiscount long accountId        return jt query SELECT DISCOUNT  EXTRACTOR DISCOUNT  accountId               orElse 0 0

User · Answer

You could use a group function so that your query always returns a result  ie  MIN ID NMB SRZ

User · Answer

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 lt String gt  certList    ArrayList lt String gt   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

User · Answer

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 lt String gt  certs   jdbc queryForList sql  String class        if  certs isEmpty              return null        else           return certs get 0

User · Answer

I dealt with this before  amp  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 lt Long gt              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

User · Answer

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 lt Myclass gt                          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

User · Answer

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 lt String gt  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

User · Answer

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 lt FooObject gt   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

User · Answer

to make       jdbcTemplate queryForList sql  String class    work  make sure your jdbcTemplate is of type       org springframework jdbc core JdbcTemplate

User · Answer

In Postgres  you can make almost any single value query return a value or null by wrapping it   SELECT  SELECT  lt query gt   AS value   and hence avoid complexity in the caller

User · Answer

You can do this way  String cert   DataAccessUtils singleResult      jdbcTemplate queryForList           quot 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 quot           new MapSqlParameterSource                addValue  quot id str rt quot    quot 999 quot                addValue  quot id nmb srz quot    quot 60230009999999 quot            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

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

Examples related to java

Examples related to spring

Examples related to jdbctemplate