August 1 2016

Spring JdbcTemplate stored procedure example

Spring JdbcTemplate stored procedure example

Spring JdbcTemplate provides support for calling stored procedures. The method ‘call’ is added in JdbcTemplate to execute the stored procedures.

public Map call(CallableStatementCreator csc,List declaredParameters) throws DataAccessException

The method takes two parameters

  1. CallableStatementCreator csc

First parameter of the method is CallableStatementCreator interface which is used by JdbcTemplate to get CallableStatement. Implementation class of the interface has to implement the ‘createCallableStatement’ method.

The ‘createCallableStatement’ method creates a callable statement in this connection with required parameters and returns. SQLExceptions in this method will be caught and handled by the calling JdbcTemplate.

CallableStatement createCallableStatement(Connection con) throws SQLException

  1. List declaredParameters

Second parameter of the method is java.uti.List which contains list of required SqlParameter objects.

Example

Stored Procedure


DELIMITER $$

DROP PROCEDURE IF EXISTS `EMPLOYEE`.`getEmployeeName` $$

CREATE PROCEDURE `EMPLOYEE`.`getEmployeeName`

   (IN EMP_ID INT, OUT EMP_NAME VARCHAR(255))

BEGIN

   SELECT NAME INTO EMP_NAME

   FROM Employee

   WHERE EMPLOYEE_ID = EMP_ID;

END $$

DELIMITER ;

Employee.java


package com.answersz.springJdbcTemplate;

public class Employee {

private Integer age;
 private Integer employeeId;
 private String fatherName;
 private String name;

public Integer getAge() {
 return age;
 }

public void setAge(Integer age) {
 this.age = age;
 }

public Integer getEmployeeId() {
 return employeeId;
 }

public void setEmployeeId(Integer employeeId) {
 this.employeeId = employeeId;
 }

public String getFatherName() {
 return fatherName;
 }

public void setFatherName(String fatherName) {
 this.fatherName = fatherName;
 }

public String getName() {
 return name;
 }

public void setName(String name) {
 this.name = name;
 }

}

CallableStatementBean.java

 package com.answersz.springJdbcTemplate;
 
 import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Types;
 
 import org.springframework.jdbc.core.CallableStatementCreator;
 
 public class CallableStatementBean implements CallableStatementCreator {
 
  private Integer employeeId;
 
  public CallableStatementBean(Integer employeeId) {
  super();
  this.employeeId = employeeId;
  }
 
  public CallableStatement createCallableStatement(Connection con) throws SQLException {
  CallableStatement callableStatement = con.prepareCall("{ call getEmployeeName(?,?)}");
  callableStatement.setInt(1, employeeId);
  callableStatement.registerOutParameter(2, Types.LONGNVARCHAR);
  return callableStatement;
  }
 
 }
 

EmployeeDAO.java

 package com.answersz.springJdbcTemplate;
 
 import java.sql.Types;
 import java.util.ArrayList;
 import java.util.Map;
 
 import org.springframework.jdbc.core.SqlOutParameter;
 import org.springframework.jdbc.core.SqlParameter;
 import org.springframework.jdbc.core.support.JdbcDaoSupport;
 
 public class EmployeeDAO extends JdbcDaoSupport {
 
  public String getEmployeeName(Integer employeeId) {
  ArrayList<SqlParameter> params = new ArrayList<SqlParameter>();
  params.add(new SqlParameter(Types.INTEGER));
  params.add(new SqlOutParameter("EMP_NAME", Types.LONGNVARCHAR));
  Map<String, Object> results = getJdbcTemplate().call(new CallableStatementBean(employeeId), params);
  String name = (String) results.get("EMP_NAME");
  return name;
  }
 }
 

springStoredProcedure.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-2.5.xsd">
 
  <bean id="employeeDAO" class="com.answersz.springJdbcTemplate.EmployeeDAO">
  <property name="dataSource" ref="dataSource" />
  </bean>
 
  <bean id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <property name="url" value="jdbc:mysql://localhost:3306/answersz" />
  <property name="username" value="root" />
  <property name="password" value="admin" />
  </bean>
 
 
 </beans>
 

SpringStoredProcedureTest.java

package com.answersz.springJdbcTemplate;
 
 import org.springframework.context.ApplicationContext;
 import org.springframework.context.support.ClassPathXmlApplicationContext;
 
 public class SpringStoredProcedureTest {
 
  public static void main(String[] args) {
 
  // Spring JdbcTemplate stored procedure example
  
  ApplicationContext context = new ClassPathXmlApplicationContext("springStoredProcedure.xml");
 
  EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");
  String name = employeeDAO.getEmployeeName(11);
  System.out.println("Employee Name : " + name);
  }
 }

Output

 Employee Name : Robert
 


Copyright © 2014. All rights reserved.

Posted August 1, 2016 by answersz in category Spring

Leave a Comment

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

*