接上一篇文章，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
GO1、测试：使用 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;
}
}