귓가에 햇살을 받으며 석양까지 행복한 여행을...: iBATIS DataMapper

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

...

귓가에 햇살을 받으며 석양까지 행복한 여행을...

Thursday, August 27, 2009

iBATIS DataMapper - Oracle REF CURSOR Solutions

Current Working Solutions

Solution 1

Solution 2

Improving the Solutions

Improvement 1

Improvement 2

New Solutions

New 1

New 2

No comments:

About Me

Links