java通用分页代码:从存储过程到JSP页面（转的）

java通用分页代码


SQL存储过程代码：
CREATE proc page
(
@table_name varchar(100),--表名
@count int,--每页显示好多行
@page int,--当前页数
@table_id varchar(100),--表的主键
@sqlWhere varchar(1000),--过滤条件
@sqlOrder varchar(1000),--排序条件
@sumCount int output--返回参数，返回总页数
)
as
declare @sqlScript nvarchar(4000)
select @sumCount = 0
if @sqlWhere is not null and @sqlWhere <> ''
begin
select @sqlScript = 'select @sumCount=count(*) from '+@table_name + ' where ' + @sqlWhere
end
else
begin
select @sqlScript = 'select @sumCount=count(*) from '+@table_name
end

execute sp_executesql @sqlScript,N'@sumCount int output',@sumCount output--得到总行数
--select @sumCount
if @sumCount%@count<>0
select @sumCount = @sumCount / @count + 1--得到总页数
else
select @sumCount = @sumCount / @count
select @page = @page * @count--过滤的行数

select @sqlScript = 'select top '+cast(@count as varchar)+' * from ' + @table_name
+ ' where ' + @table_id + ' not in (select top ' + cast(@page as varchar) + ' ' +@table_id+ ' from '+@table_name+' '+case when @sqlWhere is not null and @sqlWhere<>'' then ' where ' +@sqlWhere else '' end+isnull(@sqlOrder,'')+' ) ' + case when @sqlWhere is not null and @sqlWhere<>'' then ' and ' + @sqlWhere else '' end+isnull(@sqlOrder,'')

exec(@sqlScript)
GO

调用存储过程方法：

//获得某城市下，满足贡献度需求的迷宫任务列表
public ArrayList getMiGongRenWuList(String table_name,int count,int page,String table_id,String sqlWhere,int []out_print){
   ArrayList migong_list = new ArrayList();
   Connection con = null;
 
   try {
    con = dbMain.getConnection();
    con.setAutoCommit(false);
    CallableStatement cstm = con.prepareCall( "{call page(?,?,?,?,?,?,?)}");
    cstm.setString(1,table_name);
    cstm.setInt(2,count);
    cstm.setInt(3,page);
    cstm.setString(4,table_id);
    cstm.setString(5,sqlWhere);
    cstm.setString(6,"");
    cstm.registerOutParameter(7,Types.INTEGER);
  
    ResultSet rs = cstm.executeQuery();
  
    while(rs.next()){
     migong_list.add(City_MiGongDAO.assembleCity_MiGong(rs));
    }
    out_print[0] = Integer.parseInt(cstm.getObject(7).toString());
    con.commit();
    con.close();
   } catch (ClassNotFoundException e) {
    e.printStackTrace();
   } catch (SQLException e) {
    try {
     con.rollback();
     con.close();
    } catch (SQLException e1) {
     e1.printStackTrace();
    }
    e.printStackTrace();
   }
   return migong_list;
}

SEVLET调用该方法：

int []out_page = {0};

ArrayList city_renwu_list = RenWu_MiGongDao.getMiGongRenWuList(table_name,16,page,table_id,sqlWhere,out_page);

JSP页面：

<%if(request.getParameter("page")==null||(Integer.parseInt(request.getParameter("page").toString()) - 1)<0){%>上一页<%}else{%><a href="<%=path%>/servlet/game/qiantai/migong/ShowMiGong?search_condition=<%= search_condition %>&radio_search=<%= radio_search %>&page=<%=request.getParameter("page")==null?(0 + 1):(Integer.parseInt(request.getParameter("page").toString()) - 1)%>">上一页</a><%}%>

<%if(sumPageCount>(request.getParameter("page")==null?(0 + 1):(Integer.parseInt(request.getParameter("page").toString()) + 1))){%><a href="<%=path%>/servlet/game/qiantai/migong/ShowMiGong?search_condition=<%= search_condition %>&radio_search=<%= radio_search %>&page=<%=request.getParameter("page")==null?(0 + 1):(Integer.parseInt(request.getParameter("page").toString()) + 1)%>">下一页</a><%}else{%>下一页<%}%>