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 ;