Spring - Difference Between RowMapper and ResultSetExtractor

In Spring's JDBC module, both RowMapper and ResultSetExtractor are used to map data from a ResultSet to domain objects. However, they serve different use cases and offer distinct levels of control over the mapping process. Let's delve into their differences:

1. RowMapper:

Definition: RowMapper is an interface used to map a row from a ResultSet to an object, typically a domain model or DTO (Data Transfer Object).

Granularity: It maps a single row of data at a time.
Usage: It's especially useful when you expect multiple rows in the result and want to map each row to an object.
Method: The primary method you need to implement is:
```
T mapRow(ResultSet rs, int rowNum) throws SQLException;
```
Here, T is the type of object you're mapping to, and rowNum gives the current row number.
Common Scenario: Retrieving a list of domain objects from the database.

Example:

List<User> users = jdbcTemplate.query(
    "SELECT id, name, age FROM users",
    new RowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setAge(rs.getInt("age"));
            return user;
        }
    });

2. ResultSetExtractor:

Definition: ResultSetExtractor is an interface used to extract values from a ResultSet. It gives you more control over the ResultSet object than RowMapper.

Granularity: It provides control over the entire ResultSet, allowing you to iterate and map multiple rows if needed.
Usage: Useful when you have more complex use cases, like one-to-many relationships or when a query returns multiple result sets.

Method: The primary method to implement is:

T extractData(ResultSet rs) throws SQLException, DataAccessException;

Common Scenario: Handling more complex database extraction tasks, like aggregations or joining results from multiple tables.

Example:

User user = jdbcTemplate.query(
    "SELECT id, name, age FROM users WHERE id = ?",
    new ResultSetExtractor<User>() {
        public User extractData(ResultSet rs) throws SQLException {
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                return user;
            }
            return null;
        }
    }, userId);

Comparison:

Level of Control: ResultSetExtractor provides more control over the ResultSet since you handle the iteration yourself. With RowMapper, the iteration is done for you, and you simply map individual rows.
Complexity: RowMapper is simpler for most common use cases and is often the go-to choice for basic operations. ResultSetExtractor is more flexible but requires more manual handling.
Use Cases: If you need to map each row to an object and return a list of these objects, RowMapper is the best fit. For more complex scenarios, especially where you might want to map one-to-many relationships or deal with aggregated data, ResultSetExtractor is the better choice.

In summary, while both RowMapper and ResultSetExtractor allow you to work with ResultSet data in Spring JDBC, the choice between them boils down to the granularity of control you need over the data mapping process.

Spring RowMapper vs ResultSetExtractor:

RowMapper and ResultSetExtractor are both interfaces in Spring JDBC used for processing query results. RowMapper maps each row to a domain object, while ResultSetExtractor processes the entire result set.

// RowMapper example
public class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        // Mapping other fields...
        return user;
    }
}

// ResultSetExtractor example
public class UserResultSetExtractor implements ResultSetExtractor<List<User>> {
    @Override
    public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
            // Mapping other fields...
            users.add(user);
        }
        return users;
    }
}

Differences in usage of RowMapper and ResultSetExtractor in Spring JDBC:

RowMapper is used to map a single row to a domain object, while ResultSetExtractor is used for more complex scenarios where the entire result set needs to be processed.

// RowMapper usage
User user = jdbcTemplate.queryForObject("SELECT * FROM users WHERE id = ?", new UserRowMapper(), userId);

// ResultSetExtractor usage
List<User> users = jdbcTemplate.query("SELECT * FROM users", new UserResultSetExtractor());

When to use RowMapper or ResultSetExtractor in Spring framework:

Use RowMapper when mapping each row to a single domain object. Use ResultSetExtractor for more customized result set processing or when mapping to a collection of objects.

// RowMapper usage
User user = jdbcTemplate.queryForObject("SELECT * FROM users WHERE id = ?", new UserRowMapper(), userId);

// ResultSetExtractor usage
List<User> users = jdbcTemplate.query("SELECT * FROM users", new UserResultSetExtractor());

Comparing RowMapper and ResultSetExtractor for result set processing in Spring:

RowMapper focuses on mapping each row to a specific domain object, providing a clean and straightforward mapping. ResultSetExtractor is more flexible, allowing for custom processing of the entire result set.

// RowMapper example
public class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        // Mapping other fields...
        return user;
    }
}

// ResultSetExtractor example
public class UserResultSetExtractor implements ResultSetExtractor<List<User>> {
    @Override
    public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
            // Mapping other fields...
            users.add(user);
        }
        return users;
    }
}

Advantages of using RowMapper over ResultSetExtractor in Spring:
- RowMapper is advantageous when dealing with simple row-to-object mappings. It is concise, focused, and suitable for scenarios where each row corresponds to a single domain object.
```
// RowMapper usage
User user = jdbcTemplate.queryForObject("SELECT * FROM users WHERE id = ?", new UserRowMapper(), userId);
```
Scenarios where ResultSetExtractor is preferable to RowMapper in Spring:
- Use ResultSetExtractor when dealing with more complex scenarios, such as aggregating data from multiple rows or performing custom result set processing beyond simple row-to-object mapping.
```
// ResultSetExtractor usage
List<User> users = jdbcTemplate.query("SELECT * FROM users", new UserResultSetExtractor());
```

Implementing custom result set extraction with ResultSetExtractor in Spring:

ResultSetExtractor allows for custom result set processing. For example, aggregating data or performing specific actions on the entire result set.

// ResultSetExtractor example
public class CustomResultSetExtractor implements ResultSetExtractor<Object> {
    @Override
    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
        // Custom result set processing...
        return result;
    }
}

How RowMapper and ResultSetExtractor contribute to data access in Spring:
- Both RowMapper and ResultSetExtractor play crucial roles in Spring data access. They provide flexibility in mapping query results to Java objects and allow for customized processing of result sets, contributing to the versatility of data access in Spring.
```
// RowMapper usage
User user = jdbcTemplate.queryForObject("SELECT * FROM users WHERE id = ?", new UserRowMapper(), userId);
```
```
// ResultSetExtractor usage
List<User> users = jdbcTemplate.query("SELECT * FROM users", new UserResultSetExtractor());
```