Stored Procedure execution using Spring Framework

1.Create a simple table and procedure ,which returns salary of employee.
input is emp_id and output salary of Employee

CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID     NUMBER(6),
FIRST_NAME      VARCHAR2(20 BYTE),
LAST_NAME       VARCHAR2(25 BYTE) ,
EMAIL           VARCHAR2(25 BYTE),
SALARY          NUMBER(8,2)
)

CREATE OR REPLACE PROCEDURE  get_salary (
  p_emp_id   IN       employees.employee_id%TYPE,
  o_salary   OUT      employees.salary%TYPE
)
IS
BEGIN
SELECT salary
 INTO o_salary
 FROM employees
WHERE employee_id = p_emp_id;
END get_salary;
/

2.Define the database configuration in jdbc.properties file

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@locahost:1521:DEMO
jdbc.username=test
jdbc.password=demo

3.Define applicationContext.xml file and configure dataSource in it.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

 <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

  <property name="locations">

   <value>classpath:/jdbc.properties</value>

  </property>

 </bean>

 <bean id="dataSource" destroy-method="close"

  class="org.apache.commons.dbcp.BasicDataSource">

  <property name="driverClassName" value="${jdbc.driverClassName}" />

  <property name="url" value="${jdbc.url}" />

  <property name="username" value="${jdbc.username}" />

  <property name="password" value="${jdbc.password}" />

 </bean>

</beans>

4.Write a class which extends Spring StoredProcedure class.

  package com.kp.hr.proc;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;



public class GetSalaryProcedure extends StoredProcedure {
  
  private static final String SPROC_NAME="get_salary";
  private static final String EMPLOYEE_ID="p_emp_id";
  private static final String SALARY="o_salary";
   public GetSalaryProcedure(DataSource dataSource) {
        super();
          setDataSource(dataSource);
          setFunction(false);
          setSql(SPROC_NAME);
          
    declareParameter(new SqlParameter(EMPLOYEE_ID, Types.DECIMAL));
    declareParameter(new SqlOutParameter(SALARY, Types.NUMERIC));
         
          compile();
      }
   public Map execute(Integer empId ) {
     Map outs=null;
     try{
          Map inputs = new HashMap();
          System.out.println(empId.intValue());
          inputs.put(EMPLOYEE_ID,empId);
          outs= super.execute(inputs);
     }catch(Exception e){
       e.printStackTrace();
     }
          return outs;
    }
}
    

5.Add new class to call StoredProcedure Class
package com.kp.hr.service;

import java.util.Map;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.kp.hr.proc.GetSalaryProcedure;

public class ProcExecuteService {
  private GetSalaryProcedure getSalProc;
  
  public void getSalary() {
    Map outs = getSalProc.execute(100);
    java.math.BigDecimal sal = (java.math.BigDecimal) outs.get("o_salary");
    System.out.println(sal.intValue());
  }
  
  public static void main(String args[]) {
    ApplicationContext ctx = new ClassPathXmlApplicationContext(
        "applicationContext.xml");
    ProcExecuteService service = (ProcExecuteService) ctx
        .getBean("procExecuteService");
    service.getSalary();
  }
  
  public GetSalaryProcedure getGetSalProc() {
    return getSalProc;
  }
  
  public void setGetSalProc(GetSalaryProcedure getSalProc) {
    this.getSalProc = getSalProc;
  }

}

6.Add two entries for newly added classes in applicationContext.xml file

<bean id="getSalProc" class="com.kp.hr.proc.GetSalaryProcedure">
  <constructor-arg ref="dataSource"/>
</bean>

<bean id="procExecuteService" class="com.kp.hr.service.ProcExecuteService">
   <property name="getSalProc" ref="getSalProc"/>
</bean>

7. project snapshot