Implementing RowMapper in Spring with Example

In this tutorial, we implement a custom RowMapper class to map our domain objects. We then use this class to write fetch methods that return custom model objects.

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.
Popular Tutorials
  1. Spring Tutorial
  2. Spring MVC Web Tutorial
  3. Spring Boot Tutorial
  4. Spring JDBC Tutorial
  5. Spring AOP Tutorial
  6. Spring Security Tutorial

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;
}




2 comments:

  1. Hi ,
    This is good tutorial.. I was looking for something that would allow me to map an entire list to the list of model objects without needing to hardcode the fields [ rs.getString() ]
    But tell me something , if the data returned has 5 columns and my model object has only two that match would this throw an error or only that the values would not be set .

    ReplyDelete
    Replies
    1. Hi Roshan,

      Thanks for nice comment.
      You can use mapping for this scenario to avoid hard coded setting parameters.

      If you map one model object with table columns then model should have same number of parameters with number of columns of mapped table other wise its through MappingException.

      Thanks,
      Dinesh

      Delete