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