Dinesh on Java

Implementing RowMapper

Spring provides a RowMapper interface for mapping a single row of a ResultSet to an object. It can be used for both single and multiple row queries. It is parameterized as of Spring 3.0.

public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum)
throws SQLException;
}

An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object, but don’t need to worry about exception handling. SQLExceptions will be caught and handled by the calling JdbcTemplate.

Typically used either for JdbcTemplate’s query methods or for out parameters of stored procedures. RowMapper objects are typically stateless and thus reusable; they are an ideal choice for implementing row-mapping logic in a single place.

Alternatively, consider subclassing MappingSqlQuery from the jdbc.object package: Instead of working with separate JdbcTemplate and RowMapper objects, you can build executable query objects (containing row-mapping logic) in that style.

Say for example, when we are selecting records from an employee table, we will iterate over the result set to get the individual values which won’t be ideal for situations, especially in Java where we want to map records from a database to individual Java objects. Also the question of re-usability comes into the picture as the above code doesn’t represent for getting itself re-used. Spring Row Mapper interfaces come into the rescue for such situations.

Querying for Single Row:

Here’s two ways to query or extract a single row record from database, and convert it into a model class(Employee).

1. Custom RowMapper:

In general, It’s always recommended to implement the RowMapper interface to create a custom RowMapper to suit your needs..

package com.dineshonjava.sdnext.jdbc.utils;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public class EmployeeMapper implements RowMapper {  
 public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {  
  Employee employee = new Employee();  
  employee.setEmpid(rs.getInt("empid"));  
  employee.setName(rs.getString("name"));  
  employee.setAge(rs.getInt("age"));  
  employee.setSalary(rs.getLong("salary"));  
  return employee;  
 }  
}  

Pass it to queryForObject() method, the returned result will call your custom mapRow() method to match the value into the property.

public Employee getEmployee(Integer empid) {
   String SQL = "SELECT * FROM Employee WHERE empid = ?";
   Employee employee = (Employee) jdbcTemplateObject.queryForObject(SQL, new Object[]{empid}, new EmployeeMapper());
    return employee;
}

2. BeanPropertyRowMapper:

In Spring 2.5, comes with a handy RowMapper implementation called ‘BeanPropertyRowMapper’, which can maps a row’s column value to a property by matching their names. Just make sure both the property and column has the same name, e.g property ‘empid’ will match to column name ‘EMPID’ or with underscores ‘EMP_ID’.

public Employee getEmployee(Integer empid) {
   String SQL = "SELECT * FROM Employee WHERE empid = ?";
   Employee employee = (Employee) jdbcTemplateObject.queryForObject(SQL, new Object[]{empid}, new BeanPropertyRowMapper(Employee.class));
    return employee;
}

Querying for Multiple Rows:

Now, query or extract multiple rows from database, and convert it into a List.

1. Map it manually:

In mutiple return rows, RowMapper is not supported in queryForList() method, you need to map it manually.

public List<Employee> findAll(){
  String sql = "SELECT * FROM Employee";
 
 List employees= new ArrayList();
 
 List rows = getJdbcTemplate().queryForList(sql);
 for (Map row : rows) {
  Employee employee = new Employee();
  employee.setEmpid((Integer)(row.get("EMPID")));
  employee.setName((String)row.get("NAME"));
  employee.setAge((Integer)row.get("AGE"));
                employee.setSalary((Long)row.get("SALARY"));
  employees.add(employee);
 }
  return employees;
}

2. BeanPropertyRowMapper:

The simplest solution is using the BeanPropertyRowMapper class.

public List findAll(){
  String sql = "SELECT * FROM Employee";
  List employees= getJdbcTemplate().query(sql,
   new BeanPropertyRowMapper(Employee.class));
  return employees;
}