SQL Server分页查询

注：SQL Server分页查询的SQL来源于：http://blog.csdn.net/qiaqia609/article/details/41445233

后根据项目需要，做过一定的修改。

 

1、用到的分页存储过程：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--分页存储过程  
CREATE PROCEDURE [dbo].[sp_Paging] 
( 
@Tables nvarchar(1000),                --表名/视图名
@PrimaryKey nvarchar(100),             --主键
@Sort nvarchar(200) = NULL,            --排序字段(不带order by)
@pageindex int = 1,                    --当前页码
@PageSize int = 10,                    --每页记录数
@Fields nvarchar(1000) = N'*',         --输出字段
@Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)
@Group nvarchar(1000) = NULL          --Group语句(不带Group By)
) 
AS   
DECLARE @SortTable nvarchar(100) 
DECLARE @SortName nvarchar(100) 
DECLARE @strSortColumn nvarchar(200) 
DECLARE @operator char(2) 
DECLARE @type nvarchar(100) 
DECLARE @prec int 

--设定排序语句
IF @Sort IS NULL OR @Sort = ''     
   SET @Sort = @PrimaryKey      
IF CHARINDEX('DESC',@Sort)>0   
BEGIN         
    SET @strSortColumn = REPLACE(@Sort, 'DESC', '')         
    SET @operator = '<='     
END 
ELSE     
BEGIN                
    SET @strSortColumn = REPLACE(@Sort, 'ASC', '')                
    SET @operator = '>='     
END 
IF CHARINDEX('.', @strSortColumn) > 0     
BEGIN         
    SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))     
END 
ELSE     
BEGIN         
    SET @SortTable = @Tables         
    SET @SortName = @strSortColumn  
END 

--设置排序字段类型和精度 
SELECT @type=t.name, @prec=c.prec FROM sysobjects o 
       JOIN syscolumns c on o.id=c.id 
       JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName
        
IF CHARINDEX('char', @type) > 0    
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
   
DECLARE @strPageSize nvarchar(50) 
DECLARE @strStartRow nvarchar(50) 
DECLARE @strFilter nvarchar(1000) 
DECLARE @strSimpleFilter nvarchar(1000) 
DECLARE @strGroup nvarchar(1000)  
 
IF @pageindex <1     
   SET @pageindex = 1  
SET @strPageSize = CAST(@PageSize AS nvarchar(50)) 
--设置开始分页记录数 
SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  
--筛选以及分组语句
IF @Filter IS NOT NULL AND @Filter != ''     
BEGIN         
    SET @strFilter = ' WHERE ' + @Filter + ' ' 
    SET @strSimpleFilter = ' AND ' + @Filter + ' ' 
END 
ELSE     
BEGIN         
    SET @strSimpleFilter = ''         
    SET @strFilter = ''   
	SET @strGroup='';  
END 
IF @Group IS NOT NULL AND @Group != ''  
   SET @strGroup = ' GROUP BY '+@Group; 
/*
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
*/
--执行查询语句    
declare @sql varchar(2000);
declare @sql2 varchar(1000);
declare @sql3 varchar(1000);
declare @sql4 varchar(1000);
declare @sql5 varchar(1000);
set @sql = 'DECLARE @SortColumn ' + @type+';';
set @sql2 = 'SET ROWCOUNT ' + @strStartRow+';';
/*
print '@strSortColumn='+@strSortColumn;
print '@Tables:'+@Tables;
print '@strFilter:'+@strFilter;
print '@strGroup:';
print '@Sort:'+@Sort;
*/
set @sql3='SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ORDER BY ' + @Sort+';';
set @sql4='SET ROWCOUNT ' + @strPageSize+';';
set @sql5='SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ORDER BY ' + @Sort + ''+';';

/*
print 'sql:'+@sql;
print 'sql2:'+@sql2;
print 'sql3:'+@sql3;
print 'sql4:'+@sql4;
print 'sql5:'+@sql5;
*/
set @sql  = @sql+@sql2+@sql3+@sql4+@sql5;
print @sql;
EXEC(@sql)

2、使用Java进行调用：

/**
 * 该方法用来加载基础数据
 * @param userID:当前登陆人登陆ID，若为0表示加载全部
 * @param curPage:当前为多少页
 * @param pageSize:每页显示数量
 * @param sort:排序  1-默认按照ID顺序排 2-日期 3-地区
 * @param area:地区
 * @param startTime:开始时间
 * @param endTime:结束时间
 * @return List<Table2>:当前登陆人的全部基础信息
 * **/
public List<Table2> loadBaseInfo(String userID,int curPage,int pageSize,
		String sort,String area,String startTime,String endTime){
	int u_id = 0;
	try{
		u_id = Integer.parseInt(userID);
	}catch(Exception e){
		return null;
	}
	List<Table2> table2List = null;
	StringBuffer sql = new StringBuffer();
	StringBuffer where = new StringBuffer();
		
	//地区不为空 且 日期在去年12月到今年的数据
	where.append("len(field97)>0 and (year(field98)=year(getDate()) or (year(field98)=(year(getDate())-1) and month(field98)=12))");
	if(u_id>0){//获取当前人的数据
		where.append(" and field19="+userID);
	}
	
	//排序
	String orderBy = "field1 desc"; 
	int order = Integer.parseInt(sort);
	switch(order){
		case 2:orderBy="field24 desc";break;
		case 3:orderBy="field97 asc";break;
		default:break;
	}
	//地区
	if(area!=null && area.trim().length()>0){
		where.append(" and field97 like ''%"+area+"%''");
	}
	
	//时间
	if(startTime!=null && startTime.trim().length()>0 && endTime!=null && endTime.trim().length()>0){
		startTime = startTime+" 00:00:00.000";
		endTime = endTime + " 23:59:59.000";
		where.append(" and field7 between ''"+startTime+"'' and ''"+endTime+"'' ");
	}
	int size=0;
	try{
		//判断是否要返回总数
		if(curPage==1){//说明是第一页，那么需要返回总页数
			//replaceAll方法处理拼接like和between时特意添加的两个单引号''，因为若是用一个单引号，调用分页查询的存储过程时会出现问题。此处若不处理，SQL必然报错。
			sql.append("select count(field1) from Table2 where "+where.toString().replaceAll("''", "'"));
			size = execCountSQL(sql.toString());
			if(size<=0){//没有总数，那么不需要再往下查询了
				//写日志
				writeLoggerForSQL("根据登录人序号(序号："+userID+",若为0表示加载全部)获取基础信息", sql.toString(),0+"");
				return null;
			}
		}
		table2List = new ArrayList<Table2>();
		Table2 t2 = null;
		sql.delete(0,sql.length());
		sql.append(getPagingSQL("table2", "field1", orderBy, curPage, pageSize, "*", where.toString(), null));
		System.out.println("第"+curPage+"页；执行的SQL："+sql);
		ResultSet rs = execQuery(sql.toString());
		while(rs.next()){
			t2 = new Table2(
					rs.getInt(1),
					rs.getString(2),
					rs.getString(3),
					rs.getString(4)
			);
			table2List.add(t2);
		}
		if(curPage==1){
			table2List.add(new Table2(curPage,pageSize,size));
		}
		//写日志
		writeLoggerForSQL("根据登录人序号(序号："+userID+",若为0表示加载全部)获取基础信息", sql.toString(),((table2List!=null && table2List.size()>0)?table2List.size():0)+"");
		
	}catch(Exception e){
		//将异常信息写入日志文件
		writeLoggerForException("根据登录人序号(序号："+userID+",若为0表示加载全部)获取基础信息", sql.toString(), e);
		e.printStackTrace();
	}
	return table2List;
}


/**
 * 该方法用来拼接使用存储过程进行分页的SQL语句,
 * 其中tableName、primaryKey两个不能为空
 * @param tableName:表名称
 * @param primaryKey:主键字段
 * @param Sort:排序字段，不需要传order by关键字，默认使用主键顺序排列
 * @param pageIndex:当前页
 * @param pageSize:每页显示数量
 * @param fields:查询字段，默认为*
 * @param filter:查询条件，不需要传where关键字
 * @param group:分组字段，不需要传group by关键字
 * @param 
 * **/
private String getPagingSQL(String tableName,String primaryKey,String sort,
		int pageIndex,int pageSize,String fields,String filter,String group){
	//表名称和主键名称不能为空
	if(tableName==null || tableName.trim().length()<=0 || 
		primaryKey==null || primaryKey.trim().length()<=0){return null;}
	
	//为其他添加默认值
	if(sort==null || sort.trim().length()<=0){sort="NULL";}else{sort="'"+sort+"'";}
	if(pageIndex<=0){pageIndex=1;}
	if(pageSize<=0){pageSize=5;}
	if(fields==null || fields.trim().length()<=0){fields="*";}
	if(filter==null || filter.trim().length()<=0){filter="NULL";}else{filter="'"+filter+"'";}
	if(group==null || group.trim().length()<=0){group="NULL";}else{group="'"+group+"'";}
	
	StringBuffer sql = new StringBuffer();
	sql.append("DECLARE @return_value int;")
	.append("EXEC @return_value = [dbo].[sp_Paging] ")
	.append("@Tables = N'"+tableName+"',")
	.append("@PrimaryKey = N'"+primaryKey+"',")
	.append("@Sort = "+sort+",")
	.append("@pageindex = "+pageIndex+",")
	.append("@PageSize = "+pageSize+",")
	.append("@Fields = '"+fields+"',")
	.append("@Filter = "+filter+",")
	.append("@Group = "+group+"");
	return sql.toString();
}