基于Mysql的通用分页存储过程

最新推荐文章于 2021-02-08 19:17:10 发布

转载最新推荐文章于 2021-02-08 19:17:10 发布 · 532 阅读

本文介绍了一种基于MySQL的通用分页存储过程P_viewPage，该存储过程通过输入表名、字段列表、主键等参数，实现了灵活的分页查询功能。适用于多种Web开发场景。

基于Mysql的通用分页存储过程 2008-04-16 14:08:56

标签：数据库 mysql 存储过程

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。 http://wenlujun.blog.51cto.com/336737/71819

　　前面“ 结合JSTL用配置来完成基于存储过程的分页显示”文章中使用的是SQL Server 2000数据库，而由于Web开发可能不仅仅使用一种数据库，比如还可能用到Mysql等，因此为了在Mysql下方便的使用分页，笔者将分页存储过程P_viewPage在Mysql下进行了改写，通用性不变，功能也基本一样，读者可以按照自己的需求自行调用该存储过程实现分页，也可以按照前面文章中那个项目模板实现分页显示，这里不得不为自己赞一下，由于前面文章中介绍的项目模板移植性好，你除了某些必要的工作如在Mysql下重新构建数据库，重写分页存储过程和加载Mysql驱动外，仅仅针对项目的变化就是重写连接池配置文件Proxool.xml，使其适应Mysql配置，而项目其它任何地方均勿需做改动，是不是特别方便。而且我们完全可以实现项目中两种不同数据库的无缝结合，同时访问两种不同的数据库而不对项目做任何代码的改写，你要做的工作也仅仅是在连接池配置文件Proxool.xml中添加一个新的连接池而已！Perfect！下面看看我们在Mysql下重写的分页存储过程P_viewPage.

CREATE PROCEDURE `P_viewPage`(
       $TableName VARCHAR(200),
       $FieldList VARCHAR(2000),
       $PrimaryKey VARCHAR(100),
       $ Where VARCHAR(1000),
       $ Order VARCHAR(1000),
       $SortType INT,
       $RecorderCount INT,
       $PageSize INT,
       $PageIndex INT,
OUT    $TotalCount INTEGER,
OUT    $TotalPageCount INTEGER
)
BEGIN

IF !(($TableName is null OR $TableName= '') OR ($FieldList is null OR $FieldList= '') OR ($PrimaryKey is null OR $PrimaryKey= '') OR $SortType < 1 OR $SortType >3 OR $RecorderCount < 0 OR $PageSize < 0 OR $PageIndex < 0) THEN

IF ($ where is null OR $ where= '') THEN
   SET @new_where1 = ' ' ;
   SET @new_where2 = ' WHERE ' ;
ELSE
   SET @new_where1 =concat( ' WHERE ',$ where);
   SET @new_where2 =concat( ' WHERE ',$ where, ' AND ');
END IF;

IF $ order= '' OR $SortType = 1 OR $SortType = 2 THEN
   IF $SortType = 1 THEN
       SET @new_order =concat( ' ORDER BY ',$PrimaryKey, ' ASC' );
     END IF;
   IF $SortType = 2 THEN
       SET @new_order =concat( ' ORDER BY ',$PrimaryKey, ' DESC');
     END IF;
ELSE
   SET @new_order =concat( ' ORDER BY ',$ Order);
END IF;

SET @SqlCount = concat( 'SELECT COUNT(*) into @TotalCount FROM ',$TableName,@new_where1);
SET @SqlCount1 = concat( 'SELECT CEILING((COUNT(*)+0.0)/',$PageSize, ') into @TotalPageCount FROM ',$TableName,@new_where1);
IF $RecorderCount = 0 THEN
     PREPARE stmt1 FROM @SqlCount;
     EXECUTE stmt1;
     set $TotalCount=@TotalCount;
     PREPARE stmt1 FROM @SqlCount1;
     EXECUTE stmt1;
     set $TotalPageCount=@TotalPageCount;
ELSE
   set $TotalCount = $RecorderCount;
END IF;

IF $PageIndex > CEILING(($TotalCount+0.0)/$PageSize) THEN
   SET $PageIndex = CEILING(($TotalCount+0.0)/$PageSize);
END IF;

IF $PageIndex = 0 or $PageIndex = 1 THEN
   SET @Sql=concat( 'SELECT ',$FieldList, ' FROM ',$TableName,@new_where1,@new_order, ' limit ',$PageSize);
ELSE
   IF $SortType = 1 THEN
       SET @Sql=concat( 'SELECT ',$FieldList, ' FROM ',$TableName,@new_where2,$PrimaryKey, ' > (SELECT max(',$PrimaryKey, ') FROM (SELECT ',$PrimaryKey, ' FROM ',$TableName,@new_where1,@new_order, ' limit ',$PageSize*($PageIndex-1), ' ) AS TMP) ',@new_order, ' limit ',$PageSize);
   END IF;
   IF $SortType = 2 THEN
       SET @Sql=concat( 'SELECT ',$FieldList, ' FROM ',$TableName,@new_where2,$PrimaryKey, ' < (SELECT MIN(',$PrimaryKey, ') FROM (SELECT ',$PrimaryKey, ' FROM ',$TableName,@new_where1,@new_order, ' limit ',$PageSize*($PageIndex-1), ' ) AS TMP) ',@new_order, ' limit ',$PageSize);
     END IF;
   IF $SortType = 3 THEN
       IF INSTR($ Order, ',') > 0 THEN
             SET @Sql=concat( 'SELECT ',$FieldList, ' FROM ',$TableName,@new_where2,$PrimaryKey, ' NOT IN (SELECT ',$PrimaryKey, ' FROM (SELECT ',$PrimaryKey, ' FROM ',$TableName,@new_where1,@new_order, ' limit ',$PageSize*($PageIndex-1), ' ) a)',@new_order, ' limit ',$PageSize);
       ELSE
             SET @new_order =concat( ' ORDER BY ',$PrimaryKey, ' ASC' );
             SET @Sql=concat( 'SELECT ',$FieldList, ' FROM ',$TableName,@new_where2,$PrimaryKey, ' > (SELECT max(',$PrimaryKey, ') FROM (SELECT ',$PrimaryKey, ' FROM ',$TableName,@new_where1,@new_order, ' limit ',$PageSize*($PageIndex-1), ' ) AS TMP) ',@new_order, ' limit ',$PageSize);
       END IF;
   END IF;
END IF;

Prepare stmt2 from @Sql;
execute stmt2;

END IF;
END ;

Job Done！

标签