Spring SQLReturnType and varray

As a avid fan of Hibernate scheme of things i have been saved from JDBC junk/complexities for most part of my life as a programmer. As i say most of the times your biggest fears become reality sooner or later. So 5 yrs. down the i had to get out of the comfort of Hibernate and dig deep into JDBC calls for Multilevel collections.

Here i am talking about collections of collections (Varrays,Nested tables) in oracle.

Spring JDBC provide perfect foil to access these complex datatypes to java objects. Lets see how :

Remember the following checklist, this is must:

1.SqlReturnType : Interface to be implemented for retrieving values for more complex database specific types not supported by the standard getObject method. (Spring docs)

2.SQLData
The interface used for the custom mapping of an SQL user-defined type (UDT) to a class in the Java programming language. The class object for a class implementing the SQLData interface will be entered in the appropriate Connection object's type map along with the SQL name of the UDT for which it is a custom mapping.

3.http://forum.springsource.org/archive/index.php/t-10042.html (Thomas Risberg comments)

4.Mentions iNput and Output paramters in your StoredProcedureClass constructor.

public TestStoredProcedureDao(DataSource dataSource, String storedProcedureName)
{
super(dataSource, storedProcedureName);
declareParameter(new SqlParameter(INPUTPARAMETERID, Types.VARCHAR)); declareParameter(new SqlParameter(INPUTPARAMETERSETNAME, Types.VARCHAR));
declareParameter(new SqlParameter(INPUTPARAMETERDATEFROM, Types.VARCHAR));
declareParameter(new SqlParameter(INPUTPARAMETERDATETO, Types.VARCHAR));
declareParameter(new SqlOutParameter("extra",
OracleTypes.ARRAY,"EXTRA",new ExtraMapper()));
declareParameter(new SqlOutParameter("FROMDATE",Types.VARCHAR));
declareParameter(new SqlOutParameter("TODATE",Types.VARCHAR));
compile();
}

5.Suppose the structure of the EXTRA varray is as below:
Make sure all corresponding beans PARENT,CHILDINFO,CHILD1,CHILD2,CHILD3 implements SQLData and implement SQLData.readSQL and SQLData.writeSQL methods as :

public class CHILD1 implements SQLData
{

private String key;
private BigDecimal value;
private String sql_type = "CHILD1_TYPE";

public String getKey()
{
return Key;
}

public void setKey(String key)
{
Key = key;
}

public BigDecimal getValue()
{
return Value;
}

public void setValue(BigDecimal value)
{
Value = value;
}

public String getSql_type() {
return sql_type;
}

public void setSql_type(String sql_type) {
this.sql_type = sql_type;
}

public String getSQLTypeName() throws SQLException {
return getSql_type();
}

public void readSQL(SQLInput stream, String typeName) throws SQLException {
Key = stream.readString();
Value = stream.readBigDecimal();
}
public void writeSQL(SQLOutput arg0) throws SQLException {
}

}

Remember ExtraMapper class in the StoredProcedureClass mentioned above. It needs to implement SqlReturnType and use con.getTypeMap() to actually call for Driver methods implementation to map the DataBase datatypes to java objects.

private static Logger log = Logger.getLogger(ExtraMapper.class);

public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType,
String typeName) throws SQLException {

Connection con = cs.getConnection();
ArrayList extras = null;

Array array = cs.getArray(5);
ResultSet rs = array.getResultSet();
Map typeMap = con.getTypeMap();

try
{
typeMap.put("EXTRA_TYPE",Class.forName("com.ExtraBean"));
typeMap.put("PARENT_TYPE",Class.forName("com.ParentBean"));
typeMap.put("CHILD_TYPE",Class.forName("com.ChildBean"));
typeMap.put("CHILD1_TYPE",Class.forName("com.Child1Bean"));
typeMap.put("CHILD2_TYPE",Class.forName("com.Child2Bean"));

extras = new ArrayList();
while(rs.next())
{
ExtraBean tr = (ExtraBean)rs.getObject(2,typeMap);
if(null != tr)
extras.add(tr);
}
}
catch(ClassNotFoundException cnf)
{
log.info("Error"+cnf.toString());
}
return extras;
}

It works like a charm.

--
Pankaj Chaswal