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 🙂

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