Spring JdbcTemplate: RowMapper vs. ResultSetExtractor

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 a 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:

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 of 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?