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

姚强

2011-09-06

关注关注

Ibatis配置文件ibatis.xml：

<resultMapid="result-map"class="com.xxx.xxx.Table">

<resultproperty="rsId"column="id"/>

</resultMap>

<parameterMapid="parameter-map"class="java.util.HashMap">

<parameterproperty="param1"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

<parameterproperty="param2"jdbcType="Integer"javaType="java.lang.Integer"mode="IN"/>

<parameterproperty="result"jdbcType="ORACLECURSOR"javaType="java.sql.ResultSet"mode="OUT"/>

</parameterMap>

<procedureid="testCur"resultMap="result-map"parameterMap="parameter-map">

{calltestProcedures(?,?,?)}

</procedure>

再看java里面调用：

MapparamMap=newHashMap();

Stringstr=request.getParameter(.....);

intn=request.getParameter(.....);

paramMap.put("param1",str);

paramMap.put("param2",n);

ListdetailList=null;

try{

detailList=getSqlMapClientTemplate().queryForList("testCur",paramMap);

//可以用getSqlMapClientTemplate().queryForObject("testCur",paramMap);返回值是Object。

}catch(Exceptionex){

ex.printStackTrace();returndetailList;

detailList就是需要的结果集

================================================================

在实际的工作和学习中，我们可以通过Oracle数据库提供的REFCURSOR功能实现在程序间传递结果集的功能，另外，利用REFCURSOR可以同时实现BULKSQL，以此提高SQL的性能。

首先，我们需要使用scott用户的emp表实现以下测试：

SQL>descemp

NameNull?Type

----------------------------------------

EMPNONOTNULLNUMBER(4)

ENAMEVARCHAR2(10)

JOBVARCHAR2(9)

MGRNUMBER(4)

HIREDATEDATE

SALNUMBER(7,2)

COMMNUMBER(7,2)

DEPTNONUMBER(2)

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

SQL>setserveroutputon

SQL>DECLARE

2TYPEmytableISTABLEOFemp%ROWTYPE;

3l_datamytable;

4l_refcsys_refcursor;

5BEGIN

6OPENl_refcFOR

7SELECTempno,ename,job,mgr,hiredate,sal,comm,deptnoFROMemp;

9FETCHl_refcBULKCOLLECTINTOl_data;

11CLOSEl_refc;

13FORiIN1..l_data.COUNT

14LOOP

15DBMS_OUTPUT.put_line(l_data(i).ename

16||'washiredsince'

17||l_data(i).hiredate

18);

19ENDLOOP;

20END;

21/

SMITHwashiredsince17-DEC-80

ALLENwashiredsince20-FEB-81

WARDwashiredsince22-FEB-81

JONESwashiredsince02-APR-81

MARTINwashiredsince28-SEP-81

BLAKEwashiredsince01-MAY-81

CLARKwashiredsince09-JUN-81

SCOTTwashiredsince19-APR-87

KINGwashiredsince17-NOV-81

TURNERwashiredsince08-SEP-81

ADAMSwashiredsince23-MAY-87

JAMESwashiredsince03-DEC-81

FORDwashiredsince03-DEC-81

MILLERwashiredsince23-JAN-82

PL/SQLproceduresuccessfullycompleted.

=====================================================================

iBatis调用存储过程返回结果集

数据表：t_userviewplaincopytoclipboardprint?</pre><preclass="html"name="code">CREATETABLEt_user

(

idserialNOTNULL,

login_namecharactervarying(20)NOTNULL,

login_passwdcharactervarying(20),

namecharactervarying(20)NOTNULL,

sexsmallintNOTNULLDEFAULT1,

phonecharactervarying(10)[],

privilegeinteger[],

CONSTRAINTt_user_pkeyPRIMARYKEY(id)

)

</pre><preclass="html"name="code">CREATETABLEt_user

(

idserialNOTNULL,

login_namecharactervarying(20)NOTNULL,

login_passwdcharactervarying(20),

namecharactervarying(20)NOTNULL,

sexsmallintNOTNULLDEFAULT1,

phonecharactervarying(10)[],

privilegeinteger[],

CONSTRAINTt_user_pkeyPRIMARYKEY(id)

)

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

我们创建一个登录的存储过程：f_login_user()

viewplaincopytoclipboardprint?CREATEORREPLACEFUNCTIONf_login_user(p_login_nameincharactervarying,p_login_passwdincharactervarying)

RETURNSrefcursor

$BODY$

DECLARE

p_user_currefcursor;

BEGIN

OPENp_user_curFORSELECT*FROMt_user

WHERElogin_name=p_login_nameANDlogin_passwd=p_login_passwd;

RETURNp_user_cur;

END;

$BODY$LANGUAGE'plpgsql'VOLATILE;

CREATEORREPLACEFUNCTIONf_login_user(p_login_nameincharactervarying,p_login_passwdincharactervarying)

RETURNSrefcursor

$BODY$

DECLARE

p_user_currefcursor;

BEGIN

OPENp_user_curFORSELECT*FROMt_user

WHERElogin_name=p_login_nameANDlogin_passwd=p_login_passwd;

RETURNp_user_cur;

END;

$BODY$LANGUAGE'plpgsql'VOLATILE;

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

接下来我们准备iBatis的SqlMap的xml文件：

viewplaincopytoclipboardprint?<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEsqlMapPUBLIC"-//iBATIS.com//DTDSQLMap2.0//EN""http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMapnamespace="UserDaoSqlMap">

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

<resultMapid="userResultMap"class="user">

<resultproperty="id"column="id"/>

<resultproperty="loginName"column="login_name"/>

<resultproperty="loginPasswd"column="login_passwd"/>

<resultproperty="name"column="name"/>

<resultproperty="sex"column="sex"/>

<resultproperty="phone"column="phone"<spanstyle="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler"/>

<resultproperty="privilege"column="privilege"<spanstyle="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler"/>

</resultMap>

<parameterMapid="<spanstyle="color:#ff0000;">loginUserParameters"class="java.util.HashMap">

<parameterproperty="result"<spanstyle="color:#ff0000;">jdbcType="OTHER"javaType="java.sql.ResultSet"mode="OUT"/>

<parameterproperty="loginName"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

<parameterproperty="loginPasswd"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

</parameterMap>

<selectid="checkUser"resultMap="userResultMap"parameterclass="user">

SELECTid,login_name,login_passwd,name,sex,phone,privilege

FROMt_user

WHERElogin_name=#loginName#ANDlogin_passwd=#loginPasswd#

</select>

<procedureid="loginUser"resultMap="<spanstyle="color:#ff0000;">userResultMap"parameterMap="<spanstyle="color:#ff0000;">loginUserParameters">

{?=callf_login_user(?,?)}

</procedure>

</sqlMap>

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEsqlMapPUBLIC"-//iBATIS.com//DTDSQLMap2.0//EN""http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMapnamespace="UserDaoSqlMap">

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

<resultMapid="userResultMap"class="user">

<resultproperty="id"column="id"/>

<resultproperty="loginName"column="login_name"/>

<resultproperty="loginPasswd"column="login_passwd"/>

<resultproperty="name"column="name"/>

<resultproperty="sex"column="sex"/>

<resultproperty="phone"column="phone"<spanstyle="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler"/>

<resultproperty="privilege"column="privilege"<spanstyle="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler"/>

</resultMap>

<parameterMapid="<spanstyle="color:#ff0000;">loginUserParameters"class="java.util.HashMap">

<parameterproperty="result"<spanstyle="color:#ff0000;">jdbcType="OTHER"javaType="java.sql.ResultSet"mode="OUT"/>

<parameterproperty="loginName"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

<parameterproperty="loginPasswd"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

</parameterMap>

<selectid="checkUser"resultMap="userResultMap"parameterclass="user">

SELECTid,login_name,login_passwd,name,sex,phone,privilege

FROMt_user

WHERElogin_name=#loginName#ANDlogin_passwd=#loginPasswd#

</select>

<procedureid="loginUser"resultMap="<spanstyle="color:#ff0000;">userResultMap"parameterMap="<spanstyle="color:#ff0000;">loginUserParameters">

{?=callf_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个参数,按照调用方式:?=callf_login_user(?,?)的顺序，第一个参数是返回结果集的，这里的jdbcType填写OTHER，javaType填写java.sql.ResultSet，如果是ORACLE的存储过程通过游标返回结果集的话，jdbcType应该填写为ORACLECURSOR，看来iBatis专门为ORACLE做了开发，不过在PostgreSQL中不能用ORACLECURSOR，得用OTHER。

好了，现在我们看看DAO中如何通过SqlMapClient得到这个结果集：

viewplaincopytoclipboardprint?publicUserBeanloginUser(StringloginName,StringloginPasswd)throwsDaoException{

HashMap<String,String>parameters=newHashMap<String,String>();

parameters.put("loginName",loginName);

parameters.put("loginPasswd",loginPasswd);

return(UserBean)getSqlMapClientTemplate().queryForObject("UserDaoSqlMap.loginUser",parameters);

}

publicUserBeanloginUser(StringloginName,StringloginPasswd)throwsDaoException{

HashMap<String,String>parameters=newHashMap<String,String>();

parameters.put("loginName",loginName);

parameters.put("loginPasswd",loginPasswd);

return(UserBean)getSqlMapClientTemplate().queryForObject("UserDaoSqlMap.loginUser",parameters);

}

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

另外需要注意的问题：

1.PostgreSQL的存储过程支持returnsSETOFrecord来返回多行记录，这种方法在存储过程中内部使用了游标，效率比较高，另外应用也不用关心游标的关闭和释放的问题。

2.如果存储过程返回多个结果集，需要用returnsSETOFrefcursor来返回多个结果集。

ibatis sql存储过程 cursor oracle sql数据库 oracle数据库

安科网

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

姚强

姚强

相关推荐

mybatis插件开发小例子

ibatis和spring整合开发例子

iBatis与 Hibernate 相同点不同点

赶紧的，用户已经打电话来投诉线上出问题了

mybatis出现sql异常时的日志优化-打印sql参数（版本2）

mybatis出现sql异常时的日志优化-打印sql参数

JDBC、ibatis(mybatis)、Hibernate有什么不同？

通过插件规范程序员的开发过程(Eclipse 代码助手新功能发布)

spring整合ibatis事务管理

ibatis文档

打印mybatis的执行语句到控制台和文件

J2EE 领域的一些技术框架结构图

ibatis批量导入mysql数据库

mybatis总结

ibatis和hibernate的比较

缓存----Ibatis /Hibernate

Mybatis Invalid bound statement (not found):.....问题

iBatis入门

日志框架统一输出控制（slf4j+log4j）

关于Ibatis 转换 oracle date类型的问题

姚强