Monday, 8 April 2013

Call Stored Procedures in Spring

There are multiple ways to call stored procedure in Spring Framework
1. query() method from JdbcTemplate to call stored procedures
2. extend abstract class StoredProcedure to call stored procedures

Step 1: Create a store Procedure

mysql> DELIMITER // 
mysql> create procedure usp_GetEmployeeName(IN id INT, OUT name VARCHAR(20)) 
-> begin 
-> select emp_name into name from employee where emp_id = id; 
-> end//

 Query OK, 0 rows affected (0.52 sec) 
 mysql> DELIMITER ;

Step2: Java Class which wraps Stored procedure
 In this example, we have extended abstract class StoredProcedure in our class called, EmployeeSP. This is declared as nested class inside EmployeeDAO because its only used by this class, if your stored procedure is used my multiple DAO classes, than you can also make it a top level class. If you look at constructor of EmployeeSP, it calls super class constructor and passes datasource and name of database stored procedure. We have also declared two stored procedure parameters, one is IN parameter id, and other is OUT parameter. Input to stored procedure is passed using IN parameter, and output from stored procedure is read using OUT parameter. Your stored procedure can have multiple IN and OUT parameter. StoredProcedure class also provide several execute() methods, which can be invoked to call stored procedure and get result. It return result as Map, where key is OUT parameter, and value is result of stored procedure.

import java.sql.Types; 
import java.util.Map; 
import javax.sql.DataSource; 
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter; 
import org.springframework.jdbc.core.SqlParameter; 
import org.springframework.jdbc.object.StoredProcedure;
public class EmployeeDao 
 private JdbcTemplate jdbcTemplate;
 private EmployeeSP sproc; 
 public void setDataSource(DataSource source)
  this.jdbcTemplate = new JdbcTemplate(source); 
  this.sproc = new EmployeeSP(jdbcTemplate.getDataSource()); 
 /* * wraps stored procedure call */ 
 public String getEmployeeName(int emp_id)
  return (String) sproc.execute(emp_id); 
 /* * Inner class to implement stored procedure in spring. */ 
 private class EmployeeSP extends StoredProcedure
  private static final String SPROC_NAME = "usp_GetEmployeeName"; 
  public EmployeeSP( DataSource datasource )
   super( datasource, SPROC_NAME ); 
   declareParameter( new SqlParameter( "id", Types.INTEGER) ); //declaring sql in parameter to pass input 
   declareParameter( new SqlOutParameter( "name", Types.VARCHAR ) ); //declaring sql out parameter 
  public Object execute(int emp_id)
   Map results = super.execute(emp_id); 
   return results.get("name"); //reading output of stored procedure using out parameters 

Step3: Test stored procedure

import org.springframework.context.ApplicationContext; 
/* * Main class to start and test this Java application */ 
public class Main 
 public static void main(String args[])
  ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-config.xml"); 
  EmployeeDao dao = (EmployeeDao) ctx.getBean("employeeDao"); //calling stored procedure using DAO method 
  System.out.println("Employee name for id 103 is : " + dao.getEmployeeName(103)); } } 

2013-01-17 23:56:34,408 0 [main] DEBUG EmployeeDao$EmployeeSP - Compiled stored procedure. Call string is [{call usp_GetEmployeeName(?, ?)}] 
2013-01-17 23:56:34,439 31 [main] DEBUG EmployeeDao$EmployeeSP - RdbmsOperation with SQL [usp_GetEmployeeName] compiled 
Employee name for id 103 is : Jack





  1. very useful thank you for this tutorial

    1. i am getting error in
      will u plz....giv any solution..
      thanx in advance

  2. Put this code....It'll work guys.... Map

    1. This comment has been removed by the author.

    2. Map should contains objects of type String and Object. Create Map in that way. Then it'll work


Related Posts Plugin for WordPress, Blogger...