Dinesh on Java

The SimpleJdbcCall was introduced in Spring 2.5. It’s goal is to make calling stored procedures as simple as possible. It tries to use JDBC meta-data as much as possible, and it allows the developers to override settings. Using any other class like “JdbcTemplate” or extending from “StoredProcedure” is considered “old school spring”.

Step-1 : Let’s create an Employee table which can be created in MySQL DAVDB database with the following DDL:

CREATE TABLE Employee(  
   EMPID   INT NOT NULL AUTO_INCREMENT,  
   NAME VARCHAR(20) NOT NULL,  
   AGE  INT NOT NULL,  
   SALARY BIGINT NOT NULL,  
   PRIMARY KEY (ID)  
); 

Step-2: Let’s create a stored procedure which takes employee Id (empid) and returns corresponding employee’s name, age and salary using OUT parameters.

DELIMITER $$

DROP PROCEDURE IF EXISTS `DAVDB`.`getEmpRecord` $$
CREATE PROCEDURE `DAVDB`.`getEmpRecord` (
IN in_empid INTEGER,
OUT out_name VARCHAR(26),
OUT out_age  INTEGER,
OUT out_salary  LONG)
BEGIN
   SELECT name, age, salary
   INTO out_name, out_age, out_salary
   FROM Employee where empid = in_empid ;
END $$

DELIMITER ;

Step-3: Create Java Project “SpringSPDemo” and add the required spring library and mysql library as following.

Step-4: Create DAO interface EmpDAO and list down all the required methods. Though it is not required and you can directly write EmployeeJDBCTemplate class, but as a good practice, let’s do it.

package com.dineshonjava.sdnext.dao;

import java.util.List;

import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public interface EmpDao {
 
 /** 
  * This is the method to be used to create
  * a record in the Employee table.
  */
 void create(String name, Integer age, Long salary);
 /** 
  * This is the method to be used to list down
  * a record from the Employee table corresponding
  * to a passed Employee id.
  */
 Employee getEmployee(Integer empid);
 /** 
  * This is the method to be used to list down
  * all the records from the Employee table.
  */
 List listEmployees();
 /** 
  * This is the method to be used to delete
  * a record from the Employee table corresponding
  * to a passed Employee id.
  */
 void delete(Integer empid);
 /** 
  * This is the method to be used to update
  * a record into the Employee table.
  */
 void update(Integer empid, Integer age);
}

Step-5: Create other required Java classes Employee, EmployeeMapper, EmployeeDaoImpl and EmpMainApp under the com.dineshonjava.sdnext.* package.
Employee.java

package com.dineshonjava.sdnext.domain;

/**
 * @author Dinesh Rajput
 *
 */
public class Employee {
 private int empid;
 private String name;
 private int age;
 private long salary;
 /**
  * @return the empid
  */
 public int getEmpid() {
  return empid;
 }
 /**
  * @param empid the empid to set
  */
 public void setEmpid(int empid) {
  this.empid = empid;
 }
 /**
  * @return the name
  */
 public String getName() {
  return name;
 }
 /**
  * @param name the name to set
  */
 public void setName(String name) {
  this.name = name;
 }
 /**
  * @return the age
  */
 public int getAge() {
  return age;
 }
 /**
  * @param age the age to set
  */
 public void setAge(int age) {
  this.age = age;
 }
 /**
  * @return the salary
  */
 public long getSalary() {
  return salary;
 }
 /**
  * @param salary the salary to set
  */
 public void setSalary(long salary) {
  this.salary = salary;
 }
 public String toString(){
  return "EMPLOYEE{empid- "+this.empid+" name- "+this.name+
    " age- "+this.age+" salary- "+this.salary+"}";
 }
}

EmployeeMapper.java

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

EmployeeDaoImpl.java

package com.dineshonjava.sdnext.dao.impl;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;

import com.dineshonjava.sdnext.dao.EmpDao;
import com.dineshonjava.sdnext.domain.Employee;
import com.dineshonjava.sdnext.jdbc.utils.EmployeeMapper;

/**
 * @author Dinesh Rajput
 *
 */
@Component
public class EmployeeDaoImpl implements EmpDao {
 @Autowired
 private DataSource dataSource;
 @Autowired
 private JdbcTemplate jdbcTemplateObject;
 private SimpleJdbcCall simpleJdbcCall;
 
 @Override
 public void create(String name, Integer age, Long salary) {
  String SQL = "INSERT INTO Employee (name, age, salary) VALUES (?, ?, ?)";
  jdbcTemplateObject.update(SQL, new Object[]{name, age, salary} );
     System.out.println("Created Record Name = " + name + " Age = " + age+ " Salary = " + salary);
 }

 /**
  * @param jdbcTemplateObject the jdbcTemplateObject to set
  */
 public void setJdbcTemplateObject(JdbcTemplate jdbcTemplateObject) {
  this.jdbcTemplateObject = jdbcTemplateObject;
 }

 /**
  * @param dataSource the dataSource to set
  */
 @Autowired
 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
  this.simpleJdbcCall = new SimpleJdbcCall(this.dataSource).withProcedureName("getEmpRecord");
 }

@Override
public Employee getEmployee(Integer empid) {
 SqlParameterSource in = new MapSqlParameterSource().addValue("in_empid", empid);
   Map out = simpleJdbcCall.execute(in);
   Employee employee = new Employee();
   employee.setEmpid(empid);
   employee.setName((String)out.get("out_name"));
   employee.setAge((Integer)out.get("out_age"));
   employee.setSalary(Long.valueOf((String)out.get("out_salary")));
   return employee;
}

 @Override
 public List listEmployees() {
  String SQL = "SELECT * FROM Employee";
  List employees = (List) jdbcTemplateObject.query(SQL, new EmployeeMapper());
     return employees;
 }

 @Override
 public void delete(Integer empid) {
  String SQL = "DELETE FROM Employee WHERE empid = ?";
  jdbcTemplateObject.update(SQL, new Object[]{empid});
  System.out.println("Deleted Record with EMPID = " + empid );
 }

 @Override
 public void update(Integer empid, Integer age) {
   String SQL = "UPDATE Employee SET age = ? WHERE empid = ?";
   jdbcTemplateObject.update(SQL, new Object[]{age, empid});
   System.out.println("Updated Record with EMPID = " + empid );
 }
}

EmpMainApp.java

package com.dineshonjava.sdnext.main;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dineshonjava.sdnext.dao.EmpDao;
import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public class EmpMainApp {

 /**
  * @param args
  */
 public static void main(String[] args) {
  ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
  EmpDao empDao = (EmpDao) context.getBean("employeeDaoImpl");
  
  System.out.println("------Records Creation--------" );
  empDao.create("Dinesh", 25, 50000l);
  empDao.create("Anamika", 23, 30000l);
  empDao.create("Nimmo", 24, 30020l);
  empDao.create("Adesh", 24, 30011l);
  empDao.create("Vinesh", 22, 20011l);
  
  System.out.println("------Listing Multiple Records--------" );
  List employees = empDao.listEmployees();
  for (Employee employee : employees) {
          System.out.print(employee);
     }
  
  System.out.println("----Updating Record with EMPID = 2 -----" );
  empDao.update(2, 20);
  
  System.out.println("----Listing Record with EMPID = 2 -----" );
  Employee employee = empDao.getEmployee(2);
  System.out.print(employee);
 }
}

Step-6: Let’s create spring configuration file Spring.xml.

<beans xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:>

After creating source and spring bean configuration, now run this application, it will print following output on the console:
Output:

Dec 9, 2012 4:10:43 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d: display name [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]; startup date [Sun Dec 09 16:10:43 IST 2012]; root of context hierarchy

Dec 9, 2012 4:10:43 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [spring.xml]

Dec 9, 2012 4:10:43 PM org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory

INFO: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]: org.springframework.beans.factory.support.DefaultListableBeanFactory@1e8a1f6

Dec 9, 2012 4:10:43 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons

INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1e8a1f6: defining beans [org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,employeeDaoImpl,dataSource,jdbcTemplateObject]; root of factory hierarchy

----Listing Record with EMPID = 2 -----

Dec 9, 2012 4:10:43 PM org.springframework.jdbc.core.JdbcTemplate extractReturnedResults

INFO: Added default SqlReturnUpdateCount parameter named #update-count-1

EMPLOYEE{empid- 2 name- Anamika age- 20 salary- 30000}
<<Using JdbcTemplate |index| DAO Support Classes>>