前面“ 结合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！