一,Oracle 创建存储过程
CREATE OR REPLACE PROCEDURE P_TEST_PAGING_QUERY(
p_pagesql IN VARCHAR2, --sql
p_curPage IN OUT NUMBER , --当前页
p_pageSize IN OUT NUMBER , --每页显示记录的条数
p_totalRecords OUT NUMBER, --总记录数
p_totalPages OUT NUMBER , -- 总页数
pageResultSet OUT SYS_REFCURSOR -- 输出结果集游标
)
AS
v_sql VARCHAR2(2000):=''; --sql语句
v_startRecord NUMBER; --开始显示的记录数
v_endRecord NUMBER; --结束显示的记录条数
BEGIN
--记录总记录条数
v_sql:='select count(*) FROM (' || p_pagesql || ')';
EXECUTE IMMEDIATE v_sql INTO p_totalRecords;
IF MOD(p_totalRecords,p_pageSize)=0 THEN
--得到整数则直接取得到的页码数否在原来的基础上增加一个页码
p_totalPages:=p_totalRecords/p_pageSize;
ELSE
p_totalPages:=p_totalRecords/p_pageSize+1;
END IF;
--验证页号
IF p_curPage<1 THEN
p_curPage:=1;
END IF;
--如果取的当前页大于总页数则取最大页数的数据
IF p_curPage>p_totalPages THEN
p_curPage:=p_totalPages;
END IF;
--实现分页查询
v_startRecord :=(p_curPage - 1) * p_pageSize + 1;
v_endRecord :=p_curPage * p_pageSize;
v_sql := 'select * from (SELECT t.*, ROWNUM RN from (' || p_pagesql || ') t where rownum<=' || v_endRecord || ' ) where RN>=' ||v_startRecord;
p_totalPages :=floor(p_totalPages); --去整数总页
OPEN pageResultSet FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
CLOSE pageResultSet;
END P_TEST_PAGING_QUERY;
2,mybatis调用方式,2中方式调用
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.dao.EmpMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.demo.model.Emp">
<id column="EMPNO" property="empno" />
<result column="ENAME" property="ename" />
<result column="JOB" property="job" />
<result column="MGR" property="mgr" />
<result column="HIREDATE" property="hiredate" />
<result column="SAL" property="sal" />
<result column="COMM" property="comm" />
<result column="DEPTNO" property="deptno" />
</resultMap>
<!-- 执行存储过程。语句类型statementType一定要为CALLABLE-->
<!-- 入参定义:占位符形式写入SQL,然后接口中使用MAP传入 -->
<!-- 参数模式:共IN、OUT、INOUT三种,如果是IN参可不写,此外IN存在 null的情况,必须指定 jdbcType,还有 OUT时必须指定 jdbcType -->
<select id="test_exe_procedure1" statementType="CALLABLE">
{CALL
P_TEST_PAGING_QUERY(
'select t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO from scott.emp t',
#{in_PAGE, mode=IN, jdbcType=INTEGER},
#{in_ROWS, mode=IN, jdbcType=INTEGER},
#{inout_TOTAL_RECORDS,mode=INOUT, jdbcType=INTEGER},
#{inout_TOTAL_PAGES, mode=INOUT, jdbcType=INTEGER},
#{out_SYSCURSOR, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=BaseResultMap}
)}
</select>
<!-- 执行存储过程2-->
<!-- 入参定义:使用parameterMap进行参数映射,这时候存储过程参数占位符为:? -->
<!-- 注意:调用存储过程的两个大括号和中间的内容不要换行!可能会出问题的。正确姿势:{CALL xxx(?,?...)} -->
<parameterMap type="java.util.Map" id="test_exe_procedure2_param">
<parameter property="in_SQL" mode="IN" jdbcType="VARCHAR" />
<parameter property="in_PAGE" mode="IN" jdbcType="INTEGER" />
<parameter property="in_ROWS" mode="IN" jdbcType="INTEGER" />
<parameter property="inout_TOTAL_RECORDS" mode="INOUT" jdbcType="INTEGER" />
<parameter property="inout_TOTAL_PAGES" mode="INOUT" jdbcType="INTEGER" />
<parameter property="out_SYSCURSOR" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="BaseResultMap" />
</parameterMap>
<select id="test_exe_procedure2" statementType="CALLABLE" parameterMap="test_exe_procedure2_param">
{CALL P_TEST_PAGING_QUERY(?,?,?,?,?,?)}
</select>
</mapper>
3,测试代码如下
private void test_exe_procedure(boolean bTestProcedure1)
{
int in_PAGE = 3;
int in_ROWS =5;
int inout_TOTAL_RECORDS=0;
int inout_TOTAL_PAGES = 0;
HashMap<String,Object> mm=new HashMap<String,Object>();
mm.put("in_PAGE", in_PAGE);
mm.put("in_ROWS", in_ROWS);
mm.put("inout_TOTAL_RECORDS", inout_TOTAL_RECORDS);
mm.put("inout_TOTAL_PAGES", inout_TOTAL_PAGES);
mm.put("out_SYSCURSOR", new ArrayList<Emp>());
if(bTestProcedure1)
{
System.out.println("test_exe_procedure1...");
empMapper.test_exe_procedure1(mm);
}
else
{
System.out.println("test_exe_procedure2...");
mm.put("in_SQL", "select t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO from scott.emp t");
empMapper.test_exe_procedure2(mm);
}
System.out.println("Get in_PAGE: "+ mm.get("in_PAGE"));
System.out.println("Get in_ROWS: "+ mm.get("in_ROWS"));
System.out.println("Get inout_TOTAL_RECORDS: "+ mm.get("inout_TOTAL_RECORDS"));
System.out.println("Get inout_TOTAL_PAGES: "+ mm.get("inout_TOTAL_PAGES"));
List<Emp> empList = (List<Emp>)mm.get("out_SYSCURSOR");
Emp.Print(empList);
return;
}