JDBC调用MySQL分页存储过程实现(一)

DROP PROCEDURE IF EXISTS `pro_pager`;

CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`(
 in p_pageNo int, /*当前页*/  
 in p_perPageCnt int, /*每页记录数*/
 in p_sql VARCHAR(2000), /*查询sql语句*/  
 out v_totalRowsCnt int, /*记录总条数*/  
 out v_totalPageCnt int) /*记录总页数*/
BEGIN
  /*当传入查询页数为null或者<1时，赋p_pageNo=1 */
  IF p_pageNo IS NULL OR p_pageNo < 1 THEN
    SET p_pageNo = 1;
  END IF; 

  SET @rowsCnt = 0;
  SET @pagesCnt = 0;
  SET @sqlCnt = CONCAT('select count(1) into @rowsCnt from (',p_sql,') as t');  -- 统计总记录数sql

  /*统计总记录数-预处理*/
  PREPARE s_cnt from @sqlCnt;
  EXECUTE s_cnt;
  DEALLOCATE PREPARE s_cnt;
  SET v_totalRowsCnt = @rowsCnt;

  SET @pagesCnt = floor((@rowsCnt + p_perPageCnt - 1) / p_perPageCnt);  -- 计算总页数
  /*当传入查询页数>总页数时，赋p_pageNo=总页数 */
  IF p_pageNo > @pagesCnt THEN
    SET p_pageNo = @pagesCnt;
  END IF;

  SET v_totalPageCnt = @pagesCnt;
  SET @limitStart = (p_pageNo - 1) * p_perPageCnt;  -- 查询记录起始行
  SET @limitEnd = p_perPageCnt;  -- 查询记录结束行
  SET @sqlQry = CONCAT(p_sql, ' limit ', @limitStart, ',', @limitEnd);  -- 查询记录集sql

  /*查询记录集-预处理*/
  PREPARE record from @sqlQry;
  EXECUTE record;
  DEALLOCATE PREPARE record;
END ;