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{%>下一页<%}%>