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

基于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 isnull OR $TableName='')OR ($FieldList isnull OR $FieldList='')OR ($PrimaryKey isnull OR $PrimaryKey='')OR $SortType < 1 OR $SortType >3OR $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 = 2THEN  
  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,',') > 0THEN  
            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！