Oracle存储过程，ibatis兑现分页

Oracle存储过程，ibatis实现分页

1、oracle 分页存储过程脚本

create or replace procedure Pagination(Pindex in integer, --页号从1开始
                                         Psql   in varchar2, --查询语句
                                         Psize in integer, --每页分多少行
                                         Pcount out number, --共有多少页
                                         Result out sys_refcursor --返回数据集
                                         ) as

v_sql   VARCHAR2(3000);
v_count number;
v_Plow number;
v_Phei number;
Begin
-----------取分页总数 -----------
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql
    into v_count;
Pcount := v_count; --ceil(v_count / Psize);
---------------显示任意页内容 ---------------
v_Phei := Pindex * Psize;
v_Plow := v_Phei - Psize + 1;
v_sql := 'select * from (select rownum rn,t.* from (' || Psql ||
            ') t ) where rn between ' || v_Plow || ' and ' || v_Phei ;

open Result for v_sql;

End Pagination;

2、Ibatis调用oracle存储过程sqlMap文件配置

<parameterMap id="parametermap" class="java.util.HashMap" >
     <parameter property="pindex" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
     <parameter property="psql" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
     <parameter property="psize" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
     <parameter property="pcount" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
     <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>

<procedure id="pagination" resultClass="com.xxx.xxx.pojo.ObjectQurey" parameterMap="parametermap">
     {call p_pagination(?,?,?,?,?)}
</procedure>

3、service层调用sqlMap

public List<ObjectQurey> pagination(Map param) throws AppException {
      try
  {
   return baseDao.queryForList(ObjectQureySql.PageNation, param);
  }catch (Exception e)
  {
   throw new AppException(THIS_CLASS_NAME, "getAllObjectQureySqlInfo", MODULE_NAME, MessageUtil.BUSSIESS_ERROR, e);
  }
}

我的异常网推荐解决方案：oracle存储过程,http://www..net/oracle-develop/177537.html

Oracle存储过程，ibatis兑现分页

相关推荐