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 ;