LINQ to SQL in c# .Net

Hello folks, for a change in the week end i started reading .Net, as a programmer i should appreciate both the legendary languages(Java, .Net) some have i was attracted to the beauty of one of the .Net’s feature called LINQ.
so here is a small post where i will demonstrate how to link up our front end with database using LINQ(Language Integrated Query). This is my simple attempt to write a .Net post(apologies if anything is wrong and please give your precious feedback)

prior to create our application let us create the database table as follows

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Details]    Script Date: 04/27/2014 20:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Details](
	[id] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Age] [int] NULL,
 CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

after creating the database from the visual designer of the VisualStudio please create the following screen.
FrontEnd

inside the “CSharp” class behind this UI double click the submit button it will create the following method

private void SaveDetailsButton_Click(object sender, EventArgs e){
    // code to save the details and retrieve the details using LINQ
}

now its time to create the LINQ class inside the project create a file called “LINQ to SQL” and drag and drop the table in this file and it looks as below.
dbml_file

Once this is done the VisualStudio automatically create the following code


//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.4984
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace SampleApplication
{
	using System.Data.Linq;
	using System.Data.Linq.Mapping;
	using System.Data;
	using System.Collections.Generic;
	using System.Reflection;
	using System.Linq;
	using System.Linq.Expressions;
	using System.ComponentModel;
	using System;
	
	
	[System.Data.Linq.Mapping.DatabaseAttribute(Name="TestDatabase")]
	public partial class DetailsClassDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
		
    #region Extensibility Method Definitions
    partial void OnCreated();
    partial void InsertDetail(Detail instance);
    partial void UpdateDetail(Detail instance);
    partial void DeleteDetail(Detail instance);
    #endregion
		
		public DetailsClassDataContext() : 
				base(global::SampleApplication.Properties.Settings.Default.TestDatabaseConnectionString, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public System.Data.Linq.Table<Detail> Details
		{
			get
			{
				return this.GetTable<Detail>();
			}
		}
	}
	
	[Table(Name="dbo.Details")]
	public partial class Detail : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private int _id;
		
		private string _FirstName;
		
		private string _LastName;
		
		private System.Nullable<int> _Age;
		
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnidChanging(int value);
    partial void OnidChanged();
    partial void OnFirstNameChanging(string value);
    partial void OnFirstNameChanged();
    partial void OnLastNameChanging(string value);
    partial void OnLastNameChanged();
    partial void OnAgeChanging(System.Nullable<int> value);
    partial void OnAgeChanged();
    #endregion
		
		public Detail()
		{
			OnCreated();
		}
		
		[Column(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]
		public int id
		{
			get
			{
				return this._id;
			}
			set
			{
				if ((this._id != value))
				{
					this.OnidChanging(value);
					this.SendPropertyChanging();
					this._id = value;
					this.SendPropertyChanged("id");
					this.OnidChanged();
				}
			}
		}
		
		[Column(Storage="_FirstName", DbType="VarChar(50)")]
		public string FirstName
		{
			get
			{
				return this._FirstName;
			}
			set
			{
				if ((this._FirstName != value))
				{
					this.OnFirstNameChanging(value);
					this.SendPropertyChanging();
					this._FirstName = value;
					this.SendPropertyChanged("FirstName");
					this.OnFirstNameChanged();
				}
			}
		}
		
		[Column(Storage="_LastName", DbType="VarChar(50)")]
		public string LastName
		{
			get
			{
				return this._LastName;
			}
			set
			{
				if ((this._LastName != value))
				{
					this.OnLastNameChanging(value);
					this.SendPropertyChanging();
					this._LastName = value;
					this.SendPropertyChanged("LastName");
					this.OnLastNameChanged();
				}
			}
		}
		
		[Column(Storage="_Age", DbType="Int")]
		public System.Nullable<int> Age
		{
			get
			{
				return this._Age;
			}
			set
			{
				if ((this._Age != value))
				{
					this.OnAgeChanging(value);
					this.SendPropertyChanging();
					this._Age = value;
					this.SendPropertyChanged("Age");
					this.OnAgeChanged();
				}
			}
		}
		
		public event PropertyChangingEventHandler PropertyChanging;
		
		public event PropertyChangedEventHandler PropertyChanged;
		
		protected virtual void SendPropertyChanging()
		{
			if ((this.PropertyChanging != null))
			{
				this.PropertyChanging(this, emptyChangingEventArgs);
			}
		}
		
		protected virtual void SendPropertyChanged(String propertyName)
		{
			if ((this.PropertyChanged != null))
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
			}
		}
	}
}

now copy and paste the following code snippet into the button handling method as shown below.

 DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();

            Detail detail = new Detail();
            detail.id = Int32.Parse(IdtextBox.Text.ToString());
            detail.FirstName = firstNametextBox.Text.ToString();
            detail.LastName = lastNametextBox.Text.ToString();
            detail.Age = Int32.Parse(agetextBox.Text.ToString());
            try
            {
                detailsClassDataContext.Details.InsertOnSubmit(detail);
                detailsClassDataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

           
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;

Also in the Form_load method please write the following code as below. this helps to query the database at the application load time and populate the DataGrid.

     private void Form1_Load(object sender, EventArgs e)
        {
            DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;
        }

Finally aour application is ready to use and this looks as below.
FinalScreen

Happy Coding in LINQ to SQL with C# ๐Ÿ™‚

Integrating Spring Data with MongoDB

Introduction to MongoDB
Overview

MongoDB is a document database that provides high performance, high availability, and easy scalability.

Document Database
Documents (objects) map nicely to programming language data types.
Embedded documents and arrays reduce need for joins.
Dynamic schema makes polymorphism easier.
High Performance
Embedding makes reads and writes fast.
Indexes can include keys from embedded documents and arrays.
Optional streaming writes (no acknowledgments).
High Availability
Replicated servers with automatic master failover.
Easy Scalability
Automatic sharding distributes collection data across machines.
Eventually-consistent reads can be distributed over replicated servers.
Advanced Operations
With MongoDB Management Service (MMS) MongoDB supports a complete backup solution and full deployment monitoring.

MongoDB Data Model

A MongoDB deployment hosts a number of databases. A database holds a set of collections. A collection holds a set of documents. A document is a set of key-value pairs. Documents have dynamic schema. Dynamic schema means that documents in the same collection do not need to have the same set of fields or structure, and common fields in a collectionโ€™s documents may hold different types of data.

This Tutorial assumes that you have mongoDB installed and db server running successfully, also tutorial is developed using Maven structure
Now let us see how we can connect Spring Data with MongoDB. first of all we shall go and create our document model object (simply POJO as show below)

/**
 * 
 */
package com.spark.spring.data.document.obj;

import org.springframework.data.annotation.Id;

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

	@Id
    private String id;
    private String name;
     
    /**
     * @return
     */
    public String getId() {
        return id;
    }
    /**
     * @param id
     */
    public void setId(String id) {
        this.id = id;
    }
    /**
     * @return
     */
    public String getName() {
        return name;
    }
    /**
     * @param name
     */
    public void setName(String name) {
        this.name = name;
    }
}

Now lets code our Service class that will interact with MongoDB using SpringFrame work class MongoTemplate. as shown below.

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

import java.util.List;

import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;

import com.spark.spring.data.document.obj.Person;

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

	private MongoTemplate mongoTemplate;
	private static final String COLLECTION_NAME = "person";

	public void setMongoTemplateObject(MongoTemplate mongoTemplate) {
		this.mongoTemplate = mongoTemplate;
	}

	public void addPerson(Person person) {
		try {
			if (!mongoTemplate.collectionExists(Person.class)) {
				mongoTemplate.createCollection(Person.class);
			}
			mongoTemplate.insert(person, COLLECTION_NAME);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<Person> listPerson() {
		return mongoTemplate.findAll(Person.class, COLLECTION_NAME);
	}

	public void deletePerson(Person person) {
		mongoTemplate.remove(person, COLLECTION_NAME);
	}
	
	public Person findPersonByName(String name){
		//BasicQuery query = new BasicQuery("{Name:'"+name+"'}");
		
		Query query = new Query();
		query.addCriteria(Criteria.where("Name").is(name));
		
		Person person = mongoTemplate.findOne(query, Person.class);
		return person;
	}

	public void updatePerson(Person person) {
		mongoTemplate.insert(person, COLLECTION_NAME);
	}
}

let us spend little time here, its worth seeing “findPersonByName(String name)” method in the above class. it has implementation of two different querying styles
1. using BasicQuery class which directly take a json style parameter
2. using the Criteria class and specifying the criteria.
Both these class are implemeted in SpringData framework.

Once our service class is ready let us look at our helper class (SpringDataUtil.java) which will actually helps in loading config file getting objects of specific classes etc

/**
 * 
 */
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;
import org.springframework.data.mongodb.core.MongoTemplate;

/**
 * @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);
	}
	
	public static MongoTemplate getMongoTemplate(){
		return (MongoTemplate) getContext().getBean("mongoTemplate");
	}
	
}

Now lets take look at application-context.xml file 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>

	<context:component-scan base-package="com.spark.spring.data" />

	<!-- Factory bean that creates the Mongo instance -->
	<bean id="mongo" class="org.springframework.data.mongodb.core.MongoFactoryBean">
		<property name="host" value="localhost" />
	</bean>

	<!-- MongoTemplate for connecting and quering the documents in the database -->
	<bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
		<constructor-arg name="mongo" ref="mongo" />
		<constructor-arg name="databaseName" value="test" />
	</bean>
	
	<bean id="personService" class="com.spark.spring.data.service.PersonService"></bean>
</beans>

Finally lets write our Test class(main) to test the service methods

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

import java.util.UUID;

import org.springframework.data.mongodb.core.MongoTemplate;

import com.spark.spring.data.document.obj.Person;
import com.spark.spring.data.service.PersonService;
import com.spark.spring.data.util.SpringDataUtil;

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

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Person person = new Person();
		final String uuID = UUID.randomUUID().toString();

		person.setId(uuID);
		person.setName(uuID + " PavanKumar");

		MongoTemplate mongoTemplate = SpringDataUtil.getMongoTemplate();
		PersonService personService = (PersonService) SpringDataUtil
				.getObject("personService");

		personService.setMongoTemplateObject(mongoTemplate);
		//personService.addPerson(person);

		/*for (Person p : personService.listPerson()) {
			System.out.println("ID: " + p.getId() + " Name:" + p.getName());
		}*/
		
		System.out.println(personService.findPersonByName("053f724f-f4bf-477a-9b1a-f03947f161bc PavanKumar").getId());
		
	}
}

uncomment to check different methods in above code.
finally our POM.xml is as follows.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.spark.springdata.mongodb</groupId>
	<artifactId>SpringDataMongoDB</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringDataMongoDB</name>
	<description>SpringDataMongoDB</description>
	<properties>
		<spring-version>3.2.8.RELEASE</spring-version>
	</properties>

	<!-- Spring framework -->

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring-version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring-version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${spring-version}</version>
		</dependency>

		<!-- mongodb java driver -->
		<dependency>
			<groupId>org.mongodb</groupId>
			<artifactId>mongo-java-driver</artifactId>
			<version>2.11.0</version>
		</dependency>

		<!-- Spring data mongodb -->
		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-mongodb</artifactId>
			<version>1.2.0.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>cglib</groupId>
			<artifactId>cglib</artifactId>
			<version>2.2.2</version>
		</dependency>

		<!-- oracle and mysql -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc14</artifactId>
			<version>14.0.0</version>
		</dependency>
	</dependencies>

</project>

finally the project structure should look as below.
Note:jdbc.properties in the image shown is not needed.
mongodb_project

Happy MongoDB with SpringData ๐Ÿ™‚

Handling Oracle Ref Cursors with Complex Objects in Spring

this write up is very simple if you already followed my previous two topics. In this we shall create a ref cursor which will return group of complex Objects. so first lets have a look at our database part as below.

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);
  <b>TYPE CUR_DATA IS REF CURSOR;
  procedure get_all_states(out_state OUT CUR_DATA);</b>
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;
  
  <b>procedure get_all_states(out_state out CUR_DATA) is
  begin
    OPEN out_state FOR
    SELECT id,state
    FROM states
    ORDER BY id asc;
  end get_all_states;</b>
  
end pkg_state;

Now we shall start coding our DataAccess Layer Implementation as shown below.

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

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
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.GetAllStatesRowMapper;
import com.spark.spring.data.dao.rowmappers.SqlDataStateData;
import com.spark.spring.data.domain.State;

/**
 * @author PavanKumar Mantha
 * 
 */
public class StateDaoImpl {
	SimpleJdbcCall getAllStatesJdbcCall;

	public StateDaoImpl(DataSource dataSource) {

		this.getAllStatesJdbcCall = new SimpleJdbcCall(dataSource)
				.withCatalogName("pkg_state")
				.withProcedureName("get_all_states")
				.declareParameters(
						new SqlOutParameter("out_state", OracleTypes.CURSOR,
								new GetAllStatesRowMapper()));
	}
	
	public List<State> getAllStates(){
		getAllStatesJdbcCall.execute();
		Map<Integer, Object> states = GetAllStatesRowMapper.getObjects();
		List<State> list = null;
		try {
			list = GetAllStatesRowMapper.getAllStates(states);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}

In the above code we have used a row mapper class which will fetch complex objects from the ref cursor and converts them to java domain objects
for domain objects please refer to my previous write up

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

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import oracle.sql.STRUCT;

import org.springframework.jdbc.core.RowCallbackHandler;

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

/**
 * @author PavanKumar Mantha
 *
 */
public class GetAllStatesRowMapper implements RowCallbackHandler {

	static Map<Integer, Object> states = new HashMap<Integer, Object>();

	public void processRow(ResultSet rs) throws SQLException {
		states.put(((BigDecimal)rs.getObject(1)).intValue(), rs.getObject(2));
	}

	public static Map<Integer, Object> getObjects(){
		return states;
	}
	
	/**
	 * @param states
	 * @return
	 * @throws SQLException
	 */
	public static List<State> getAllStates(Map<Integer, Object> states) throws SQLException{
		
		List<State> listStates = new ArrayList<State>();
		Object[] stateObjects;
		Object[] capitals;
		
		Set<Integer> keys = states.keySet();
		Iterator<Integer> it = keys.iterator();
		while(it.hasNext()){
			
			State state = new State();
			Capital capital = new Capital();
			
			stateObjects = ((STRUCT)states.get(it.next())).getAttributes();
			state.setId(((BigDecimal)stateObjects[0]).intValue());
			state.setStateName((String)stateObjects[1]);
			
			capitals = ((STRUCT)stateObjects[2]).getAttributes();
			capital.setCapitalId(((BigDecimal)capitals[0]).intValue());
			capital.setCapitalName((String)capitals[1]);
			
			state.setCapital(capital);
			listStates.add(state);
		}
		return listStates;
	}
}

Once the implementation of DAO and RowMapper classes are done, its time for us to test with the main class as below.

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

import java.util.List;

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");	
			
			 List<State> states = stateDaoImpl.getAllStates();
			 for (State s: states) {
				System.out.println(s.getStateName()+" : s.getCapital().getCapitalName());
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

For config files like application-context.xml and jdbc.properties please refer to my previous post and copy the same files to work with

Thats it with the simple above code we can handle N number of complex objects that gets returned from ref cursor.

Happy Oracle Collections and Spring ๐Ÿ™‚

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 ๐Ÿ™‚

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 ๐Ÿ™‚