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 
   compile(); 
  } 
  
  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; 
import org.springframework.context.support.ClassPathXmlApplicationContext; 
/* * 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)); } } 
 }
}

Output:

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

spring-config.xml

        
                
                        
                                classpath:jdbc.properties