Multiple resultsets SpringStoredProc and Ibatis

[yogish]

Hi ,

This mail is w.r.t multiple result sets returned from stored proc.

I am using ibatis in my persistence layer extensively, because of ibatis
doesn’t support multiple resultsets wherever my procedure returns one
resultsets I am using Ibatis but if my procedure returns multiple then I
am using Spring stored procedure where I written RowMappers class to map
result sets information to Javabean object.

My question is

1. Is this design decision right..?

2. Is there any way to implement this in best way. like..

A. Can we make use of iBatis sqlMaps definitions or any other way to map
resultsets (which are returned from spring stored proc) to Java Bean object
?

Right now I am using spring RowMappers

B. Is anybody having an idea of solving this in more abstract way. (using
ibatis+spring stored proc)


Thanks in advance
Yogish

[Spring Guy]

Hi ,

This mail is w.r.t multiple result sets returned from stored proc.

I am using ibatis in my persistence layer extensively, because of ibatis
doesn’t support multiple resultsets wherever my procedure returns one
resultsets I am using Ibatis but if my procedure returns multiple then I
am using Spring stored procedure where I written RowMappers class to map
result sets information to Javabean object.

My question is

1. Is this design decision right..?

2. Is there any way to implement this in best way. like..

A. Can we make use of iBatis sqlMaps definitions or any other way to map
resultsets (which are returned from spring stored proc) to Java Bean object
?

Right now I am using spring RowMappers

B. Is anybody having an idea of solving this in more abstract way. (using
ibatis+spring stored proc)


Thanks in advance
Yogish

I had similar situation in my last project related to REF_CURSOR or in other words, ability to return multiple resultsets.
Since iBatis was obvious choice in the beginning & due to its shortcoming, i had to rewrite my own framework/component that actually used StoredProcedure class & custom RowMappers to abstract the mundane code. I am more than willing to share the code with you. Its fairly simple & i do leverage Exception translator class as well. All of the mappings are done using custom XML schema [i know that i should have used Hibernate, but the project scope & time didn't allow me to. Client was adamant about using Stored procs & REF_CURSORS]

[yogish]

Hi,

Thanks for the reply... it will be great if you can share sample code that covers Abstraction Layer...(generic classes) that handles multiple resultsets with your custom XML schema..

and custom XML schema is specific to your framework..?

Yogish

[Spring Guy]

Hi,

Thanks for the reply... it will be great if you can share sample code that covers Abstraction Layer...(generic classes) that handles multiple resultsets with your custom XML schema..

and custom XML schema is specific to your framework..?

Yogish

The class [B]DatabaseAccessGateway[B] is the actual interface that is injected into each DAO. As you can see, it is similar to typical iBatis style interface, bunch of gets, updates/deletes.
Anyway the real implementation is the DatabaseAccessGatewayImpl which uses Spring's BeanWrapper & BeanWrapperImpl classes.
The XML schema looks something like this

<sql-procedures>
    <procedure id="getFacilities" storedProcedure="XXXX.f_get_XXX">
        <params>
            <param id="facilityResults" direction="OUT" type="CURSOR" /> 
            <param id="ID" direction="IN" type="NUMBER" /> 
            <param id="name" direction="IN" type="VARCHAR" />
            <param id="selectionKey" direction="IN" type="VARCHAR" /> -->
        </params>
        
        <beans>
            <bean type="xxxx.xxxx.domain.Facility" ref-out-param="facilityResults">
                <property-mapping>
                      <property name="facilityID" db-column="XXX_ID" /> 
                      <property name="name" db-column="XXXX_NAME" /> 
                      <!-- rest omitted -->
                </property-mapping>
            </bean>
        </beans>
    </procedure>

I will leave the exercise of parsing this xml into JavaBeans upto you. I used Digester to quickly parse all this.
The main code is basically creation of the StoredProcedure class for the corresponding <procedure tags
The inner class does this trick

public class CustomDataRowMapper extends AbstractRowBeanMapper {
		public CustomDataRowMapper(BeanInfo beanInfo) {
			super(beanInfo);
		}

		public void mapRow(BeanWrapper beanWrapper, Map propertyMappers,
				ResultSet rs, int rowNumber) throws SQLException {
			Iterator iterator = propertyMappers.entrySet().iterator();
			while (iterator.hasNext()) {
				Entry entry= (Entry) iterator.next();
				PropertyMapper propertyMapper = (PropertyMapper) entry.getValue(); 
				ObjectHandlerUtils.setPropertyValueFromResultSet(beanWrapper,
						propertyMapper, rs);
			}
		}
	}

And the AbstractRowBeanMapper, as you might have guessed, is an abstract class that implements RowMapper

protected abstract void mapRow(BeanWrapper wrapper, Map propertyMappers, ResultSet rs, int rowNumber) throws SQLException;
	
	/**
	 * Originally called by Spring, it simply creates the new bean, and delegates it to the child class
	 */
	public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
		BeanWrapper beanWrapper= ObjectHandlerUtils.getBeanWrapper(beanInfo.getType());		
		mapRow(beanWrapper, beanInfo.getPropertyMappers(), rs, rowNumber);		
		return beanWrapper.getWrappedInstance();
	}

The key is creation of the BeanWrapper class...
Everything else is just fairly simple. BTW ObjectHandlerUtils is just a simple utils class, it abstracts way all of the pain of handling exceptions.

[yogish]

Hi ,

Thanks for giving high level overview and looks good.

I am not sure what is BeanWrapper does is it like a BeanUtils class ?
can you please explain about your ObjectHandlerUtils that does copying to BeanInfo objects(javabean) from resultsets , is it uses Reflection….?


Thanks
Yogish

[Spring Guy]

Hi ,

Thanks for giving high level overview and looks good.

I am not sure what is BeanWrapper does is it like a BeanUtils class ?
can you please explain about your ObjectHandlerUtils that does copying to BeanInfo objects(javabean) from resultsets , is it uses Reflection….?


Thanks
Yogish

I prefer using BeanWrapper because it is part of Spring & easier to use, i guess. The good thing of using the BeanWrapper is i can pass the object wrapped inside it.
Anyway, It doesn't really matter what underlying class you use for this bcoz they all use reflection underneath it.
All ObjectHandlerUtils it does is call the setPropertyValue method and handle all of the exceptions that might arise from it.
Also, it checks the datatype of the property & typecasts the value come from the database to it i.e. it uses valueOf methods on the datatype wrapper objects from lang package.
I use custom type handlers, [i should be using PropertyEditors] to do all datatype conversions. A type handler factory returns the appropriate type handler for the given database type. Moreover, it also is able to convert java types to Oracle types for. e.g int to OracleTypes.INTEGER..all you need to do is register the types to the factory [by default it register all wrapper objects from the lang, but you can also register your own type such as BLOB type etc.]
Also check my post on validator framework. You can use Spring's AOP to intercept DAO /Services & validate information sent to them...for e.g.

public void doSomeUpdate(SomeDomainObject object) { 
      //call persistence layer to save this object
}

In the above code you might want to validate some business rules before the method calls the persistence layer, but you don't want to hardcode it in their.
I uses AOP to intercept this call & validate the object before the method is executed.