SQL Server存储过程之通用数据分页

接上一篇文章，SQL Server存储过程学习(通俗易懂)。现在编写一个通用数据分页的存储过程。

存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。

---

数据分页功能在后台系统开发中是非常常见的，一般要编写 2 条SQL语句，一条查询总记录数据，一条是获取当前页的数据集（列表）。

分页功能一般有这些参数：总记录数、每页条数（大小）、当前页、总页数（由总记录数和每页条数可以确定）。

说明：执行下面的存储过程，或返回2条结果：一条总记录数据 和 当前页数据集（列表）

分页存储过程代码：

CREATE proc [dbo].[sp_Pager]
@tableName varchar(64),  --分页表名
@columns varchar(1000),  --查询的字段
@order varchar(256),    --排序方式
@pageSize int,  --每页大小
@pageIndex int,  --当前页
@where varchar(2000) = '1=1',  --查询条件
@totalCount int output  --总记录数
as
declare @beginIndex int,@endIndex int,@sqlResult nvarchar(2000),@sqlGetCount nvarchar(2000)
set @beginIndex = (@pageIndex - 1) * @pageSize + 1  --开始
set @endIndex = (@pageIndex) * @pageSize  --结束
set @sqlresult = 'select '+@columns+' from (
select row_number() over(order by '+ @order +')
as Rownum,'+@columns+'
from '+@tableName+' where '+ @where +') as T
where T.Rownum between ' + CONVERT(varchar(max),@beginIndex) + ' and ' + CONVERT(varchar(max),@endIndex)
set @sqlGetCount = 'select @totalCount = count(*) from '+@tablename+' where ' + @where  --总数
--print @sqlresult
exec(@sqlresult)
exec sp_executesql @sqlGetCount,N'@totalCount int output',@totalCount output
--测试调用：
--declare @total int
--exec sp_Pager 'tbLoginInfo','Id,UserName,Success','LoginDate desc',4,2,'1=1',@total output
--print @total

GO

1、测试：使用 SQL Server管理工具

查询第一页数据：

--测试调用：
declare @total int
 exec sp_Pager 'tbOrganization','id,type,name,url','id desc',5,1,'1=1',@total output
print @total

查询第二页数据：

2、测试：在程序中调用存储过程

C#

/// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        public string GetPager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        {
            DataTable dt = AchieveCommon.SqlPagerHelper.GetPager(tableName, columns, order, pageSize, pageIndex, where, out totalCount);
            //dt.Columns.Add(new DataColumn("OrganizationID"));
            //dt.Columns.Add(new DataColumn("OrganizationName"));
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    DataTable dtOrganization = new AchieveBLL.OrganizationBLL().GetOrganizationByXCheckId(Convert.ToInt32(dt.Rows[i]["ID"]));
            //    dt.Rows[i]["OrganizationID"] = AchieveCommon.JsonHelper.ColumnToJson(dtOrganization, 0);
            //    dt.Rows[i]["OrganizationName"] = AchieveCommon.JsonHelper.ColumnToJson(dtOrganization, 1);
            //}
            return AchieveCommon.JsonHelper.ToJson(dt);
        }

 /// <summary>
    /// SQL分页帮助类
    /// </summary>
    public class SqlPagerHelper
    {
        /// <summary>
        /// 获取分页数据（单表分页）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        public static DataTable GetPager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        {
            SqlParameter[] paras = { 
                                   new SqlParameter("@tablename",SqlDbType.VarChar,100),
                                   new SqlParameter("@columns",SqlDbType.VarChar,1000),
                                   new SqlParameter("@order",SqlDbType.VarChar,100),
                                   new SqlParameter("@pageSize",SqlDbType.Int),
                                   new SqlParameter("@pageIndex",SqlDbType.Int),
                                   new SqlParameter("@where",SqlDbType.VarChar,2000),
                                   new SqlParameter("@totalCount",SqlDbType.Int)
                                   };
            paras[0].Value = tableName;
            paras[1].Value = columns;
            paras[2].Value = order;
            paras[3].Value = pageSize;
            paras[4].Value = pageIndex;
            paras[5].Value = where;
            paras[6].Direction = ParameterDirection.Output;   //输出参数

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, CommandType.StoredProcedure, "sp_Pager", paras);
            totalCount = Convert.ToInt32(paras[6].Value);   //赋值输出参数，即当前记录总数
            return dt;
        }

    }

 /// <summary>     
        /// Datatable转换为Json     
        /// </summary>    
        public static string ToJson(DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                StringBuilder jsonString = new StringBuilder();
                jsonString.Append("[");
                DataRowCollection drc = dt.Rows;
                for (int i = 0; i < drc.Count; i++)
                {
                    jsonString.Append("{");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string strKey = dt.Columns[j].ColumnName;
                        string strValue = drc[i][j].ToString();

                        Type type = dt.Columns[j].DataType;
                        jsonString.Append("\"" + strKey + "\":");
                        strValue = StringFormat(strValue, type);
                        if (j < dt.Columns.Count - 1)
                            jsonString.Append(strValue + ",");
                        else
                            jsonString.Append(strValue);
                    }
                    jsonString.Append("},");
                }
                jsonString.Remove(jsonString.Length - 1, 1);
                jsonString.Append("]");
                return jsonString.ToString();
            }
            else
                return "[]";
        }

Java (数据库层使用 Spring JdbcTemplate)

 @Autowired
 private JdbcTemplate jdbcTemplate;


    /**
     * 单位信息
     * 参数： 
        pager 分页对象
        whereSql 查询条件
     */
    public Pager getOrganizationList(Pager pager, String whereSql) throws Exception {

        //exec [dbo].[sp_Pager] 'tbOrganization','*','id desc',10,3,'1=1',@total output
        List<Object> resultList = jdbcTemplate.execute(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection conn) throws SQLException {
                String sql="{call sp_Pager( ? , ? , ? , ? , ? , ? , ?  )}";
                CallableStatement prepareCall = conn.prepareCall(sql);
                prepareCall.setString(1, "tbOrganization");
                prepareCall.setString(2, " id, type, name, url ");
                prepareCall.setString(3, " id desc ");
                prepareCall.setInt(4, pager.getPageSize());
                prepareCall.setInt(5, pager.getPageNumber());
                prepareCall.setString(6, whereSql);
                prepareCall.registerOutParameter(7, Types.INTEGER);
                //prepareCall.registerOutParameter(8, Types.JAVA_OBJECT);
                return prepareCall;
            }
        }, new CallableStatementCallback<List<Object>>() {
            public List<Object> doInCallableStatement(CallableStatement call) throws SQLException {
                call.execute();
                List<Object> resultList = new ArrayList<>();
                List<Map<String, Object>> dataList = new ArrayList<>();
                //获取当前页数据列表（注意存储过程返回结果集 和 总记录数，结果集必须这样处理）
                ResultSet resultSet =  call.getResultSet();
                ResultSetMetaData md = resultSet.getMetaData();
                int columnCount = md.getColumnCount();
                while (resultSet.next()) {
                    Map<String, Object> rowData = new HashMap<String, Object>();
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnName(i), resultSet.getObject(i));
                    }
                    dataList.add(rowData);
                }
                //获取总记录数
                Integer totalCount = call.getInt(7);
                resultList.add(0 , totalCount);
                resultList.add(1 , dataList);
                return resultList;
            }
        });
        if (resultList != null && resultList.size() > 0){
            pager.setTotalCount((Integer) resultList.get(0));
            pager.setList((List<Map<String , Object>>) resultList.get(1));
        }
        return pager;

    }

import java.util.List;
/**
 * 分页对象
 */
public class Pager {
	// 排序方式
	public enum OrderType {
		asc, desc
	}
	public static final Integer MAX_PAGE_SIZE = 500;// 每页最大记录数限制
	private Integer pageNumber = 1;// 当前页码
	private Integer pageSize = 10;// 每页记录数
	private Integer totalCount = 0;// 总记录数
	private Integer pageCount = 0;// 总页数
	private String property;// 查找属性名称
	private String keyword;// 查找关键字
	private String propertiesArr[] = new String[50];// 查找属性名称数组形式
	private String operatorArr[] = new String[50];// 查找关键字数组形式
	private String keywordArr[] = new String[50];// 查找关键字数组形式
	private String orderBy = "createTime";// 排序字段
	private OrderType orderType = OrderType.desc;// 排序方式
	private List list;// 数据List

	public Integer getPageNumber() {
		return pageNumber;
	}

	public void setPageNumber(Integer pageNumber) {
		if (pageNumber < 1) {
			pageNumber = 1;
		}
		this.pageNumber = pageNumber;
	}

	public Integer getPageSize() {
		return pageSize;
	}

	public void setPageSize(Integer pageSize) {
		if (pageSize < 1) {
			pageSize = 1;
		} else if (pageSize > MAX_PAGE_SIZE) {
			pageSize = MAX_PAGE_SIZE;
		}
		this.pageSize = pageSize;
	}

	public Integer getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(Integer totalCount) {
		this.totalCount = totalCount;
	}

	public Integer getPageCount() {
		pageCount = totalCount / pageSize;
		if (totalCount % pageSize > 0) {
			pageCount++;
		}
		return pageCount;
	}

	public void setPageCount(Integer pageCount) {
		this.pageCount = pageCount;
	}

	public String getProperty() {
		return property;
	}

	public void setProperty(String property) {
		this.property = property;
	}

	public String getKeyword() {
		return keyword;
	}

	public void setKeyword(String keyword) {
		this.keyword = keyword;
	}

	public String getOrderBy() {
		return orderBy;
	}

	public void setOrderBy(String orderBy) {
		this.orderBy = orderBy;
	}

	public OrderType getOrderType() {
		return orderType;
	}

	public void setOrderType(OrderType orderType) {
		this.orderType = orderType;
	}

	public List getList() {
		return list;
	}

	public void setList(List list) {
		this.list = list;
	}

	public void setPropertiesArr(String propertiesArr[]) {
		this.propertiesArr = propertiesArr;
	}

	public String[] getPropertiesArr() {
		return propertiesArr;
	}

	public void setOperatorArr(String operatorArr[]) {
		this.operatorArr = operatorArr;
	}

	public String[] getOperatorArr() {
		return operatorArr;
	}

	public void setKeywordArr(String keywordArr[]) {
		this.keywordArr = keywordArr;
	}

	public String[] getKeywordArr() {
		return keywordArr;
	}

}