RowMapper/ResultsetExtractor in spring

Question

I am working on spring framework, below is the SQL query to execute and get the result.

 String sql = "SELECT DESCRIPTION FROM ATWData WHERE SNO = '980098' ";

In spring framework we have RowMapper and ResultsetExtractor call back interfaces, which one is best to use to execute the above mentioned SQL query and get the single record. Below code gets the result using RowMapper and ResultSetExtractor.

code using ResultSetExtractor:

  try
        {
            description = jdbcTemplate.query(sql,

            new ResultSetExtractor<String>()
            {
                public String extractData(final ResultSet rs) throws SQLException
                {
                    if (rs.next())
                    {
                        String description = null;
                        description = rs.getString("DESCRIPTION");
                        return description;
                    }
                    return null;
                }
            });
        }
        catch (final DataAccessException e)
        {
            LOG.error("Error " + e);
        }

below code uses RowMapper to get the result:

try
     {
       description   = jdbcTemplate2.query(sql,

         new RowMapper<String>()
         {
             public String mapRow(final ResultSet rs, final int rowNum) throws SQLException
             {
                 String description = (String)rs.getString("DESCRIPTION"));
                 return description;
             }
         });
     }
     catch (final DataAccessException e)
     {
         LOG.error("Error " + e);
    }

I have read the documentation about those methods but confused to choose one in my existing scenario. Please suggest which code(RowMapper/ResultSetExtractor) best suits to execute the simple SQL query given above.

I doubt declaring `extractData()` to return `LocalDate` will allow code to compile. — Andreas, Sep 25 '16 at 20:48

score 2 · Answer 1 · answered Sep 25 '16 at 20:26

In your case, either is fine. RowMappers are designed to convert a row in the result set into a model in your application. A ResultSetExtractor is slightly more generic and allows you to operate on the ResultSet in its entirety rather than just over a single row. It simply depends on what you need to do with the results. In the case of extracting a single string, they both work. Also see this question for more information. Your question may be marked as a duplicate.

score 2 · Answer 2 · answered Sep 25 '16 at 21:02

Don't use either.

But first, and more importantly:

Don't build a SQL statement using string concatenation, unless you want to leave yourself open to SQL Injection attacks, allowing hackers to steal your data and/or corrupt or delete your database.
Don't catch the DataAccessException and continue execution as if everything is fine. The caller needs to be made aware that query failed, and will most likely want to rollback any pending transaction.

Since your query is a single row/single column query with parameter, use queryForObject(String sql, Class<T> requiredType, Object... args).

This is what you should do:

String sno = "980098";
String sql = "SELECT DESCRIPTION FROM ATWData WHERE SNO = ?";
String description = jdbcTemplate2.queryForObject(sql, String.class, sno);

It will throw EmptyResultDataAccessException if no row is found. If a missing row is not considered an error, catch the exception and return null (or Optional.empty()).

RowMapper/ResultsetExtractor in spring

2 Answers2