Using Spring3 JdbcTemplate RowMapper To Return List Of records

preface : This Tutorial explains about Spring3 JdbcTemplate RowMapper using XML
Mapping
,Here we are going to Learn about Spring3 JdbcTemplate RowMapper of 
Spring3 to fetch the List of records from database table,and inserting record in
database using Spring3 JdbcTemplate and DI functionality of the Spring by xml
mapping as metadata.
Prerequisites: Before moving ahead we need some setup as given bellow.

1:Database : MySql (or which database you like , database configuration must be
in spring3-beans.xml)
2: Create a database name : java2t_spring3tutorialdb  using bellow query or using
database GUI Tool.

Query  :  CREATE DATABASE java2t_spring3tutorialdb

 

3: Create a table using below query.

CREATE TABLE `employee` (

  `emp_id` INT(10NOT NULL,

  `first_name` VARCHAR(50NULL DEFAULT NULL,

  `last_name` VARCHAR(50NULL DEFAULT NULL,

  `dob` VARCHAR(50NULL DEFAULT NULL,

  `address` VARCHAR(50NULL DEFAULT NULL,

  `education` VARCHAR(50NULL DEFAULT NULL

)

COLLATE=‘latin1_swedish_ci

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

NOTE : Above query are for MySQL database for  other database queries might need to be modify.

 

Step 1:Open the eclipse workspace and create a java project .Step 2:Create the lib folder and place the required library (*.jar )
files   in the lib folder and add the jar file in the build path.
Jar files are given below: Download jars from here

 

1.  antlr-runtime-3.0.jar

2.  commons-dbcp.jar

3.  commons-logging-1.0.4.jar

4. commons-pool.jar

5.  mysql-connector-java-5.0.7.jar

6.  org.springframework.asm-3.0.0.M3.jar

7.  org.springframework.beans-3.0.0.M3.jar

8.  org.springframework.context-3.0.0.M3.jar

9.  org.springframework.context.support-3.0.0.M3.jar

10.  org.springframework.core-3.0.0.M3.jar

11. org.springframework.expression-3.0.0.M3.jar

12.  org.springframework.jdbc-3.0.0.M3.jar

13.  org.springframework.transaction-3.0.0.M3.jar

 

Step 3: create the spring3-beans.xml or you may put the xml file name
according to your choice and place in the root directory or directory as
per your choice Refer the below screen shot for project structure and O/P

Spring3 JdbcTemplate RowMapper to Fetch List of Records.

 

 

 

spring3-beans.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”
>
  <!– DataSource Configuration –>
<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:3306/java2t_spring3tutorialdb” />

    <property name=“username” value=“root” />

    <property name=“password” value=“root” />

  </bean>

  <!– JdbcTemplate Configuration –>

  <bean id=“jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>

    <property name=“dataSource” ref=“dataSource” />

  </bean>

  <bean id=“employeeDAO” class=“com.java2t.Spring3Tutorial3.dao.impl.EmployeeDAOImpl”>

    <property name=“jdbcTemplate” ref=“jdbcTemplate” />

  </bean>

   <bean id=“employeeService”
class=“com.java2t.Spring3Tutorial3.service.impl.EmployeeServiceImpl”>

    <property name=“employeeDAO” ref=“employeeDAO” />

  </bean>

</beans>

Step 4: Create the package and java files (Spring3JdbcTemplateRowMapperDemo.java,
Employee.java,EmployeeDAO.java ,EmployeeDAOImpl.java ,
EmployeeService.java,EmployeeServiceImpl.javaas given bellow.

Employee.java


package com.java2t.Spring3Tutorial3.model;

public class Employee {

  private Integer employeeId;
  private String fname;
  private String lname;
  private String address;
  private String eduation;
  private String dob;  

  public Employee(){
  }

  public Employee(Integer employeeId,String fname,String lname,
String address,String education,String dob
){

    this.employeeId=employeeId;
    this.fname=fname;
    this.lname=lname;
    this.address=address;
    this.eduation=education;
    this.dob=dob;

  }

  public Integer getEmployeeId() {

    return employeeId;

  }

  public void setEmployeeId(Integer employeeId) {

    this.employeeId = employeeId;

  }

  public String getFname() {

    return fname;

  }

  public void setFname(String fname) {

    this.fname = fname;

  }

  public String getLname() {

    return lname;

  }

  public void setLname(String lname) {

    this.lname = lname;

  }

  public String getAddress() {

    return address;

  }

  public void setAddress(String address) {

    this.address = address;

  }

  public String getEduation() {

    return eduation;

  }

  public void setEduation(String eduation) {

    this.eduation = eduation;

  }

  public String getDob() {

    return dob;

  }

  public void setDob(String dob) {

    this.dob = dob;

  }

  

}

***************************************************************************

EmployeeDAO.java


package com.java2t.Spring3Tutorial3.dao;

import java.util.List;

import com.java2t.Spring3Tutorial3.model.Employee;

public interface EmployeeDAO {

  public abstract void insertEmployee(Employee employee);

  public abstract List<Employee> fetchEmployeeRecord();

  

}

***************************************************************************

EmployeeDAOImpl.java


package com.java2t.Spring3Tutorial3.dao.impl;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import com.java2t.Spring3Tutorial3.dao.EmployeeDAO;

import com.java2t.Spring3Tutorial3.model.Employee;

public class EmployeeDAOImpl implements EmployeeDAO {

  private JdbcTemplate jdbcTemplate;

  public JdbcTemplate getJdbcTemplate() {

    return jdbcTemplate;

  }

  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

    this.jdbcTemplate = jdbcTemplate;

  }

  @Override

  public void insertEmployee(Employee employee) {

    StringBuffer employeeInsertSQLQuery = new StringBuffer(

        “insert into employee(emp_id,first_name,last_name,dob,address,education) values(“);

    if (employeeInsertSQLQuery != null) {

      employeeInsertSQLQuery.append(employee.getEmployeeId() “,”);

      employeeInsertSQLQuery.append(“‘” + employee.getFname() “‘,”);

      employeeInsertSQLQuery.append(“‘” + employee.getLname() “‘,”);

      employeeInsertSQLQuery.append(“‘” + employee.getDob() “‘,”);

      employeeInsertSQLQuery.append(“‘” + employee.getAddress() “‘,”);

      employeeInsertSQLQuery.append(“‘” + employee.getEduation() “‘”);

      employeeInsertSQLQuery.append(“)”);

      getJdbcTemplate().execute(employeeInsertSQLQuery.toString());

    }

  }

  @Override

  public List<Employee> fetchEmployeeRecord() {

    String query = “select emp_id,first_name,last_name,dob,address,education from employee”;

    RowMapper rowMapper = new RowMapper() {

      @Override

      public Object mapRow(ResultSet rs, int arg1)

          throws SQLException {

        Employee employee = new Employee(rs.getInt(“emp_id”),
                                                    rs.getString(“first_name”),
rs
.getString(“last_name”),
rs
.getString(“dob”),
rs
.getString(“address”),
rs
.getString(“education”));
return employee;

      }

    };

    List<Employee> empList = getJdbcTemplate().query(query,

        rowMapper);

    return empList;

  }

}

***************************************************************************

EmployeeService.java


package com.java2t.Spring3Tutorial3.service;

import java.util.List;

import com.java2t.Spring3Tutorial3.model.Employee;

public interface EmployeeService {

  public void addEmployee(Employee employee);

  public List<Employee> viewEmployeeDetail();

}

***************************************************************************

EmployeeServiceImpl.java


package com.java2t.Spring3Tutorial3.service.impl;

import java.util.List;

import com.java2t.Spring3Tutorial3.dao.EmployeeDAO;

import com.java2t.Spring3Tutorial3.model.Employee;

import com.java2t.Spring3Tutorial3.service.EmployeeService;

public class EmployeeServiceImpl implements EmployeeService{

   private EmployeeDAO employeeDAO;

  public EmployeeDAO getEmployeeDAO() {

    return employeeDAO;

  }

  public void setEmployeeDAO(EmployeeDAO employeeDAO) {

    this.employeeDAO = employeeDAO;

  }

    

   

  @Override

  public void addEmployee(Employee employee) {

    getEmployeeDAO().insertEmployee(employee);

    

  }

  @Override

  public List<Employee> viewEmployeeDetail() {

    

    return employeeDAO.fetchEmployeeRecord();

  }

}

***************************************************************************

Spring3JdbcTemplateRowMapperDemo.java


package com.java2t.Spring3Tutorial3.main;

import java.util.List;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.java2t.Spring3Tutorial3.model.Employee;

import com.java2t.Spring3Tutorial3.service.EmployeeService;

public class Spring3JdbcTemplateRowMapperDemo {

  public static void main(String[] args) {

    ApplicationContext context = new ClassPathXmlApplicationContext(

        “spring3-beans.xml”);

    EmployeeService employeeService = (EmployeeServicecontext

        .getBean(“employeeService”);

    employeeService.addEmployee(new Employee(1“Raj”“Kumar”“India”,

        “BTech”“1980/12/12″));

    employeeService.addEmployee(new Employee(2“Rajesh”“Kumar”“India”,

        “MTech”“1982/12/13″));

    List<Employee> emplList = employeeService.viewEmployeeDetail();

    for (Employee employee : emplList) {

      System.out.println(employee.getEmployeeId() “    : ”

          + employee.getFname() “  : ” + employee.getLname()

          “ : ” + employee.getAddress() “ : ”

          + employee.getEduation() “ : ” + employee.getDob());

    }

    System.out.println();

  }

}

Step 5:Build and run the the Spring3JdbcTemplateRowMapperDemo.java files

Console O/P:

Sep 122011 3:23:55 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@18a47e0:
display name 
[org.springframework.context.support.ClassPathXmlApplicationContext@18a47e0];
startup date 
[Mon Sep 12 15:23:55 IST 2011]; root of context hierarchySep 122011 3:23:58 PM
org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring3-beans.xml]Sep 122011 3:24:10 PM org.springframework.context.support.AbstractApplicationContext
obtainFreshBeanFactory
INFO: Bean factory for application context
[org.springframework.context.support.ClassPathXmlApplicationContext@18a47e0]:
org.springframework.beans.factory.support.DefaultListableBeanFactory
@ba6c83Sep 122011 3:24:10 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletonsINFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@ba6c83: defining beans [dataSource,jdbcTemplate,employeeDAO,employeeService]; root of factory hierarchy

1    : Raj  : Kumar : 1980/12/12 : India : BTech

2    : Rajesh  : Kumar : 1982/12/13 : India : MTech


Conclusion: Here we Learned about Spring3 JdbcTemplate RowMapper to fetch the
List of records form the Database and how to insert record into the database
using Spring3  JdbcTemplate ,
DI (Dependency injection) using XML Mapping,
This entry was posted in Spring3.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>