Spring - Execute Stored Procedure

By arunraj

In this article let’s discuss about how to execute stored procedures using Spring framework.

Please refer article on Spring JDBC, to know about performing JDBC operations (SQL) using Spring.

CREATE OR REPLACE PROCEDURE get_col_values(emp_names OUT varchar2)
   AS
   v_empnames   VARCHAR2 (4000);

   CURSOR c_emp_names
   IS
      SELECT DISTINCT emp_name FROM employees;
BEGIN
   FOR i IN c_emp_names
   LOOP
      v_empnames := v_empnames || TRIM (i.emp_name) || ',';
   END LOOP;

   v_empnames := RTRIM (v_empnames, ', ');
   emp_names := v_empnames;
END;

Consider the above stored procedure. It returns the employee names as comma separated values. Let’s see how to call this procedure using Spring JDBC.

First create a class which extends from StoredProcedure Spring api.

ProcedureAndFunctions.java

public class ProcedureAndFunctions extends StoredProcedure
{
        public ProcedureAndFunctions()
        {
        }

        public Map execute()
        {
                Map inputs = new HashMap();
                return super.execute(inputs);
        }
}

Create a DAO class to access the stored procedure.

EmpDaoJdbc

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

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import in.techdive.spring.dao.ProcedureAndFunctions;

public class EmpDaoJdbc extends SimpleJdbcDaoSupport
{
        public String getAttrNamesProc()
        {
                String st = "";
                ProcedureAndFunctions stproc = new ProcedureAndFunctions();

                stproc.setJdbcTemplate(getJdbcTemplate());
                stproc.setSql("get_col_values");
                // uncomment the below line if you going to call sql function.
                // stproc.setFunction(true);

                stproc.declareParameter(new SqlOutParameter(st, Types.VARCHAR));
                stproc.compile();
                Map results = stproc.execute();

                for (Iterator it = results.keySet().iterator(); it.hasNext();)
                {
                        System.out.println(results.get(it.next()));
                }
                return st;
        }
}

Here is the Spring application context file for your reference.

spring-stProcedure.xml

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
               
   <property name="url" value="<jdbc-url>"/>
   <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
   <property name="username" value="<userName>"/>
   <property name="password" value="<passWord>"/>

</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"/>
</bean>

<bean id="EmpDaoJdbc" class="in.techdive.spring.dao.impl.EmpDaoJdbc">
        <property name="jdbcTemplate" ref="jdbcTemplate" />

</bean>

Finally we can test our code using the following class

SpringJdbcTest.java

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import in.techdive.spring.dao.impl.EmpDaoJdbc;

public class SpringJdbcTest
{
    public static void main(String[] args)
    {
        ApplicationContext ctx = new FileSystemXmlApplicationContext(
                new String [] {"classpath*:spring-stProcedure.xml"});
                       
        EmpDaoJdbc empDaoJdbc = (EmpDaoJdbc)ctx.getBean("EmpDaoJdbc");
        empDaoJdbc.getEmpNamesProc();
    }
}

In this way you can call stored procedure/ function using Spring JDBC.

Technology: 
Spring
  • 13494 reads

Search