iBATIS DataMapper
Added by Clinton Begin, last edited by ifnu on Jul 13, 2009  (view change)
Labels: 
(None)


This whiteboard is for the collaborative documentation and design as a start toward improving or extending current support for Oracle REF CURSOR types.

Current Working Solutions

If you have a current solution that works, please put it here.

Solution 1

This is sample mapping for a Stored procedure which return Ref cursor in it's out parameter

<?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
   
   <sqlMap namespace="KOMUNIKA_REPORT">
   <resultMap id="BaseResultMap" class="javaapplication4.StockAreaAndWarehouse" >
   <result column="PRODUCT_CODE" property="productCode"  />
   <result column="PRODUCT_NAME" property="productName" />
   <result column="INCOMING" property="incoming" />
   <result column="UNIT_SOLD" property="unitSold" />
   <result column="TOTAL_STOCK" property="totalStock" />
   </resultMap>
   <parameterMap id="resultMap" class="java.util.Map">
   <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
   </parameterMap>
   <procedure id="selectStockAreaAndWarehouse"
   parameterMap="resultMap"
   resultMap="BaseResultMap"
   >
   { call KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(?) }
   </procedure>
   </sqlMap>

You should aware that javaType="java.sql.ResultSet" attribute must set to be able to map result to a Class, if it not set than your effort is a waste (like mine )

Oracle's PL/SQL Stored procedure

CREATE OR REPLACE PROCEDURE KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(
   RESULT_CURSOR out TYPES.ref_cursor) as
   BEGIN
   OPEN RESULT_CURSOR FOR select PRODUCT_CODE, PRODUCT_NAME, INCOMING, UNIT_SOLD, TOTAL_STOCK
   from KOMUNIKA.STOCK_AREA_AND_WAREHOUSE ;
   END LP_STOCK_AREA_WAREHOUSE;

To make that PL/SQL work you should declare a package so that TYPES.ref_cursor recognized by oracle

CREATE OR REPLACE PACKAGE KOMUNIKA.Types
   AS
   TYPE ref_cursor IS REF CURSOR;
   END;

To test wether it works or not you need sqlMapConfig

<?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
   <sqlMapConfig>
   <settings useStatementNamespaces="true" classInfoCacheEnabled="true" />
   <transactionManager type="JDBC" >
   <dataSource type="SIMPLE">
   <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
   <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
   <property name="JDBC.Username" value="YOUUSERHERE"/>
   <property name="JDBC.Password" value="YOURPASSWORDHERE"/>
   <property name="JDBC.DefaultAutoCommit" value="true" />
   </dataSource>
   </transactionManager>
   <sqlMap resource="javaapplication4/KOMUNIKA_REPORT_SqlMap.xml" />
   </sqlMapConfig>

oh, one more setting which is realy important is useStatementNamespaces="true", i find it out in a hardway when i want to use namespace

At last here is Java code used to test the configuration. This java code derived from improvement solution below,

Main.java
package javaapplication4;
   
   import com.ibatis.common.resources.Resources;
   import com.ibatis.sqlmap.client.SqlMapClient;
   import com.ibatis.sqlmap.client.SqlMapClientBuilder;
   import java.io.Reader;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   /**
   *
   * @author ifnu<mailto:ifnubima@gmail.com>
   */
   public class Main {
   public static void main(String[] args) throws Exception {
   String resource;
   Reader reader;
   List list;
   SqlMapClient sqlMap;
   resource = "javaapplication4/ibatis.xml";
   reader = Resources.getResourceAsReader (resource);
   sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
   Map map = new HashMap();
   // use queryForList because the procedure map defines a resultmap
           // for the statement
           list = sqlMap.queryForList("KOMUNIKA_REPORT.selectStockAreaAndWarehouse", map);
   System.out.println(list);
   }
   }

you can download the code above : http://opensource.atlassian.com/confluence/oss/download/attachments/561/iBatisCallSpRefCursorOracle.zip

Solution 2

...

Improving the Solutions

If you have an idea to improve one of the solutions above, but it requires a change to the iBATIS framework, put it here.

Improvement 1

I have modified the framework to allow all oracle cursors to be mapped by a statement's resultMap so you can call the queryForList() method and have the oracle cursor results returned in the list. I also added the ability to specifiy a resultMap to use with each of the oracle cursor parameters to support the queryForObject() call. I have tested this code with IBATIS 2.1.6.598

here are my sample sqlMap:

<?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
   
   <sqlMap>
   
   <typeAlias alias="Employee" type="test.Employee" />
   
   <resultMap id="employee-map" class="Employee">
   <result property="name" column="ENAME" />
   <result property="employeeNumber" column="EMPNO" />
   <result property="departmentNumber" column="DEPTNO" />
   </resultMap>
   
   <parameterMap id="single-rs" class="map" >
   <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
   <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
   </parameterMap>
   
   <procedure id="GetSingleEmpRs" parameterMap="single-rs" resultMap="employee-map">
   { call scott.example.GetSingleEmpRS(?, ?) }
   </procedure>
   
   <parameterMap id="double-rs" class="map" >
   <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
   <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />
   <parameter property="output2" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />
   </parameterMap>
   
   <procedure id="GetDoubleEmpRs" parameterMap="double-rs" >
   { call scott.example.GetDoubleEmpRS(?, ?, ?) }
   </procedure>
   </sqlMap>

Here is my sample code that makes use of the maps:

Main.java
package test;
   import java.io.Reader;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   import com.ibatis.common.resources.Resources;
   import com.ibatis.sqlmap.client.SqlMapClient;
   import com.ibatis.sqlmap.client.SqlMapClientBuilder;
   
   public class Main {
   
   
   public static void main(String arg[]) throws Exception {
   String resource;
   Reader reader;
   List list;
   SqlMapClient sqlMap;
   resource = "test/SqlMapConfig.xml";
   reader = Resources.getResourceAsReader (resource);
   sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
   Map map = new HashMap();
   map.put("in1", new Integer(10));
   // use queryForList because the procedure map defines a resultmap
   	        // for the statement
   	        list = sqlMap.queryForList("GetSingleEmpRs", map);
   
   System.out.println("--------------------");
   System.out.println( list );
   System.out.println("--------------------");
   
   map = new HashMap();
   map.put("in1", new Integer(10));
   // use queryForObject because the procedure map does not define a
   	        // result map for the statement
   	        sqlMap.queryForObject("GetDoubleEmpRs", map);
   
   System.out.println("--------------------");
   System.out.println( map.get("output1"));
   System.out.println( map.get("output2"));
   System.out.println("--------------------");
   
   
   }
   }

Here is the output from Main.java

   --------------------
   [Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
   --------------------
   --------------------
   [Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
   [Employee[name=ADAMS,id=7876,dept=20], Employee[name=ALLEN,id=7499,dept=30], Employee[name=BLAKE,id=7698,dept=30],
   Employee[name=FORD,id=7902,dept=20], Employee[name=JAMES,id=7900,dept=30], Employee[name=JONES,id=7566,dept=20],
   Employee[name=MARTIN,id=7654,dept=30], Employee[name=SCOTT,id=7788,dept=20], Employee[name=SMITH,id=7369,dept=20],
   Employee[name=TURNER,id=7844,dept=30],Employee[name=WARD,id=7521,dept=30]]
   --------------------
   

All test code and modified src are included in this page's attachments. Most of my PL/SQL code just returns a single ref cursor so it is nice just to define a resultMap for the statement and just have it work automatically with a queryForList() call. The second form handles the more complicated case of returning two or more cursors. I have tested this code with oracle 9i using the thin driver (ojdbc14.jar) with jdk 1.4

Warning

This implementation does not support

  • Paging functionality
  • Remappable results for parameter assigned resultMaps

Improvement 2

...

New Solutions

If you have a completely new solution that can only work by changing or extending the iBATIS framework, please put it here.

New 1

...

New 2

...

who has the solution?

Posted by normanding at Jun 29, 2005 22:47

I noticed that the jdbcType attribute can be set to ORACLECURSOR. An undocumented feature. Unfortunately I still had to make modification that basically mean I've got an Oracle only solution.

It would be nice to somehow use a special type handler that would utilise the resultMaps definitions. What are the chances???

Posted by Anonymous at Jul 12, 2005 10:15

I've added a sample to the wiki How do I use an Oracle Ref Cursor?. This shows how you can work with Oracle REF cursor and iBatis 2.2.0. Doesn't iBatis 2.2.0 make the quest for an Oracle Ref Cursor solution obsolete?

Posted by Jan Vissers at Sep 20, 2006 03:45

Hi:

When I try as in the above shown example, The list I am getting back is always of size 10 and all the elements are null. Would you know where I am going wrong? The sqlconfig is as below:

<resultMap id="planRefmapping" class="com.fmr.ficl.frs.weblinks.admin.DTO.Plan" >
<result property="planNumber" column="PLANNO" />
<result property="empName" column="ENAME" />
</resultMap>

<parameterMap id="getAllPlanParameters" class="map" >
<parameter property="cClnt" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="curPlan" jdbcType="ORACLECURSOR" mode="OUT" />
</parameterMap>

<procedure id="getAllPlan" parameterMap="getAllPlanParameters" resultMap="planRefmapping">

Unknown macro: { call CPASDBA.WebLinks.CWSWLGetAllPlan(?,?) }

</procedure>

Can you please help me solve this?

Thanks

Posted by Mahitha Krishnan at Dec 13, 2006 15:03

It doesn't seem that iBatis.Net 1.6.1 or iBatis.Net 1.6.2 (Beta) has this functionality. If I'm wrong could someone please let me know. Thanks!

Posted by Denham at Jan 21, 2009 05:21; last updated at Jan 21, 2009 05:34

Previous Edit will not work if you tried to map result to a class. You should add javaType="java.sql.ResultSet" attribute in resultMap's property for ORACLECURSOR.

refer to this email thread :

http://www.nabble.com/Oracle-ref-cursors-td2858035.html#a2858035

Posted by ifnu at Jul 13, 2009 14:11
Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators