ibatis procedure

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

...http://huodong.duomi.com/music_303029713_Listen.html

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

 http://huodong.duomi.com/music_303029713_Listen.html

http://www.apachebookstore.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions