以前一直是把分页算法写在类里面,今天试了试写进存储过程中,效果好不错。
使用方法:
CALL `dbpassport`.`get_page`('表名','字段名列表(多字段用逗号隔开)',当前页号码,每页记录数,'排序字段(多字段用逗号隔开)','筛选条件(没有则留空)')
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
/**//*Table name*/
tableName varchar(100),
/**//*Fileds to display*/
fieldsNames varchar(100),
/**//*Page index*/
pageIndex int,
/**//*Page Size*/
pageSize int,
/**//*Field to sort*/
sortName varchar(500),
/**//*Condition*/
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist varchar(200);
if fieldsNames=''||fieldsNames=null THEN
set fieldlist='*';
else
set fieldlist=fieldsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/CrackCell/archive/2007/10/09/1817215.aspx
使用方法:
CALL `dbpassport`.`get_page`('表名','字段名列表(多字段用逗号隔开)',当前页号码,每页记录数,'排序字段(多字段用逗号隔开)','筛选条件(没有则留空)')
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
/**//*Table name*/
tableName varchar(100),
/**//*Fileds to display*/
fieldsNames varchar(100),
/**//*Page index*/
pageIndex int,
/**//*Page Size*/
pageSize int,
/**//*Field to sort*/
sortName varchar(500),
/**//*Condition*/
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist varchar(200);
if fieldsNames=''||fieldsNames=null THEN
set fieldlist='*';
else
set fieldlist=fieldsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/CrackCell/archive/2007/10/09/1817215.aspx