Handling Oracle Collections using Spring 3 [Part- 2]

As a continuation to my first part here we can discuss how can we handle complex object types from java to oracle. As an example lets take a “State Object which internally have Capital object” now this complete object should get persisted in single column of table with out joins and multiple tables. lets create database first as below.

create or replace
type capital_typ as object (id number, capital_name varchar2(30))
=====================================================
create or replace
type state_typ as object(id number,state_name varchar2(30),capital capital_typ);
=====================================================
create table states(id number,state state_typ);

Now its time to write the procedure implementation for the above created objects.

create or replace
package pkg_state as
  procedure add_state(in_state in state_typ);
  procedure get_state(in_state_id in number,out_state out state_typ);
end pkg_state;
==============================================================================
create or replace
package body pkg_state as
 
  procedure add_state(in_state in state_typ) is
  begin
    insert into states(id, state) values(in_state.id,in_state);
    commit;
  end add_state;
 
  procedure get_state(in_state_id in number,out_state out state_typ) is
  begin
    select state into out_state from states where id = in_state_id;
  end get_state;
end pkg_state;

Once the database is ready now let us start coding or domain objects as we did in the part-1
State.java

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

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

/**
 * @author p7164517
 * 
 */
public class State implements SQLData{

	private int id;
	private String stateName;
	Capital capital;

	/**
	 * @return the capital
	 */
	public Capital getCapital() {
		return capital;
	}

	/**
	 * @param capital the capital to set
	 */
	public void setCapital(Capital capital) {
		this.capital = capital;
	}

	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}

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

	/**
	 * @return the stateName
	 */
	public String getStateName() {
		return stateName;
	}

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

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		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 (getClass() != obj.getClass()) {
			return false;
		}
		State other = (State) obj;
		if (id != other.id) {
			return false;
		}
		return true;
	}

	public String getSQLTypeName() throws SQLException {
		return "STATE_TYP";
	}

	public void readSQL(SQLInput sqlInput, String typeName) throws SQLException {
		setId(sqlInput.readInt());
		setStateName(sqlInput.readString());
		setCapital((Capital) sqlInput.readObject());
	}

	public void writeSQL(SQLOutput sqlOutput) throws SQLException {
		sqlOutput.writeInt(getId());
		sqlOutput.writeString(getStateName());
		sqlOutput.writeObject(getCapital());
	}

}

Capital.java

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

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

/**
 * @author p7164517
 * 
 */
public class Capital implements SQLData {

	private int capitalId;
	private String capitalName;

	/**
	 * @return the capitalId
	 */
	public int getCapitalId() {
		return capitalId;
	}

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

	/**
	 * @return the capitalName
	 */
	public String getCapitalName() {
		return capitalName;
	}

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

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + capitalId;
		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 (getClass() != obj.getClass()) {
			return false;
		}
		Capital other = (Capital) obj;
		if (capitalId != other.capitalId) {
			return false;
		}
		return true;
	}

	public String getSQLTypeName() throws SQLException {
		return "CAPITAL_TYP";
	}

	public void readSQL(SQLInput sqlInput, String typeName) throws SQLException {
		setCapitalId(sqlInput.readInt());
		setCapitalName(sqlInput.readString());
	}

	public void writeSQL(SQLOutput sqlOutput) throws SQLException {
		sqlOutput.writeInt(getCapitalId());
		sqlOutput.writeString(getCapitalName());
	}

}

Lets start Coding or Dao Implementation class.

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

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.simple.SimpleJdbcCall;

import com.spark.spring.data.dao.rowmappers.SqlDataStateData;
import com.spark.spring.data.domain.State;

/**
 * @author PavanKumar Mantha
 * 
 */
public class StateDaoImpl {
	SimpleJdbcCall addStateJdbcCall, getStateJdbcCall;

	public StateDaoImpl(DataSource dataSource) {
		this.addStateJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName("pkg_state")
				.withProcedureName("add_state")
				.declareParameters(
						new SqlParameter("in_state", OracleTypes.STRUCT,
								"STATE_TYP"));

		this.getStateJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName("pkg_state")
				.withProcedureName("get_state")
				.declareParameters(
						new SqlOutParameter("out_state", OracleTypes.STRUCT,
								"STATE_TYP", new SqlDataStateData()));
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void addState(final State state) {
		Map in = Collections.singletonMap("in_state", state);
		addStateJdbcCall.execute(in);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public State getState(final int state_id) {
		Map in = Collections.singletonMap("in_state_id", state_id);
		State state = getStateJdbcCall.executeObject(State.class, in);
		return state;
	}
}

Now lets code our row mapper class. here observe in State Object we get one more object called Capital(called Object Composition)

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

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

import oracle.sql.STRUCT;

import org.springframework.jdbc.core.SqlReturnType;

import com.spark.spring.data.domain.Capital;
import com.spark.spring.data.domain.State;

/**
 * @author p7164517
 *
 */
public class SqlDataStateData implements SqlReturnType{

	Object[] states;
	Object[] capitals;

	public Object getTypeValue(CallableStatement cs, int parameterIndex, int oraType,
			String typeName) throws SQLException {
		
		State state = new State();
		Capital capital = new Capital();
		
		states = ((STRUCT)cs.getObject(parameterIndex)).getAttributes();
		state.setId(((BigDecimal)states[0]).intValue());
		state.setStateName((String)states[1]);
		
		capitals = ((STRUCT)states[2]).getAttributes();
		capital.setCapitalId(((BigDecimal)capitals[0]).intValue());
		capital.setCapitalName((String)capitals[1]);
		
		state.setCapital(capital);
		return state;
	}

}

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);
	}
}

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>
	
	<bean id="stateDaoImpl" class="com.spark.spring.data.dao.StateDaoImpl">
		<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 {
			
			
			StateDaoImpl stateDaoImpl = (StateDaoImpl)SpringDataUtil.getObject("stateDaoImpl");
				
			Capital capital = new Capital();
			capital.setCapitalId(1);
			capital.setCapitalName("Austin");
			
			State state = new State();
			state.setId(1);
			state.setStateName("Texas");
			state.setCapital(capital);
			
			stateDaoImpl.addState(state);
			
			State state2 = stateDaoImpl.getState(1);
			System.out.println("State : "+state2.getStateName()+" Capital:"+state2.getCapital().getCapitalName());
			
		} 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

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