Calling Oracle stored procedure using Spring JDBC

In this post lets discuss how to use spring jdbc api and Spring StoredProcedure classes to call Oracle Stored proc. please take a look at the below code snippet
declaring package

create or replace 
PACKAGE MY_PACKAGE
AS
-- ref cursor to return procedure results with an unlimited number of records
TYPE CUR_DATA IS REF CURSOR;
PROCEDURE MY_PROCEDURE(OUT_DATA OUT CUR_DATA);
END MY_PACKAGE;

implementing package body as follows

create or replace 
PACKAGE BODY MY_PACKAGE
AS
PROCEDURE MY_PROCEDURE(OUT_DATA OUT CUR_DATA)
IS
/* this procedure returns a Ref Cursor with all the requested parameters
calling the stored procedure from an asp page (and anywhere else)
does not require posting a predefined number of records */
BEGIN
OPEN OUT_DATA FOR
SELECT empno, ename, hiredate, job, sal, deptno
FROM EMP
ORDER BY empno;
END MY_PROCEDURE;
END MY_PACKAGE;

Now lets code the applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:jms="http://www.springframework.org/schema/jms" xmlns:context="http://www.springframework.org/schema/context"

	xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="propertyPlaceholderConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>jdbc.properties</value>
			</list>
		</property>
	</bean>

	<bean id="springDataSource"
		class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
		<property name="driverClassName" value="${db.driver}" />
		<property name="url" value="${db.url}" />
		<property name="username" value="${db.user}" />
		<property name="password" value="${db.password}" />
	</bean>

	<bean id="employeeDao" class="com.spring.jdbc.dao.impl.EmployeeDaoImpl">
		<constructor-arg name="datasource" ref="springDataSource"></constructor-arg>
	</bean>

</beans>

here we code an interface and its implementation class called EmployeeDAO and EmployeeDAOImpl which has the business methods as follows.

/**
 * 
 */
package com.spring.jdbc.dao;

import java.util.Map;

/**
 * @author Sony
 *
 */
public interface EmployeeDAO {

	public  Map<String,Object> getEmployeeDetails();
}

EmployeeDaoImpl.java

package com.spring.jdbc.dao.impl;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.dao.EmployeeDAO;

/**
 * @author Sony
 * 
 */
public class EmployeeDaoImpl extends BaseProcedure implements EmployeeDAO {

	/**
	 * @param datasource
	 */
	public EmployeeDaoImpl(DataSource datasource) {
		super(datasource);
	}
	
	/* (non-Javadoc)
	 * @see com.spring.jdbc.dao.EmployeeDAO#getEmployeeDetails(int)
	 */
	@Override
	public  Map<String,Object> getEmployeeDetails() {
		
		return execute();
	}

}

The above class extends a class “BaseProcedure.java” which is the actual logic for calling Oracle Stored Proc.

/**
 * 
 */
package com.spring.jdbc.dao.impl;

import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

/**
 * @author Sony
 *
 */
public class BaseProcedure extends StoredProcedure{

	 private static final String SPROC_NAME = "MY_PACKAGE.MY_PROCEDURE";
	 
     public BaseProcedure( DataSource datasource ){
             super( datasource, SPROC_NAME );
             declareParameter( new SqlOutParameter( "OUT_DATA", OracleTypes.CURSOR,new EmployeeRowMapper())); //declaring sql out parameter
             compile();
     }
    
     public  Map<String,Object> execute(){
             Map<String,Object> results = super.execute();
             return results; //reading output of stored procedure using out parameters
                                    
     }
}

Here now we discuss the EmployeeRowMapper.java which will map single rows with the Employee Object Properties as show below.
Employee.java

/**
 * 
 */
package com.sparing.jdbc.pojo;

import java.io.Serializable;
import java.sql.Date;

/**
 * @author Sony
 * 
 */
public class Employee implements Serializable {

	private int empId;
	private String eName;
	private Date hireDate;
	private String job;
	private long sal;
	private int deptNo;

	/**
	 * @return the empId
	 */
	public int getEmpId() {
		return empId;
	}

	/**
	 * @param empId
	 *            the empId to set
	 */
	public void setEmpId(int empId) {
		this.empId = empId;
	}

	/**
	 * @return the eName
	 */
	public String geteName() {
		return eName;
	}

	/**
	 * @param eName
	 *            the eName to set
	 */
	public void seteName(String eName) {
		this.eName = eName;
	}

	/**
	 * @return the hireDate
	 */
	public Date getHireDate() {
		return hireDate;
	}

	/**
	 * @param hireDate
	 *            the hireDate to set
	 */
	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}

	/**
	 * @return the job
	 */
	public String getJob() {
		return job;
	}

	/**
	 * @param job
	 *            the job to set
	 */
	public void setJob(String job) {
		this.job = job;
	}

	/**
	 * @return the sal
	 */
	public long getSal() {
		return sal;
	}

	/**
	 * @param sal
	 *            the sal to set
	 */
	public void setSal(long sal) {
		this.sal = sal;
	}

	/**
	 * @return the deptNo
	 */
	public int getDeptNo() {
		return deptNo;
	}

	/**
	 * @param deptNo
	 *            the deptNo to set
	 */
	public void setDeptNo(int deptNo) {
		this.deptNo = deptNo;
	}

}

EmployeeRowMapper.java

/**
 * 
 */
package com.spring.jdbc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.sparing.jdbc.pojo.Employee;

/**
 * @author Sony
 *
 */
public class EmployeeRowMapper implements RowMapper<Object>{

	@Override
	public Object mapRow(ResultSet resultSet, int row) throws SQLException {
		Employee employee = new Employee();
		employee.setEmpId(resultSet.getInt(1));
		employee.seteName(resultSet.getString(2));
		employee.setHireDate(resultSet.getDate(3));
		employee.setJob(resultSet.getString(4));
		employee.setSal(resultSet.getLong(5));
		employee.setDeptNo(resultSet.getInt(6));
		return employee;
	}

}

now lets write a main class to test the call for stored proc as follows.

/**
 * 
 */
package com.spring.jdbc.dao.test;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.sparing.jdbc.pojo.Employee;
import com.spring.jdbc.dao.EmployeeDAO;

/**
 * @author Sony
 * 
 */
public class SpringJdbcTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		EmployeeDAO employeeDAO = (EmployeeDAO) applicationContext
				.getBean("employeeDao");

		Map<String, Object> results = employeeDAO.getEmployeeDetails();

		Set<String> keys = results.keySet();
		Iterator<String> iterator = keys.iterator();
		List<Employee> employees = null;
		
		while (iterator.hasNext()) {
			String key = (String) iterator.next();
			employees = (List<Employee>) results.get(key);
		}

		for (Employee employee : employees) {
			System.out.println(employee.geteName());
		}

	}
}

finally let me expose my jdbc.properties as follows.

db.url=jdbc:oracle:thin:@localhost:1521:xe
db.driver=oracle.jdbc.driver.OracleDriver
db.user=xxxx
db.password=xxxx

Happy Coding and calling procs with Spring 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s