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 🙂

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