 |
iBATIS DataMapper version 2.2.0.638
This FAQ is based on iBATIS DataMapper (Java) 2.2.0.638
|
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.
iBatis2.2.0之后，开始支持Oracle的Cursor，不需要依赖于CTH。
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"));
}
}
That's all folks.