Ibatis 中怎样使用 Oracle Ref Cursor

Release 2.2.0 of iBATIS DataMapper natively supports Oracle REF Cursors - without the need to create a custom type handler. This FAQ shows a very small example on how to use it.

We start with the (Oracle) database related stuff. For this example we've created an Oracle user ibatis. In SQL*Plus (or whichever tool you use) log in as this user and run the commands shown below.

CREATE TABLE REFS (
  ID   NUMBER       NOT NULL PRIMARY KEY
, NAME VARCHAR2(50) NOT NULL
);

CREATE OR REPLACE PACKAGE REFS_PCK AS
  TYPE      REF_CURSOR_T IS REF CURSOR;
  FUNCTION  GET_REFS RETURN REF_CURSOR_T;
END REFS_PCK;
/

CREATE OR REPLACE PACKAGE BODY REFS_PCK IS
  FUNCTION GET_REFS RETURN REF_CURSOR_T
  IS
    L_CURSOR REF_CURSOR_T;
  BEGIN
    OPEN L_CURSOR FOR SELECT * FROM REFS;
    RETURN L_CURSOR;
  END GET_REFS;
END REFS_PCK;
/

insert into refs values(1,'Jan');
insert into refs values(2,'Danielle');
insert into refs values(3,'Tessa');

We create a simple Java bean class to hold REFS' records.

package com.cumquatit.examples.ibatis.refs;

public class Ref ...{
    private int id;

    private String name;

    public int getId() ...{
    return id;
    }

    public void setId(int id) ...{
    this.id = id;
    }

    public String getName() ...{
    return name;
    }

    public void setName(String name) ...{
    this.name = name;
    }

    public String toString() ...{
    return ("id=" + id + ", name=" + name);
    }
}

Next create a mapping file for the REFS table.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>

    <typeAlias alias="Ref" type="com.cumquatit.examples.ibatis.refs.Ref" />
    
    <resultMap class="Ref" id="ref-mapping">
        <result property="id" column="ID" />
        <result property="name" column="NAME" />
    </resultMap>
    
    <parameterMap id="output" class="map">
        <parameter property="o" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="ref-mapping" />
    </parameterMap>
    
    <procedure id="getRefs" parameterMap="output">{ ? = call refs_pck.get_refs }</procedure>
    
</sqlMap>

Also we need a sqlMapConfig file. Below an example of this file.

<?xml version="1.0"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver" />
            <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@//flash.cumquat.office:1524/neon" />
            <property name="JDBC.Username" value="ibatis" />
            <property name="JDBC.Password" value="ibatis" />
        </dataSource>
    </transactionManager>
    <sqlMap resource="com/cumquatit/examples/ibatis/refs/Ref.xml" />
</sqlMapConfig>

And to finish up, a small tester program.

package com.cumquatit.examples.ibatis.refs;

import java.io.Reader;
import java.util.HashMap;
import java.util.Map;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class RefTester ...{
    public static void main(String[] args) throws Exception ...{
    String resource;
    Reader reader;
    SqlMapClient sqlMap;
    resource = "com/cumquatit/examples/ibatis/refs/SqlMapConfig.xml";
    reader = Resources.getResourceAsReader(resource);
    sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
    Map map = new HashMap();
    sqlMap.queryForObject("getRefs", map);
    System.out.println(map.get("o"));
    }
}