Ibatis调用存储过程

Ibatis调用存储过程

Ibatis    2009-10-11 15:31    阅读7   评论0   
字号:       

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;

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

1对于全部是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>  

  2对于返回类型是游标 (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;
    }

3对于返回值为一般数据类型的函数:

函数:

  Function DeleteCommand(v_commandid Number,v_commandsetid Number default 0,v_dbid Number default 0) return Number ;

采用如下方式处理(把返回值当作出参来处理OUT):

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

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值