Handling Oracle Stored Procedures with Arrays or Table Types in Spring

Hello guys, in this post lets discuss how to handle oracle procedure that take custom or array types as input params and return array type or table type as out param.

this example has been tested with spring 3.2.x and with oracle 10g xe.
note : run the below scripts in oracle to create objects prior to coding java.

-- custom type
create or replace TYPE "KEYVALUEOBJECT"
as object(name varchar(255),
value varchar(255));
================================================================

-- array of KeyValueObject
create or replace
TYPE "MY_OBJECT_ARRAY"
as table of KeyValueObject;

================================================================
-- this proc, doesn't do too much
create or replace
procedure Sample_Proc(
i_array in MY_OBJECT_ARRAY,
o_array out MY_OBJECT_ARRAY)
as
begin
o_array := MY_OBJECT_ARRAY();
for i in 1 .. i_array.count loop

o_array.extend;
o_array(i) := KeyValueObject(i_array(i).name, i_array(i).value);
end loop;
end;

applicationContext.java

<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="keyValueDao" class="com.spring.jdbc.dao.tab.type.impl.KeyValueDaoImpl">
		<constructor-arg name="dataSource" ref="springDataSource"></constructor-arg>
	</bean>

</beans>

KeyValueDao.java

package com.spring.jdbc.dao;

import com.sparing.jdbc.pojo.KeyValueObject;

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

	public KeyValueObject[] getKeyValueObjects(KeyValueObject[] keyValueObjects);
	
}

KeyValueDaoImpl.java

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

import javax.sql.DataSource;

import com.sparing.jdbc.pojo.KeyValueObject;
import com.spring.jdbc.dao.KeyValueDao;

/**
 * @author Sony
 *
 */
public class KeyValueDaoImpl extends TableTypeBaseProcedure implements KeyValueDao{

	public KeyValueDaoImpl(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	public KeyValueObject[] getKeyValueObjects(KeyValueObject[] keyValueObjects) {
		
		return execute(keyValueObjects);
		
	}

}

the main business logic exist in this class.
TableTypeBaseProcedure.java

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

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import oracle.sql.ARRAY;

import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnType;
import org.springframework.jdbc.object.StoredProcedure;

import com.sparing.jdbc.pojo.KeyValueObject;

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

	private static final String PROC_NAME = "Sample_Proc";
	private static final String TAB_TYPE = "KEYVALUEOBJECT";
	
	private static final String MY_ARRAY = "MY_OBJECT_ARRAY";
	private static final String I_ARRAY = "input_array";
	private static final String O_ARRAY = "output_array";


	public TableTypeBaseProcedure(DataSource dataSource) {
		
		super(dataSource, PROC_NAME);
		
		declareParameter(new SqlParameter(I_ARRAY, Types.ARRAY, MY_ARRAY));
		declareParameter(new SqlOutParameter(O_ARRAY, Types.ARRAY, MY_ARRAY,
				new SqlReturnType() {

					@Override
					public Object getTypeValue(CallableStatement cs,
							int paramIndex, int sqlType, String typeName)
							throws SQLException {
						Connection connection = cs.getConnection();
						Map<String, Class<?>> typeMap = connection.getTypeMap();
						typeMap.put(TAB_TYPE, KeyValueObject.class);
						return cs.getObject(paramIndex);
					}
				}));
		compile();
	}

	public KeyValueObject[] execute(KeyValueObject[] keyValueObjects) {
		Map<String, Object> params = new HashMap<>();
		params.put(I_ARRAY, new KeyValueSqlType(keyValueObjects));

		Map<?, ?> result = execute(params);

		if ((!result.containsKey(O_ARRAY) || result.get(O_ARRAY) == null)) {
			return null;
		}

		try {
			Object[] resultArray = (Object[]) ((ARRAY) result.get(O_ARRAY))
					.getArray();

			return Arrays.copyOf(resultArray, resultArray.length,
					KeyValueObject[].class);
		} catch (SQLException e) {
			throw new DataRetrievalFailureException("Unable to retrieve array",
					e);
		}
	}

}

this is a helper class
KeyValueSqlType.java

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

import java.sql.Connection;
import java.sql.SQLException;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import org.springframework.jdbc.core.support.AbstractSqlTypeValue;

import com.sparing.jdbc.pojo.KeyValueObject;

/**
 * @author Sony
 *
 */
public class KeyValueSqlType extends AbstractSqlTypeValue{

	private KeyValueObject[] keyValueObjects;
	
	public KeyValueSqlType(KeyValueObject[] keyValueObjects) {
		this.keyValueObjects = keyValueObjects;
	}
	
	@Override
	protected Object createTypeValue(Connection connection, int sqlType,
			String typeName) throws SQLException {
		
		ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, connection);
		return new ARRAY(arrayDescriptor,connection,keyValueObjects);
	}

}

The main domain object that will flow in and out to database.
KeyValueObject.java

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

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;

/**
 * @author Sony
 * 
 */
public class KeyValueObject implements SQLData {

	private String name;
	private String value;

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

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

	/**
	 * @return the value
	 */
	public String getValue() {
		return value;
	}

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

	@Override
	public String getSQLTypeName() throws SQLException {
		return "KeyValueObject";
	}

	@Override
	public void readSQL(SQLInput stream, String typeName) throws SQLException {

		name = stream.readString();
		value = stream.readString();
	}

	@Override
	public void writeSQL(SQLOutput stream) throws SQLException {

		stream.writeString(name);
		stream.writeString(value);
	}

	@Override
	public String toString() {
		return ToStringBuilder.reflectionToString(this,
				ToStringStyle.SHORT_PREFIX_STYLE);
	}

}

Happy Coding 🙂
Happy Oracle Stored Procedures with Arrays or Table Types in Spring 🙂

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 🙂