Wednesday, June 1, 2011

Spring RowMapper Example, Use of RowMapper, RowMapper Tutorial, jdbcTemplate Example with RowMapper

Interface RowMapper:

org.springframework.jdbc.core.RowMapper
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. One very useful thing is that you can collect all the column of one recrod into java collection.

public class Student {
  private Map data = new HashMap();
  int roll;
}


Means, all the data will be there in map and only one primary column be there outside.

Example here:

Student.java

package binod.suman.rowmapper.domain;
import java.util.HashMap;
import java.util.Map;
public class Student {
 private Map data = new HashMap();

 int roll;

 public void putObject(String key, Object value) {
  data.put(key, value);
 }

 public Object getObject(String key) {
  return data.get(key);
 }

 public Student(int roll) {
  super();
  this.roll = roll;
 }

 public int getRoll() {
  return roll;
 }
 public void setRoll(int roll) {
  this.roll = roll;
 }
 @Override
 public String toString() {
  return "Name : "+data.get("sname")+" \nCity : "+data.get("city")+" \nRoll Number : "+roll;
 }


}

StudentResultSetReader.java

package binod.suman.rowmapper.dao;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import binod.suman.rowmapper.domain.Student;
public class StudentResultSetReader implements RowMapper {
 public StudentResultSetReader() {
 }

 public Student read(ResultSet rs) throws SQLException {
  Student t = new Student(rs.getInt("roll"));
  ResultSetMetaData md = rs.getMetaData();
  int numCols  = rs.getMetaData().getColumnCount();
  for (int i = 1; i <= numCols; i++) {
   t.putObject(md.getColumnName(i), rs.getObject(i));
  }
  return t;
 }
 @Override
 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
  return read(rs);
 }
}

StudentDAO.java

package binod.suman.rowmapper.dao;

import java.util.List;
import binod.suman.rowmapper.domain.Student;
public interface StudentDAO {
// public void insertStudent(Student s);
 public Student selectStudent(int roll);
 public List selectAllStudent();
}


beanx.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans"
 http://www.springframework.org/schema/beans/spring-beans.xsd"/ >

 <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/suman"/>
        <property name="username" value="root"/>
        <property name="password" value="mysql"/>
    </bean>
 
 <bean id="studentDAO" class="binod.suman.rowmapper.dao.StudentDAOImpl">
  <property name="dataSource" ref="dataSource"/>
 </bean>

</beans>
Main.java

package binod.suman.rowmapper.dao;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import binod.suman.rowmapper.domain.Student;

public class Main {
 public static void main(String[] args) {
  ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
  StudentDAO studentDAO = (StudentDAO) context.getBean("studentDAO");
//  Student student = new Student(251,"Binod Suman", "Espoo");
//  studentDAO.insertStudent(student);
  Student ss = (Student)studentDAO.selectStudent(150);
  System.out.println(ss);
 
  List ssList = studentDAO.selectAllStudent();
  System.out.println("Total Record :: "+ssList.size());
  for(Student s : ssList){
   System.out.println("******************");
   System.out.println(s);
   }
  }
}


Jar files required:
org.springframework.asm-3.0.0.M3.jar
org.springframework.beans-3.0.0.M3.jar
org.springframework.context-3.0.0.M3.jar
org.springframework.context.support-3.0.0.M3.jar
org.springframework.core-3.0.0.M3.jar
org.springframework.expression-3.0.0.M3.jar
org.springframework.jdbc-3.0.0.M3.jar
org.springframework.transaction-3.0.0.M3.jar
mysql-connector-java-3.1.12-bin.jar
antlr-runtime-3.0.jar
commons-dbcp.jar
commons-logging-1.0.4.jar
commons-pool.jar
hsqldb.jar

You need to create one database schema with name suman and one student table shoule be there:

CREATE TABLE student (
  sname varchar(100) default NULL,
  roll int(4) NOT NULL,
  city varchar(100) default NULL,
  PRIMARY KEY  (`roll`)
)

and some data should be there:
insert into student ('Binod',150,'Helsinki');

Details documentation on RowMapper

Thanks,

Binod Suman