Spring RowMapper Example

Spring RowMapper Example

To extract record from data base using JdbcTemplate and RowMapper interface is very handy where JdbcTemplate simplifies use of the JDBC and it also helps avoid common errors.

RowMapper interface: This interface used by JdbcTemplate for mapping rows of ResultSet on a per-row basis. Once you implements this interface it perform actual work of mapping each row data to result object and one need not to worry about exception handling this will be taken care by calling JdbcTemplate.

To test RowMapper example following below are needed:

• Eclipse ( We are using eclipse Kepler. You could also download eclipse from eclipse.org/downloads)
• MySQL data base (Install MySQL Community Server (GPL) version in your system : MySQL Community Server). We are using version 5.6 ( If you are not sure how to install it please use this link : Install MySQL server )
• Maven 3.0.4

Step 1. Create table to MySQL database: Please use below script to create table and insert sample data for test to the MySQL database:

DROP TABLE person
GO

CREATE TABLE person (
    id int NOT NULL AUTO_INCREMENT,
    First_Name  varchar(25) NULL,
    Last_Name   varchar(25) NULL,
    Street_Name varchar(25) NULL,
    City        varchar(25) NULL,
    State       varchar(25) NULL,
    Country     varchar(25) NULL,
    PRIMARY KEY (id) 
    )
GO

Step 2: Insert test data to the table SQL script:

truncate table person
Go

INSERT INTO person(id, First_Name, Last_Name, Street_Name, City, State, Country) 
    VALUES(2, 'Java', 'Honk', 'John st.', 'NY', 'NY', 'USA')

Go

INSERT INTO person(id, First_Name, Last_Name, Street_Name, City, State, Country) 
    VALUES(3, 'Java', 'Honk', 'John st.', 'NY', 'NY', 'USA')
GO

Step 3: Create dynamic web project in eclipse name: SpringRowMapper (Please use this link if you are not sure how to create maven project in eclipse: Create  maven project in eclipse)

Step 4: Enter all required dependencies to maven pom.xml file as below:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.springframework.samples.service.service</groupId>
  <artifactId>SpringMaven</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>  
    
    
    <dependencies>  
            
        <!-- Spring and Transactions -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.0.3.RELEASE</version>
        </dependency>
        
        <!-- MySql 5.5 Connector -->   
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.29</version>
        </dependency>
        
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.7.4</version>
        </dependency>
            
            
    </dependencies> 
</project>

Step 5: Create and configure application-config.xml file with all required value as below:

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

<!-- DataSource to connect mysql database -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/JavaHonk"></property>
    <property name="username" value="root"></property>
    <property name="password" value="admin"></property>
</bean>

<!-- JdbcTemplate bean to execute query-->
 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
 </bean>

<!-- Our main DAO class bean object which we are making transactional -->
 <bean id="personDAO" class="com.javahonk.dao.PersonDAO">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
 </bean>          

</beans>

Note: Don’t forget to replace data base URL, User id and password with your configuration.

Step 6: Create Person.java inside com.javahonk.di.bean package:

Note: Column name in the table and java object mapping property name should be same otherwise data may not populate properly.

For example: Spring checks both upper case and lower case property:

• Table column name id: Java mapping can be id or ID
• Table column name First_Name:  Java mapping can be firstname or FirstName or First_Name

package com.javahonk.di.bean;

import java.io.Serializable;

public class Person implements Serializable{

    private static final long serialVersionUID = 1L;
    private Integer id;
    private String First_Name;
    private String Last_Name;
    private String Street_Name;
    private String city;
    private String state;
    private String country;
    private Integer rowNumber;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getFirst_Name() {
        return First_Name;
    }
    public void setFirst_Name(String first_Name) {
        First_Name = first_Name;
    }
    public String getLast_Name() {
        return Last_Name;
    }
    public void setLast_Name(String last_Name) {
        Last_Name = last_Name;
    }
    public String getStreet_Name() {
        return Street_Name;
    }
    public void setStreet_Name(String street_Name) {
        Street_Name = street_Name;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    public String getCountry() {
        return country;
    }
    public void setCountry(String country) {
        this.country = country;
    }
    public Integer getRowNumber() {
        return rowNumber;
    }
    public void setRowNumber(Integer rowNumber) {
        this.rowNumber = rowNumber;
    }
    
    
    
}

Step 7: Create PersonRowMapper.java inside com.javahonk.di.bean package and we will use this class to map result set rows when using JdbcTemplate:

package com.javahonk.di.bean;

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

import org.springframework.jdbc.core.RowMapper;

public class PersonRowMapper implements RowMapper<Person>{

    @Override
    public Person mapRow(ResultSet rs, int rowRows) 
        throws SQLException {
    Person person = new Person();
    person.setRowNumber(rowRows);
    person.setId(rs.getInt("id"));
    person.setFirst_Name(rs.getString("First_Name"));
    person.setLast_Name(rs.getString("Last_Name"));
    person.setStreet_Name(rs.getString("Street_Name"));
    person.setCity(rs.getString("City"));
    person.setState(rs.getString("State"));
    person.setCountry(rs.getString("Country"));
    return person;
    }

}

Step 8: Create Interface IPersonDAO.java inside com.javahonk.dao package:

package com.javahonk.dao;

import java.util.List;

import com.javahonk.di.bean.Person;


public interface IPersonDAO {
    List<Person> selectAllPerson();
    List <Person> selectPersonByName(String personName);
    Integer findTotalPerson();
    Integer findTotalPersonUsingWhereClause();
    List<Person> selectPersonByNameUsingCustomRowMapper(
        String personName);
}

Step 9: Create PersonDAO.java class inside com.javahonk.dao package which implements IPersonDAO interface:

package com.javahonk.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.javahonk.di.bean.Person;
import com.javahonk.di.bean.PersonRowMapper;

public class PersonDAO implements IPersonDAO {

    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
    }

    //Select all person with using custom row mapper and directly map 
    //all field value when calling method
    public List<Person> selectAllPerson() {
    
    System.out.println("\nList of person in the table\n");
    String selectAllPerson = "SELECT * FROM person";
    
    return jdbcTemplate.query(selectAllPerson, 
        new RowMapper<Person>(){

        @Override
        public Person mapRow(ResultSet rs, int rowNumber) 
            throws SQLException {       
        Person person = new Person();
        person.setRowNumber(rowNumber);
        person.setId(rs.getInt("id"));
        person.setFirst_Name(rs.getString("First_Name"));
        person.setLast_Name(rs.getString("Last_Name"));
        person.setStreet_Name(rs.getString("Street_Name"));
        person.setCity(rs.getString("City"));
        person.setState(rs.getString("State"));
        person.setCountry(rs.getString("Country"));
        return person;
        }
        
    }); 

    }
    
    @Override
    //Using BeanPropertyRowMapper
    public List <Person> selectPersonByName(String personName) {

    System.out.println("\nList of person in the table\n");
    String selectPersonByName = "SELECT * FROM person "
        + "where First_Name = ?";
    List <Person> personList = (List<Person>) jdbcTemplate.
        query(selectPersonByName,
        new Object[] { personName },
        new BeanPropertyRowMapper<Person>(Person.class));

    return personList;
    }
    
    @Override
    //Using custom row mapper
    public List <Person> selectPersonByNameUsingCustomRowMapper(
        String personName) {

    System.out.println("\nList of person in the table\n");
    String selectPersonByName = "SELECT * FROM person "
        + "where First_Name = ?";
    List <Person> personList = (List<Person>) jdbcTemplate.
        query(selectPersonByName,
        new Object[] { personName },
        new PersonRowMapper());

    return personList;
    }
    
    @Override
    //Find total person
    public Integer findTotalPerson() {

    System.out.println("\nNo of person in the table\n");
    String totalNumber   = "SELECT count(*) FROM person";
    return jdbcTemplate.queryForInt(totalNumber);
    
    }
    
    @Override
    //find total person using clause
    public Integer findTotalPersonUsingWhereClause() {

    System.out.println("\nNo of person in the table\n");
    String totalNumber   = "SELECT count(*) FROM "
        + "person where First_Name = ?";
    return jdbcTemplate.queryForInt(totalNumber, 
        new Object[] {"Java"});
    
    }   
    

}

Step 10: Finally create main RowMapperTest.java class inside com.javahonk package to test RowMapper using jdbctemplate:

package com.javahonk;

import java.util.List;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.javahonk.dao.IPersonDAO;
import com.javahonk.di.bean.Person;

public class RowMapperTest {

    public static void main(String[] args) {
    
    ClassPathXmlApplicationContext applicationContext = 
        new ClassPathXmlApplicationContext(
            "spring\\application-config.xml");

    IPersonDAO personDAO = applicationContext.
        getBean("personDAO", IPersonDAO.class);
    
    //Using RowMapper to map rows run time
    List<Person> persons = personDAO.selectAllPerson();
    for (Person person : persons) {
        System.out.println("Id: "+person.getId()
            +" First Name: "+person.getFirst_Name()
            +" Last Name: "+person.getLast_Name()
            +" Street: "+person.getStreet_Name()
            +" City: "+person.getCity()
            +" State: "+person.getState()
            +" Country: "+person.getCountry()
            +" Row Number: "+person.getRowNumber());
    }
    
    //Using BeanPropertyRowMapper 
    persons = personDAO.selectPersonByName("Java");
    for (Person person : persons) {
        System.out.println("Id: "+person.getId()
        +" First Name: "+person.getFirst_Name()
        +" Last Name: "+person.getLast_Name()
        +" Street: "+person.getStreet_Name()
        +" City: "+person.getCity()
        +" State: "+person.getState()
        +" Country: "+person.getCountry());
    }
    
    //Using Custom Person RowMapper class
    List <Person> persons2 = personDAO.
        selectPersonByNameUsingCustomRowMapper("Java");
    for (Person person2 : persons2) {
        System.out.println("Id: "+person2.getId()
            +" First Name: "+person2.getFirst_Name()
            +" Last Name: "+person2.getLast_Name()
            +" Street: "+person2.getStreet_Name()
            +" City: "+person2.getCity()
            +" State: "+person2.getState()
            +" Country: "+person2.getCountry()
            +" Row Number: "+person2.getRowNumber());
        
    }
    
    //Find total person
    //Example 1
    Integer totalPerson = personDAO.findTotalPerson();
    System.out.println(totalPerson);
    
    //Find total person
    //Example 2
    totalPerson = personDAO.findTotalPersonUsingWhereClause();
    System.out.println(totalPerson);
    applicationContext.close();

    }

}

Step 11: Now we are all set to test our code. Right click RowMapperTest.java class –> Run As –> Java Application  and it will show you below output on console:

 Download Project: Spring RowMapper Example

That’s it spring RowMapper Example