Ibatis调用存储过程
procedure.xml的ibatis配置文件如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="PROCEDURE">
<!--
- =======================================================
- 授权用户某个设备得权限
- =======================================================
-->
<parameterMap class="map" id="proPermissionDevicePerson">
<parameter property="deviceId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="deviceType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="permissionType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="userId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="result" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
</parameterMap>
<procedure id="shareDeviceToPerson" parameterMap="proPermissionDevicePerson">
<![CDATA[
{call share_dev_prv_to_person (?,?,?,?,?)}
]]>
</procedure>
</sqlMap>
JAVA调用实现PermissionDAOIbatisImpl.java如下：
// 授权设备给用户
public static final String SHARE_DEVICE_TO_PERSON = "PROCEDURE.shareDeviceToPerson";
/**
* 授权某个设备给某个用户
* @param userPermission
*/
public boolean shareDeviceToPerson(UserPermission userPermission) {
Map map = new HashMap();
boolean flag = false;
map.put("deviceId", userPermission.getDeviceId());
map.put("deviceType", userPermission.getDeviceType());
map.put("userId", userPermission.getUserId());
map.put("permissionType", userPermission.getPermissionType());
try {
getSqlMapClientTemplate().insert(SHARE_DEVICE_TO_PERSON, map);
String result = (String) map.get("result");
//得到返回值
if (StringUtil.isNotBlank(result) && "2".equalsIgnoreCase(result)) {
flag = true;
}
} catch (Exception e) {
throw new DaoException("DAOException: " + e);
}
return flag;
}
存储过程的实现代码，嘿嘿。完全不了解，以后有机会学习一下存储过程
create or replace procedure share_dev_prv_to_person(deviceId in varchar2,
type in varchar2,
permission in varchar2,
userId in varchar2,
result out varchar2) as
/**
deviceId 设备id
tyoe 设备类型
permission 权限类型
userId 用户id
*/
v_role_id varchar2(31);
v_organ_id varchar2(31);
v_permission_id varchar2(31);
v_type varchar2(31);
v_permission_type varchar2(200);
v_is_exists int;
begin
result := '1';
select encodeID(TROLE_SEQ.nextval) into v_role_id from dual;
--convert type
if type = 1 then
v_type := 'general_camera_vic';
elsif type = 2 then
v_type := 'ip_camera_vic';
elsif type = 3 then
v_type := 'alarm_input_channel';
elsif type = 4 then
v_type := 'alarm_output_channel';
end if;
--convert permission type
if permission = 1 then
v_permission_type := 'vic_realtime_video_play_operation';
elsif permission = 2 then
v_permission_type := 'aic_operation';
elsif permission = 3 then
v_permission_type := 'aoc_open_close_operation';
end if;
-- insert role
begin
select id
into v_role_id
from T_ROLE
where F_NAME = deviceId || '角色' || type || '_' || permission;
select 1
into v_is_exists
from permission pp,
T_ROLE role,
R_USER_ROLE ru,
R_ROLE_PERMISSION rrp
where role.f_name = deviceId || '角色' || type || '_' || permission
and ru.user_id = userId
and rrp.ROLE_ID = role.id
and rrp.PERMISSION_ID = pp.id;
exception
when No_data_found then
dbms_output.put_line('no role founed!!');
select organ_id into v_organ_id from t_user where id = userId;
INSERT INTO T_ROLE
(ID, F_NAME, NOTE, ORGAN_ID, CREATE_PERSON)
VALUES
(v_role_id,
deviceId || '角色' || type || '_' || permission,
deviceId || '角色' || type || '_' || permission,
v_organ_id,
userId);
-- grant role permission
select id
into v_permission_id
from permission t
where t.resource_id = deviceId
and t.operation = v_permission_type
and t.resource_type = v_type;
INSERT INTO R_ROLE_PERMISSION
(PERMISSION_ID, ROLE_ID)
VALUES
(v_permission_id, v_role_id);
-- grant role to other person
INSERT INTO R_USER_ROLE
(ROLE_ID, USER_ID)
VALUES
(v_role_id, userId);
end;
commit;
result := '2';
end;
---------------------------------------------------------------------------------------------------------
１对于全部是in 类型的参数过程，采用下面的方法调用（ibatis版本是2.3）
比如过程：
Procedure Flashback_Op(v_table_owner Varchar2,v_table_name Varchar2,v_FlashbackScn Number,v_xid Varchar2,v_dbid number);
调用方法：
<parameterMap id="tableDml_recycle" class="java.util.Map">
<parameter property="owner" jdbcType="varchar"
javaType="java.lang.String" mode="IN " />
<parameter property="name" jdbcType="varchar"
javaType="java.lang.String" mode="IN " />
<parameter property="scn" jdbcType="NUMBER"
javaType="java.math.BigDecimal" mode="IN " />
<parameter property="dbId" jdbcType="NUMBER"
javaType="java.math.BigDecimal" mode="IN " />
</parameterMap>
<procedure id="tableDml_zcRecycle" parameterMap="tableDml_caozuo">
{call TassetREC.Flashback_Op(?,?,?,?,?)}
</procedure>
２对于返回类型是游标 (OUT)的函数，采用如下方式调用。
函数：
Function GetFirstPageNumRows(v_where varchar2,v_dbid Number,v_Numrows varchar2,v_queryid varchar2) return sys_refcursor ;
调用方式：
<parameterMap id="searchParam" class="java.util.Map">
<parameter property="result" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT " resultMap="auditselect_resultList" />
<parameter property="ipAddress"
jdbcType="varchar" javaType="java.lang.String" mode="IN" />
<parameter property="dbId" jdbcType="NUMBER"
javaType="java.math.BigDecimal" mode="IN"/>
<parameter property="countNumber" jdbcType="NUMBER"
javaType="java.lang.Integer" mode="IN"/>
<parameter property="clientId" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="firstPage" parameterMap="searchParam">
{? = call tlgadmin.GetFirstPageNumRows(?,?,?,?)} （注意返回值在前面用?代表）
</procedure>
java代码这么取值：
@SuppressWarnings("unchecked")
public List getAuditSelectByPages(Map map) {
getSqlMapClientTemplate().queryForList("firstPage", map);//调用
List arryList = (ArrayList) map.get("result");//取值
return arryList;
}
３对于返回值为一般数据类型的函数：
函数：
Function DeleteCommand(v_commandid Number,v_commandsetid Number default 0,v_dbid Number default 0) return Number ;
采用如下方式处理（把返回值当作出参来处理ＯＵＴ）：
<parameterMap id="CommandDeleteById" class="java.util.Map">
<parameter property="result" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT" />
<parameter property="commandId" jdbcType="NUMBER"
javaType="java.lang.Integer" mode="IN" />
<parameter property="commandsetId" jdbcType="NUMBER"
javaType="java.lang.Integer" mode="IN" />
<parameter property="dbId" jdbcType="NUMBER"
javaType="java.math.BigDecimal" mode="IN" />
</parameterMap>
<procedure id="Command_deleteById"
parameterMap="CommandDeleteById">
{ ? = call truleadmin.DeleteCommand(?,?,?)}
</procedure>