Ibatis调用Oracle存储过程，以及返回Cursor结果集的处理

Ibatis配置文件ibatis.xml：
<resultMap id="result-map" class="com.xxx.xxx.Table">
<result property="rsId" column="id" />
<!--在Java对象跟Oracle列之间转换 -->
</resultMap>
<parameterMap id="parameter-map" class="java.util.HashMap" >
<parameter property="param1" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="param2" jdbcType="Integer" javaType="java.lang.Integer" mode="IN"/>
<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>
<procedure id="testCur" resultMap="result-map" parameterMap="parameter-map">
{call testProcedures(?, ?, ?)}
</procedure>
再看java里面调用：
Map paramMap = new HashMap();
String str = request.getParameter(.....);
int n = request.getParameter(.....);
paramMap.put("param1",str);
paramMap.put("param2",n);
List detailList = null;
try{
detailList = getSqlMapClientTemplate().queryForList("testCur", paramMap);
//可以用getSqlMapClientTemplate().queryForObject("testCur", paramMap); 返回值是Object。
}catch(Exception ex){
ex.printStackTrace(); return detailList;
detailList就是需要的结果集
================================================================
在实际的工作和学习中，我们可以通过Oracle数据库提供的REF CURSOR功能实现在程序间传递结果集的功能，另外，利用REF CURSOR可以同时实现BULK SQL，以此提高SQL的性能。

首先，我们需要使用scott用户的emp表实现以下测试：
SQL> desc emp
Name Null? Type
-------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

最后使用ref cursor获得结果集输出：

SQL> set serveroutput on
SQL> DECLARE
2 TYPE mytable IS TABLE OF emp%ROWTYPE;
3 l_data mytable;
4 l_refc sys_refcursor;
5 BEGIN
6 OPEN l_refc FOR
7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
8
9 FETCH l_refc BULK COLLECT INTO l_data;
10
11 CLOSE l_refc;
12
13 FOR i IN 1 .. l_data.COUNT
14 LOOP
15 DBMS_OUTPUT.put_line ( l_data (i).ename
16 || ' was hired since '
17 || l_data (i).hiredate
18 );
19 END LOOP;
20 END;
21 /
SMITH was hired since 17-DEC-80
ALLEN was hired since 20-FEB-81
WARD was hired since 22-FEB-81
JONES was hired since 02-APR-81
MARTIN was hired since 28-SEP-81
BLAKE was hired since 01-MAY-81
CLARK was hired since 09-JUN-81
SCOTT was hired since 19-APR-87
KING was hired since 17-NOV-81
TURNER was hired since 08-SEP-81
ADAMS was hired since 23-MAY-87
JAMES was hired since 03-DEC-81
FORD was hired since 03-DEC-81
MILLER was hired since 23-JAN-82

PL/SQL procedure successfully completed.



=====================================================================
iBatis 调用 存储过程返回结果集
数据表： t_userview plaincopy to clipboardprint?</pre><pre class="html" name="code">CREATE TABLE t_user
(
id serial NOT NULL,
login_name character varying(20) NOT NULL,
login_passwd character varying(20),
name character varying(20) NOT NULL,
sex smallint NOT NULL DEFAULT 1,
phone character varying(10)[],
privilege integer[],
CONSTRAINT t_user_pkey PRIMARY KEY (id)
)
</pre><pre class="html" name="code">CREATE TABLE t_user
(
id serial NOT NULL,
login_name character varying(20) NOT NULL,
login_passwd character varying(20),
name character varying(20) NOT NULL,
sex smallint NOT NULL DEFAULT 1,
phone character varying(10)[],
privilege integer[],
CONSTRAINT t_user_pkey PRIMARY KEY (id)
)

此处我们看到 t_user表中有 serial字段（这是一个SEQUENCE，用法与Oracle略同，有一点点差异），建表的时候，PostgreSQL会自动建一个SEQUENCE与id字段关联，并且默认值为 nextval(t_user_id_seq)，其次我们看到phone字段的类型是varchar(10)这样的一个数组，privilege的类型是int的数组。

我们创建一个登录的存储过程：f_login_user()
view plaincopy to clipboardprint?CREATE OR REPLACE FUNCTION f_login_user(p_login_name in character varying, p_login_passwd in character varying)
RETURNS refcursor
AS
$BODY$
DECLARE
p_user_cur refcursor;
BEGIN
OPEN p_user_cur FOR SELECT * FROM t_user
WHERE login_name = p_login_name AND login_passwd = p_login_passwd;
RETURN p_user_cur;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION f_login_user(p_login_name in character varying, p_login_passwd in character varying)
RETURNS refcursor
AS
$BODY$
DECLARE
p_user_cur refcursor;
BEGIN
OPEN p_user_cur FOR SELECT * FROM t_user
WHERE login_name = p_login_name AND login_passwd = p_login_passwd;
RETURN p_user_cur;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

存储过程有2个参数，登录名，登录密码，返回一个游标类型。我们准备用iBatis来调用这个存储过程，调用的方式为：{? = call f_login_user(?,?)}

接下来我们准备iBatis的SqlMap的xml文件：
view plaincopy to clipboardprint?<?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 namespace="UserDaoSqlMap">

<typeAlias alias="user" type="org.kylin.emap.bean.UserBean"/>

<resultMap id="userResultMap" class="user">
<result property="id" column="id"/>
<result property="loginName" column="login_name"/>
<result property="loginPasswd" column="login_passwd"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="phone" column="phone" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>

<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" mode="OUT"/>
<parameter property="loginName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="loginPasswd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>

<select id="checkUser" resultMap="userResultMap" parameterClass="user">
SELECT id, login_name, login_passwd, name, sex, phone, privilege
FROM t_user
WHERE login_name = #loginName# AND login_passwd = #loginPasswd#
</select>

<procedure id="loginUser" resultMap="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = call f_login_user(?,?)}
</procedure>

</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 namespace="UserDaoSqlMap">

<typeAlias alias="user" type="org.kylin.emap.bean.UserBean"/>

<resultMap id="userResultMap" class="user">
<result property="id" column="id"/>
<result property="loginName" column="login_name"/>
<result property="loginPasswd" column="login_passwd"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="phone" column="phone" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>

<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" mode="OUT"/>
<parameter property="loginName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="loginPasswd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>

<select id="checkUser" resultMap="userResultMap" parameterClass="user">
SELECT id, login_name, login_passwd, name, sex, phone, privilege
FROM t_user
WHERE login_name = #loginName# AND login_passwd = #loginPasswd#
</select>

<procedure id="loginUser" resultMap="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = call f_login_user(?,?)}
</procedure>

</sqlMap>

注意看红色部分的字：在userResultMap中有typeHandler的描述，如phone字段，用org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler这个类来处理数组类型，这个类是实现了iBatis的com.ibatis.sqlmap.client.extensions.TypeHandlerCallback，iBatis是很强啊，很方便扩展。

先看procedure的定义，用到了loginUserParameters做参数，返回userResultMap。
loginUserParameters描述了3个参数,按照调用方式: ? = call f_login_user(?, ?)的顺序，第一个参数是返回结果集的，这里的jdbcType填写OTHER，javaType填写java.sql.ResultSet，如果是ORACLE的存储过程通过游标返回结果集的话，jdbcType应该填写为ORACLECURSOR，看来iBatis专门为ORACLE做了开发，不过在PostgreSQL中不能用ORACLECURSOR，得用OTHER。

好了，现在我们看看DAO中如何通过SqlMapClient得到这个结果集：
view plaincopy to clipboardprint?public UserBean loginUser(String loginName, String loginPasswd) throws DaoException {

HashMap<String, String> parameters = new HashMap<String, String>();
parameters.put("loginName", loginName);
parameters.put("loginPasswd", loginPasswd);
return (UserBean)getSqlMapClientTemplate().queryForObject("UserDaoSqlMap.loginUser", parameters);
}
public UserBean loginUser(String loginName, String loginPasswd) throws DaoException {

HashMap<String, String> parameters = new HashMap<String, String>();
parameters.put("loginName", loginName);
parameters.put("loginPasswd", loginPasswd);
return (UserBean)getSqlMapClientTemplate().queryForObject("UserDaoSqlMap.loginUser", parameters);
}

当前这个例子，存储过程通过游标返回了一行数据，我们可以用queryForObject得到结果集，结果集也自动影射为UserBean了，很方便使用。

另外需要注意的问题：
1. PostgreSQL的存储过程支持 returns SETOF record 来返回多行记录，这种方法在存储过程中内部使用了游标，效率比较高，另外应用也不用关心游标的关闭和释放的问题。
2. 如果存储过程返回多个结果集，需要用 returns SETOF refcursor 来返回多个结果集。