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来返回多个结果集。
相关推荐
如：对于sql语句order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id，则解析成的sql为order by "id"。