sql server 2008存储过程分页

50010623

USE [hospital]
GO
/****** Object:  StoredProcedure [dbo].[P_PAGE]    Script Date: 10/21/2012 10:33:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_PAGE]
/**执行的sql语句*/
@p_sql nvarchar(max),
/**查询总数据条数的sql语句*/
@p_sqlCount nvarchar(max),
/**每页显示多少条数据*/
@p_pageSizeStr nvarchar(max),
/**当前页码*/
@p_curentPageStr nvarchar(max) ,
/**总数据条数*/
@p_pageRows int output,
/**当前页码*/
@p_realCurentPage int output

as
begin
declare @p_pageSize int,@p_curentPage int;
set @p_pageSize = CONVERT(int, @p_pageSizeStr);
set @p_curentPage = CONVERT(int, @p_curentPageStr);
/*总数据条数*/
declare @m_pageRows int;
/*总页数*/
declare @m_pageCount int;
/**首先查询总页数**/
declare @count_string nvarchar(max);
set @count_string = 'select @pageRows=count(*) from ('+@p_sqlCount+') pcount';
exec  sp_executesql  @count_string,N'@pageRows int output',@m_pageRows   output;
set @p_pageRows = @m_pageRows;
/**根据总记录跳数计算出总页数**/
set @m_pageCount = CEILING((@m_pageRows+0.0)/@p_pageSize);
if @p_curentPage <= 1
begin
set @p_realCurentPage = 1;
end
else if (@p_curentPage > @m_pageCount)
begin
set @p_realCurentPage = @m_pageCount;
end
else
begin
set @p_realCurentPage = @p_curentPage;
end
declare @sql nvarchar(max);
declare @startIndex int;
set @startIndex =(@p_realCurentPage-1)*@p_pageSize;
/**
set @sql = 'select * from ('+@p_sql+') as tb where rownumber between '+CONVERT(nvarchar(max),@startIndex)+' and '+@p_pageSizeStr;
**/
set @sql = 'select top '+@p_pageSizeStr+' * from ('+@p_sql+') as tb where rownumber > '+CONVERT(nvarchar(max),@startIndex);
exec  sp_executesql @sql output;
end

/**存储过程调用*/
declare @sql nvarchar(max), @sqlcount nvarchar(max);
set @sql = 'select row_number() over(order by createTime desc ) as rownumber, id as id, code as code, name as name, createTime as createTime from sys_notice where (1=1) and (1=1) and (1=1) and (1=1) ';
set @sqlcount = 'select id as id from sys_notice where (1=1) and (1=1) and (1=1) and (1=1) '
declare @i int,@j int;
exec P_PAGE @sql,@sqlcount,'10','2',@i,@j;
/**java的调用*/
/**
* 根据查询语句，当前页码，每页显示的数量查找数据
* @param sqlStr ：查询语句 : 在查询语句传进来前请先格式化SqlStr
* @param curPage ：当前页码
* @param pageSize : 每页显示的数量
*/
public void execute(final String sqlStr, final String sqlCount, final int curPage, final int pageSize, PageUtils pageUtils) {
long a1 = System.currentTimeMillis();
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
ResultSetMetaData meta = null;
//总记录数
int rowsCount = 0;
//当前页
int p_curPage = 1;
//总页数
int p_pageCount = 0;
try {
conn = DataSourceUtils.getConnection(getSqlMapClient().getDataSource());
String sql = "{call p_page(?,?,?,?,?,?)}";
cs = conn.prepareCall(sql);
cs.setString(1, sqlStr);
cs.setString(2, sqlCount);
cs.setString(3, pageSize+"");
cs.setString(4, curPage+"");
cs.registerOutParameter(5, Types.INTEGER);
cs.registerOutParameter(6, Types.INTEGER);
rs = cs.executeQuery();
rs = cs.getResultSet();
long a2 = System.currentTimeMillis();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Map<String, Object> map = null;
meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
Object value = null;
while (rs.next()) {
map = new HashMap<String, Object>();
for (int i = 0; i < columnCount; i++) {
value = rs.getObject(i + 1);
if(null == value){
map.put(rs.getMetaData().getColumnName(i + 1), "");
}else{
if("DATETIME".equals(rs.getMetaData().getColumnTypeName(i+1))){
map.put(rs.getMetaData().getColumnName(i + 1), DateUtils.formatDateToString(rs.getTimestamp(i + 1),"yyyy-MM-dd HH:mm:ss"));
}else{
map.put(rs.getMetaData().getColumnName(i + 1), value.toString());
}
}
}
list.add(map);
}
if (!cs.getMoreResults()) {
rowsCount = cs.getInt(5);
p_curPage = cs.getInt(6);
}
p_pageCount = (int) Math.ceil((double) rowsCount
/ pageSize);//页数
pageUtils.setCurPage(p_curPage);
pageUtils.setPageCount(p_pageCount);
pageUtils.setRowsCount(rowsCount);
pageUtils.setList(list);
//page.setCurPage(p_curPage);
//page.setPageSize(p_pageSize);
//page.setPageCount(rowsCount);
//page.setDataList(list);
} catch (SQLException e) {
e.printStackTrace();
}finally{
DataSourceUtils.releaseConnection(conn, getSqlMapClient().getDataSource());
}
}