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,
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:
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