Some doubts about RowMapper use in JDBC in a Spring Framework application

Question

I am studying how to execute query on a database using JDBC in Spring Framework.

I am following this tutorial: http://www.tutorialspoint.com/spring/spring_jdbc_example.htm

In this tutorial I define a StudentDAO interface which only define the CRUD method that I want.

Then is defined the Student class that is the entity that I want to persist on the Student database table.

Then is defined the StudentMapper class that is a specific implementation of RowMapper interface that, in this case, is used to map a specific record in the ResultSet (returned by a query) to a Student object.

Then I have the StudentJDBCTemplate that rappresent the implementation of my StudentDAO interface, in this class I implement the CRUD method that was defined in the interface.

Ok, and now I have a doubt about how the StudentMapper class work: in this StudentJDBCTemplate class there is defined the method that return the list of all record that are in the Student database table, this one:

   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, 
                                new StudentMapper());
      return students;
   }

How you can see, this method return a List of Student object and work in the following way:

the first thing that it do is to define the query that return all record in the Student database table in the SQL String.

Then this query is executed by the query method call on the jdbcTemplateObject object (that is an istance of JdbcTemplate Spring class**

This method take two parameter: the SQL String (that contains the SQL query that must be executed) and a new StudentMapper object that take the ResultSet object returned by the query and map it's record on a new Student object

Reading here: http://static.springsource.org/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html sayas that: Execute a query given static SQL, mapping each row to a Java object via a RowMapper.

My doubt is related to the fact that my StudentMapper map a ResultSet record on a Student object using the mapRow() method, this is the code:

package com.tutorialspoint;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
   }
}

So, who call this mapRow method? is it called automatically by the Spring Framework? (because in this example is never called manually...)

Tnx

Andrea

Then this query is executed by the query method call on the jdbcTemplateObject object (that is an istance of JdbcTemplate Spring class**

Solution 1

When you pass an instance of your RowMapper to the JdbcTemplate method

List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());

The JdbcTemplate depending on which method you called, will internally use the mapper with the result set it gets from the JDBC Connection to create an object of your requested type. For example, since you called JdbcTemplate#query(String, RowMapper), the method will use your String SQL to query the database and will loop through each "row" in the ResultSet kind of like this:

ResultSet rs = ... // execute query
List<Student> students = ...// some list
int rowNum = 0;
while(rs.next()) {
    Student student = rowMapper.mapRow(rs, rowNum);
    students.add(student);
    rowNum++;
}

return students;

So, Spring's JdbcTemplate method will use the RowMapper you provide and call its mapRow method to create the expected return object.

You might like to look at Martin Fowler's Data Mapper in conjunction with Table Data Gateway for an idea of how these things are distributed and provide low coupling.

Solution 2

Here is the typical pattern I use with BeanPropertyRowMapper. It saves a lot of coding. Your query needs to alias each column to match the property name in the class. In this case species_name as species and the other column names happen to match already.

public class Animal {
    String species;
    String phylum;
    String family;
    ...getters and setters omitted
}

@Repository
public class AnimalRepository {
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public List<Animal> getAnimalsByPhylum(String phylum) {
        String sql = " SELECT species_name as species, phylum, family FROM animals"
                 +" WHERE phylum = :phylum";

        Map<String, Object> namedParameters = new HashMap<String, Object>();
        namedParameters.put("phylum", phylum);
        SqlParameterSource params = new MapSqlParameterSource(namedParameters);
        List<Animal> records = namedParameterJdbcTemplate.query(sql,
                params, BeanPropertyRowMapper.newInstance(Animal.class));

        return records;
    }
}

An alternative is to use a RowMapper (this example just uses an anonymous class) when you need more customization per row:

    List<Animal> records = namedParameterJdbcTemplate.query(sql,
            params, new RowMapper<Animal>(){
        public Animal mapRow(ResultSet rs, int i) throws SQLException {
            Animal animal = new Animal();   
            animal.setSpecies(rs.getString("species_name"));
            if (some condition) {
                animal.setPhylum(rs.getString("phylum"));
            } else {
                animal.setPhylum(rs.getString("phylum")+someThing());
            }
            animal.setFamily(rs.getString("family"));

            return animal;
        }
    });

Solution 3

Using RowMapper in Spring

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

import org.springframework.jdbc.core.RowMapper;

public class RowsMap implements RowMapper<EmpPojo>{

    @Override
    public EmpPojo mapRow(ResultSet rs, int counts) throws SQLException {
        EmpPojo em=new EmpPojo();
        em.setEid(rs.getInt(1));
        em.setEname(rs.getString(2));
        em.setEsal(rs.getDouble(3));

        return em;
    }

}

Finally in Main class

List<EmpPojo> lm=jt.query("select * from emps", new RowsMap());
for(EmpPojo e:lm)
{
    System.out.println(e.getEid()+" "+e.getEname()+" "+e.getEsal());
}

Solution 4

So, who call this mapRow method? is it called automatically by the Spring Framework? (because in this example is never called manually...)

This is automatically called by spring framework. All You need is to specify

1. Connection parameters,
2. SQL statement
3. Declare parameters and provide parameter values
4. Do the work for each iteration.

View more solutions

Comments

[Ariel]

Keep in mind about BeanPropertyRowMapper: "Please note that this class is designed to provide convenience rather than high performance. For best performance, consider using a custom RowMapper implementation."