Over a million developers have joined DZone.

Spring JdbcTemplate: RowMapper vs. ResultSetExtractor

In this comparison, see when RowMapper and ResultSetExtractor work better when you're diving into the JdbcTemplate class.

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

This article comes as a collaboration from the Dreamix team!

The JdbcTemplate class provided by Spring Framework is an abstraction wrapper around JDBC. It does several things out of the box as described in the Spring Framework Reference:

  1. Opens connection.

  2. Prepares and executes statements.

  3. Set up the loop to iterate through the results.

  4. Process any exception.

  5. Handle transactions.

  6. Close the connection, statement, and resultset.

Spring Framework also provides classes to extract the data from the result set. There are several built-in classes that directly map the result set to a list of objects, for example, BeanPropertyRowMapper. However, sometimes we need to implement custom logic that extracts the data. In order to do so, we need to implement custom mapper or extractor. Below, I am going to show you two examples of mapping the same database table to different Java objects, which will clarify when we should implement RowMapper and when ResultSetExtractor. Let’s say that we have the following employees table in the database:

country

employee

Bulgaria

Veselin

America

John

Bulgaria

Angel

German

Helga

Implementing Custom RowMapper

If we have a simple select query like this one:

SELECT country, employee FROM employees;

And we want to map it to objects of the following type:

public class Employee {
    Private String country;
    Private String employeeName;
}


It is better to implement RowMapper than ResultSetExtractor because a single row returned from the table has to be mapped to a single java object. Here is an example of a custom RowMapper:

private static final class EmployeeMapper implements RowMapper<Employee> {

    @Override
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee = new Employee();

        employee.setCountry(rs.getString("country"));
        employee.setEmployeeName(rs.getString("employee"));

        return employee;
    }
}


As you can see we implement the RowMapper interface and we override the method mapRow. We create an empty Employee object and then we fill it with the retrieved country and employee name. The custom mapper can be used like this:

List<Employee> employees = jdbcTemplate.query("SELECT country, employee FROM employees", 
                                              new EmployeeMapper());


Implementing Custom ResultSetExtractor

Now let’s say that we want to import the result of the query into a single Map object where the key is the country and the value is a list of employees for the given country. We can’t do that directly with the RowMapper because there we have access only to a single row from the result set. To achieve this we must write a ResultSetExtractor. Here is an example:

private static final class EmployeeMapExtractor implements ResultSetExtractor<Map<String, List<String>>> {

@Override
public Map<String, List<String>> extractData(ResultSet rs) throws SQLException {
Map<String, List<String>> employeesMap = new HashMap<>();

while (rs.next()) {
String country = rs.getString("country");
String employeeName = rs.getString("employee");

List<String> employees = employeesMap.get(country);

            if (employees == null) {
List<String> newEmployees = new ArrayList<>();

              newEmployees.add(employeeName);
employeesMap.put(country, newEmployees);
            } else {
 employees.add(employeeName);
            }
        }
        return employeesMap;
    }
}


The EmployeeMapExtractor implements a while loop in which we iterate over the result set and we fill the map. Here is an example how we can use it:

Map<String, List<String>> employeesMap = jdbcTemplate.query("SELECT country, employee FROM employees", 
                                                            new EmployeeMapExtractor());


To wrap it up, the difference between RowMapper and ResultSetExtrator is that with the mapper we have access to a returned row while with the extractor we can use the whole result set. So if we need to map several rows returned from the query to a single object we should use extractor, but in the other cases, a mapper should be sufficient.

Do you know other scenarios where to use an extractor is more appropriate than a mapper?

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
java ,spring ,jdbc ,rowmapper ,resultsetextractor

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}