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

姚强 2011-09-06

Ibatis配置文件ibatis.xml:

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

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

<!--在Java对象跟Oracle列之间转换-->

</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;

8

9FETCHl_refcBULKCOLLECTINTOl_data;

10

11CLOSEl_refc;

12

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

AS

$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

AS

$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</span>"/>

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

</resultMap>

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

<parameterproperty="result"<spanstyle="color:#ff0000;">jdbcType="OTHER"javaType="java.sql.ResultSet</span>"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</span>"parameterMap="<spanstyle="color:#ff0000;">loginUserParameters</span>">

{?=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</span>"/>

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

</resultMap>

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

<parameterproperty="result"<spanstyle="color:#ff0000;">jdbcType="OTHER"javaType="java.sql.ResultSet</span>"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</span>"parameterMap="<spanstyle="color:#ff0000;">loginUserParameters</span>">

{?=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来返回多个结果集。

相关推荐

兔子压倒窝边草 / 0评论 2013-07-26
蜀川居 / 0评论 2014-07-16