You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Brandon Goodin <br...@gmail.com> on 2006/03/30 18:46:03 UTC
Fwd: Oracle Objects in iBatis
---------- Forwarded message ----------
From: Vikash Anand <vi...@arrkgroup.com>
Date: Thu, 30 Mar 2006 14:40:41 +0530
Subject: Oracle Objects in iBatis
To: brandon.goodin@gmail.com
Cc: ashwin.tigdoli@arrkgroup.com
Dear Brandon,
We have our legacy database in oracle and the front end
of the application was Oracle forms. Now we wish to change the front end to
J2EE. We are using Spring as service layer that will call iBatis for data
exchange.
Now, this legacy database has lots of stored procedures in which some of
them returning oracle objects. We tried getting Oracle objects but I guess
they are not supported by iBatis. We tried using TypeHandlerCallback using
example from following URL: http://issues.apache.org/jira/browse/IBATIS-145
. We are new to iBatis so please help us with an example or a method to get
oracle objects.
Sending you the code snip lets.
Main.java
SqlMapClient sqlMap;
resource = "SqlMapConfig.xml";
reader = Resources.getResourceAsReader(resource);
sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);
Map map = new HashMap();
map.put("msnId","555");
sqlMap.queryForObject("storedFunc", map);
List firstList = (List)map.get("output1");
Iterator itr = firstList.iterator();
while(itr.hasNext()) {
VehicleModel vm =
(VehicleModel)itr.next();
}
SqlMapConfig.xml
<sqlMapConfig>
<settings cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true" maxRequests="32"
maxSessions="10" maxTransactions="5"
useStatementNamespaces="false" />
<typeAlias alias="vehicle2Typehandler" type="Vehicle2Typehandler" />
<typeHandler javaType="VehicleModel" callback="vehicle2Typehandler" />
<!--
<typeHandler javaType="VehicleSqlData" callback="vehicle2Typehandler" />
<typeHandler javaType="java.sql.ResultSet" callback="RefTypeHandler"/>
-->
<transactionManager type="JDBC" >
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:oracle:thin:@10.0.4.20:1521:rajeshm"/>
<property name="JDBC.Username" value="system"/>
<property name="JDBC.Password" value="manager"/>
</dataSource>
</transactionManager>
<sqlMap resource="SQLMap.xml" />
</sqlMapConfig>
SQLMap.xml
<!-- Calling the Function -->
<parameterMap id="funcInput" class="map" >
<!-- <parameter property="output1" jdbcType="ORACLECURSOR" javaType=
"java.sql.ResultSet" mode="OUT" resultMap="demo-param"/> -->
<parameter property="output1" jdbcType="VEHICLE2" typeName="VEHICLE2"
typeHandler="Vehicle2Typehandler" mode="OUT"/>
<parameter property="msnId" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="storedFunc" parameterMap="funcInput">
<!-- {? = call TMPSP.TMPSP_FUNC(?) } -->
<!-- {? = call TMPSP.TMPSP_FUNC(?) } -->
{? = call FUNC(?)}
</procedure>
Vehicle2Typehandler.java
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;
import oracle.sql.STRUCT;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
public class Vehicle2Typehandler implements TypeHandlerCallback
{
static{
JdbcTypeRegistry.setType("VEHICLE2", Types.STRUCT);
};
public void setParameter(ParameterSetter setter,
Object parameter)
throws SQLException {
VehicleModel vehicleModel[] =
(VehicleModel[])parameter;
setter.getPreparedStatement().getConnection();
Connection conn =
setter.getPreparedStatement().getConnection();
StructDescriptor structDescriptor =
StructDescriptor.createDescriptor("VEHICLE2", conn);
STRUCT valStruct;
try {
valStruct = new STRUCT(structDescriptor,
conn, vehicleModel);
} catch (SQLException e) {
throw e;
}
setter.setObject(valStruct);
}
public Object getResult(ResultGetter getter) throws
SQLException {
//Array arr = getter.getArray();
VehicleModel vm[] =
(VehicleModel[])getter.getObject();
if(vm==null)
return null;
else
//return (String[])arr.getArray();
return vm;
}
public Object valueOf(String arg0) {
String[] r = new String[1];
r[0] = arg0;
return r;
}
}
If you notice I have modified the Vehicle2Typehandler.java class from
handling ARRAY type to STRUCT type.
Not sure weather it's the right approach. Getting the following error.
Exception in thread "main"
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in SQLMap.xml.
--- The error occurred while applying a parameter map.
--- Check the funcInput.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.lang.ClassCastException: oracle.sql.STRUCT
Caused by: java.lang.ClassCastException: oracle.sql.STRUCT
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback(GeneralStatement.java:188)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForO
bject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:561)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSession
Impl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientIm
pl.java:70)
at Main.main(Main.java:31)
Caused by: java.lang.ClassCastException: oracle.sql.STRUCT
at Vehicle2Typehandler.getResult(Vehicle2Typehandler.java:66)
at
com.ibatis.sqlmap.engine.type.CustomTypeHandler.getResult(CustomTypeHandler.
java:64)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlE
xecutor.java:357)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExec
utor.java:305)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuer
y(ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback(GeneralStatement.java:173)
... 6 more
Caused by:
java.lang.ClassCastException: oracle.sql.STRUCT
at Vehicle2Typehandler.getResult(Vehicle2Typehandler.java:66)
at
com.ibatis.sqlmap.engine.type.CustomTypeHandler.getResult(CustomTypeHandler.
java:64)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlE
xecutor.java:357)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExec
utor.java:305)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuer
y(ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback(GeneralStatement.java:173)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForO
bject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:561)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSession
Impl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientIm
pl.java:70)
at Main.main(Main.java:31)
Any Help is appreciated !!!!!!!!!!!!!
Thanks and Regards,
Vikash Anand,
Developer,
Arrk Solutions Pvt. Ltd.
Mumbai, India.