Oracle Objects support in iBatis using TypeHandler
--------------------------------------------------
Key: IBATIS-283
URL: http://issues.apache.org/jira/browse/IBATIS-283
Project: iBatis for Java
Type: Improvement
Components: SQL Maps
Environment: JBoss, Oracle 9i
Reporter: Ashwin
Priority: Blocker
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" />
<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="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 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.executeQueryWithCallback(GeneralStatement.java:188)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.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(SqlExecutor.java:357)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(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(SqlExecutor.java:357)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
at Main.main(Main.java:31)
Any Help is appreciated !!!!!!!!!!!!!
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
