Handling Oracle Collections using Spring 3 [Part- 1]

Hi All,these days i got little busy with my new assignment of work in my project finally i took some time today and thought of writing a very nice concept on Oracle Collections and Handling them in Spring framework.Oracle has given a very awesome feature called collections such as “TabType, ObjectType, VArray” etc.

Let us create database structure as shown as below.

create or replace 
type actor_type as object (id number(10),name varchar2(50),age number);
=============================================================
 CREATE TABLE "ACTOR" 
   (	"ID" NUMBER(10,0), 
	"NAME" VARCHAR2(50 BYTE), 
	"AGE" NUMBER, 
	 PRIMARY KEY ("ID") );
=============================================================

After we create Object types its time to create db procedures which will actually make use of above created objects.

create or replace 
package PKG_ACTOR AS
  PROCEDURE add_actor (in_actor IN actor_type);
  PROCEDURE get_actor (in_actor_id IN NUMBER, out_actor OUT actor_type);
  PROCEDURE delete_actors (in_actor_ids IN actor_id_array);
END PKG_ACTOR;
==============================================================
create or replace 
package BODY PKG_ACTOR AS
  PROCEDURE add_actor (in_actor IN actor_type) IS
    BEGIN
      INSERT into actor values(in_actor.id,in_actor.name,in_actor.AGE);
      COMMIT;
    END add_actor;
    
    PROCEDURE get_actor (in_actor_id IN NUMBER, out_actor OUT actor_type) IS
    BEGIN
      SELECT actor_type(id, name, age)  into out_actor FROM actor WHERE id = in_actor_id;
    END get_actor;
    
    PROCEDURE delete_actors (in_actor_ids IN actor_id_array) IS
    begin
      FOR i IN 1..in_actor_ids.count loop
        DELETE FROM actor WHERE id = in_actor_ids(i);
        COMMIT;
      END LOOP;
    end delete_actors;
END PKG_ACTOR;
============================================================================

Thats all from database side lets start handling these collection objects using Spring. let use first create domain object which will be serialized to database as below.

/**
 * 
 */
package com.spark.spring.data.domain;

/**
 * @author PavanKumar Mantha
 * 
 */
public class Actor {

	private Long id;
	private String name;
	private int age;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String toString() {
		return "Actor: [" + id + "] " + name + " " + age;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */

	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		return result;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj) {
			return true;
		}
		if (obj == null) {
			return false;
		}
		if (!(obj instanceof Actor)) {
			return false;
		}
		Actor other = (Actor) obj;
		if (id == null) {
			if (other.id != null) {
				return false;
			}
		} else if (!id.equals(other.id)) {
			return false;
		}
		return true;
	}

}

Now let us create an oracle Type object that will exactly interact with the underlying database.

/**
 * 
 */
package com.spark.spring.data.sqltypes;

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

import com.spark.spring.data.domain.Actor;

/**
 * @author PavanKumar Mantha
 * 
 */
public class ActorType extends Actor implements SQLData {

	private final String sqlType = "ACTOR_TYPE";

	/**
	 * (non-Javadoc)
	 * 
	 * @see java.sql.SQLData#getSQLTypeName()
	 */
	public String getSQLTypeName() throws SQLException {
		return sqlType;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see java.sql.SQLData#readSQL(java.sql.SQLInput, java.lang.String)
	 */
	public void readSQL(SQLInput sqlInput, String str) throws SQLException {
		setId(Long.valueOf(sqlInput.readLong()));
		setName(sqlInput.readString());
		setAge(sqlInput.readInt());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see java.sql.SQLData#writeSQL(java.sql.SQLOutput)
	 */
	public void writeSQL(SQLOutput sqlOutput) throws SQLException {
		sqlOutput.writeLong(getId().longValue());
		sqlOutput.writeString(getName());
		sqlOutput.writeInt(getAge());
	}

}

After our Type object is ready we shall start creating the Dao Implementation class which is responsible for persisting the domain object to database.

/**
 * 
 */
package com.spark.spring.data.dao;

import java.sql.SQLException;
import java.sql.Types;
import java.util.Collections;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnType;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.spark.spring.data.dao.rowmappers.SqlReturnSqlData;
import com.spark.spring.data.sqltypes.ActorType;
import com.spark.spring.data.util.SpringDataUtil;

/**
 * @author PavanKumar Mantha
 * 
 */
public class ActorDaoImpl {

	SimpleJdbcCall addActorJdbcCall, getActorJdbcCall, deleteActorJdbcCall;

	private static final String ADD_PROC_NAME = "add_actor";
	private static final String GET_PROC_NAME = "get_actor";

	private static final String CATALOG_NAME = "PKG_ACTOR";

	private final String TAB_TYPE = "ACTOR_TYPE";
	private final String ARRAY_TYPE = "ACTOR_ID_ARRAY";

	private final String IN_PARAM = "in_actor";
	private final String OUT_PARAM = "out_actor";

	public ActorDaoImpl(DataSource dataSource) {

		this.addActorJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName(CATALOG_NAME)
				.withProcedureName(ADD_PROC_NAME)
				.declareParameters(
						new SqlParameter(IN_PARAM, OracleTypes.STRUCT, TAB_TYPE));

		this.getActorJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName(CATALOG_NAME)
				.withProcedureName(GET_PROC_NAME)
				.declareParameters(
						new SqlOutParameter(OUT_PARAM, OracleTypes.STRUCT,
								TAB_TYPE,
								(SqlReturnType) new SqlReturnSqlData()));

		this.deleteActorJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName(CATALOG_NAME)
				.withProcedureName("delete_actors")
				.withoutProcedureColumnMetaDataAccess()
				.declareParameters(
						new SqlParameter("in_actor_ids", OracleTypes.ARRAY,
								"ACTOR_ID_ARRAY"));
	}

	/**
	 * @param actorType
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void addActor(final ActorType actorType) {
		Map inParams = Collections.singletonMap(IN_PARAM, actorType);
		addActorJdbcCall.execute(inParams);
	}

	/**
	 * @param actorId
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public ActorType getActor(final int actorId) throws SQLException{
		Map map = Collections.singletonMap("in_actor_id", actorId);
		return (ActorType) getActorJdbcCall.executeObject(ActorType.class, map);
	}

	/**
	 * @param ids
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void deleteActors(final Long[] ids) {
		Map in = null;
		try {
			in = Collections.singletonMap("in_actor_ids", SpringDataUtil
					.getOrclArrayType(SpringDataUtil.getOracleDataSource()
							.getConnection(), Types.ARRAY, ARRAY_TYPE, ids));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		deleteActorJdbcCall.execute(in);
	}
}

Note : Observer only the type names in above code are case sensitive because oracle stores the Metadata information in capital case.
Now we have to implement the RowMapper class which will map the columns from database to our Java Domain object as below.

/**
 * 
 */
package com.spark.spring.data.dao.rowmappers;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.SQLException;

import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.springframework.jdbc.core.SqlReturnType;

import com.spark.spring.data.sqltypes.ActorType;

/**
 * @author PavanKumar Mantha
 * @param <T>
 * 
 */
public class SqlReturnSqlData implements SqlReturnType {

	Object[] objects;
	Datum[] datums;

	/**
	 * (non-Javadoc)
	 * @see org.springframework.jdbc.core.SqlReturnType#getTypeValue(java.sql.CallableStatement, int, int, java.lang.String)
	 */
	public Object getTypeValue(CallableStatement cs, int parameterIndex,
			int sqlType, String typeName) throws SQLException {
		
		ActorType actorType = new ActorType();
		objects = ((STRUCT) cs.getObject(parameterIndex)).getAttributes();
		actorType.setId(((BigDecimal) objects[0]).longValue());
		actorType.setName((String) objects[1]);
		actorType.setAge(((BigDecimal) objects[2]).intValue());
		return actorType;
	}

}

Now lets write a helper class which actually create an Object,DataSource,ApplicationContext objects as shown below.

/**
 * 
 */
package com.spark.spring.data.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

import javax.sql.DataSource;

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

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

/**
 * @author PavanKumar Mantha
 * 
 */
public class SpringDataUtil {

	private static Future<ApplicationContext> applicationContext;

	static {
		ExecutorService executorService = Executors.newFixedThreadPool(1);
		applicationContext = executorService
				.submit(new Callable<ApplicationContext>() {
					public ApplicationContext call() throws Exception {
						return new ClassPathXmlApplicationContext(
								"application-context.xml");
					}

				});
	}

	/**
	 * @return
	 */
	public static ApplicationContext getContext() {
		try {
			return applicationContext.get();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	/**
	 * @return
	 */
	public static DataSource getOracleDataSource() {
		try {
			return (DataSource) getContext().getBean("orclDataSource");
		} catch (Exception e) {
			throw new RuntimeException();
		}
	}

	/**
	 * @return
	 */
	public static DataSource getMysqleDataSource() {
		try {
			return (DataSource) getContext().getBean("mysqlDataSource");
		} catch (Exception e) {
			throw new RuntimeException();
		}
	}

	/**
	 * @param beanName
	 * @return
	 */
	public static Object getObject(String beanName) {
		return getContext().getBean(beanName);
	}

	/**
	 * @param connection
	 * @param sqlType
	 * @param typeName
	 * @param idsArray
	 * @return
	 * @throws SQLException
	 */
	public static <T> Object getOrclArrayType(Connection connection,
			int sqlType, String typeName, T[] idsArray) throws SQLException {

		ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName,
				connection);
		return new ARRAY(arrayDescriptor, connection, idsArray);
	}
}

In the above code we used the ExecutorService interface of java’s java.util.concurrent package to load the application-context file from the classpath.

now its time for us to write application-context.xml and jdbc.properties as below.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:cache="http://www.springframework.org/schema/cache"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:lang="http://www.springframework.org/schema/lang" xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:task="http://www.springframework.org/schema/task" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
		http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang-3.2.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">

	<context:property-placeholder location="classpath:jdbc.properties" />

	<bean id="orclDataSource" class="oracle.jdbc.pool.OracleDataSource"
		destroy-method="close">
		<property name="URL" value="${orcl.jdbc.url}" />
		<property name="user" value="${orcl.jdbc.username}" />
		<property name="password" value="${orcl.jdbc.password}" />
		<property name="connectionCachingEnabled" value="true" />
	</bean>

	<bean name="mysqlDataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${mysql.jdbc.driver}" />
		<property name="url" value="${mysql.jdbc.url}" />
		<property name="username" value="${mysql.jdbc.user}" />
		<property name="password" value="${mysql.jdbc.password}" />
	</bean>
	
	<bean id="actorDaoImpl" class="com.spark.spring.data.dao.ActorDaoImpl">
		<constructor-arg ref="orclDataSource"></constructor-arg>
	</bean>
</beans>

jdbc.properties:

orcl.jdbc.url=jdbc:oracle:thin:@localhost:1521:XE
orcl.jdbc.username=scott
orcl.jdbc.password=tiger

mysql.jdbc.driver=com.mysql.jdbc.Driver
mysql.jdbc.url=jdbc:mysql//localhost:3306/test
mysql.jdbc.user=root
mysql.jdbc.password=root

now as a final touch here we write a main class to test this application

/**
 * 
 */
package com.spark.spring.data.test;

import com.spark.spring.data.dao.ActorDaoImpl;
import com.spark.spring.data.dao.StateDaoImpl;
import com.spark.spring.data.domain.Capital;
import com.spark.spring.data.domain.State;
import com.spark.spring.data.sqltypes.ActorType;
import com.spark.spring.data.util.SpringDataUtil;

/**
 * @author PavanKumar Mantha
 * 
 */
public class SpringDataTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			
			ActorDaoImpl actorDaoImpl = (ActorDaoImpl)SpringDataUtil.getObject("actorDaoImpl");
			
			ActorType actorType = new ActorType();
			actorType.setId(1L);
			actorType.setName("Pavan Kumar Mantha");
			actorType.setAge(26);
			
			/*actorDaoImpl.addActor(actorType);
			System.out.println("----------------------- Record inserted -----------------------");*/
			
			ActorType type = actorDaoImpl.getActor(2);
			System.out.println(type.getName());
			
			/*Long[] ids = new Long[1];
			ids[0] = 1L;
			actorDaoImpl.deleteActors(ids);*/
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

Note : uncomment actorDaoImpl.addActor(actorType); to insert the record and buttom three lines to delete set of records as a batch delete operation passing array of ids.
The project structure should resemble the screen as below once everything is done.
OraCollections

Lets see the handling of complex Object types in my next Post[Part-2]
Happy Oracle Collections and 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