使用传入的总记录数实现一条sql语句完成分页查询

时间:2022-03-27 13:17:56

使用传入的总记录数实现一条sql语句完成分页查询

问题:在传统的分页查询的实现中不可避免的需要两条sql语句,一条用于查询数据一条用于查询总记录数。如下面的实际代码所示:

Img1使用传入的总记录数实现一条sql语句完成分页查询

当然如果使用存储过程的话是可以渐少一次数据库来回的,但是它依然是两条sql语句,一条查数据一条查总记录数。但我们在架构层面拒绝使用存储过程,因为它将逻辑分散在了应用程序和数据库中,不好维护,并且在SqlServer2008中也没有足够的证据证明存储过程更高效。

很多人的直觉认为查询总记录数的那行没有必要,但是直觉只是第一步,项目中需要的是解决方案。

解决方案:

从客户端输入总记录数,从而避免一次对总记录数的查询。首先将分页查询的参数封装为一个简单对象PagingInput,它属于模型分类中的输入模型类别所以以Input作为命令的后缀,其类图如下:

Img2使用传入的总记录数实现一条sql语句完成分页查询

pagingInput的代码


 
namespace jDTS {
/// <summary>
/// 分页输入参数
/// </summary>
public class PagingInput {
/// <summary>
/// 构造分页参数
/// </summary>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页尺寸</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortOrder">排序方向</param>
public PagingInput(int pageIndex, int pageSize, string sortField, string sortOrder) {
this.pageIndex = pageIndex;
this.pageSize = pageSize;
this.sortField = sortField;
this.sortOrder = sortOrder;
}
/// <summary>
/// 构造分页参数
/// </summary>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页尺寸</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortOrder">排序方向</param>
/// <param name="total">总记录数</param>
public PagingInput(int pageIndex, int pageSize, string sortField, string sortOrder, int? total)
: this(pageIndex, pageSize, sortField, sortOrder) {
this.total = total;
}
/// <summary>
/// 页索引。零基索引,即第一页对应0
/// </summary>
public int pageIndex { get; set; }
/// <summary>
/// 页尺寸
/// </summary>
public int pageSize { get; set; }
/// <summary>
/// 排序字段
/// </summary>
public string sortField { get; set; }
/// <summary>
/// 排序方向
/// </summary>
public string sortOrder { get; set; }
/// <summary>
/// 归档数据有一个特性,就是它的总记录数是不改变的,所以传入total以渐少一次数据库count查询
/// </summary>
public long? total { get; set; }
/// <summary>
/// 查看total字段是否为空或者0
/// </summary>
public bool IsTotalNullOrZero { get { return !total.HasValue || total.Value == 0; } }
/// <summary>
/// pageSize * pageIndex的计算值
/// </summary>
public int SkipCount { get { return pageSize * pageIndex; } }
/// <summary>
/// 判断分页输入参数是否合法
/// </summary>
/// <returns></returns>
public bool IsValid() {
if (string.IsNullOrEmpty(sortField) || string.IsNullOrEmpty(sortOrder)) {
throw new jDTSException("排序是必须的");
}
if (sortOrder != "asc" && sortOrder != "desc") {
throw new jDTSException("排序方向只能是asc或desc,小写形式");
}
return true;
}
}
}

将GetPlistNodeELementPermissionTrs的与分页有关的形参替换为PagingInput类,重构后的代码如下:

Img3使用传入的总记录数实现一条sql语句完成分页查询

相应的表现层的代码也需要做调整,从表现层收集来自界面层输入的total参数,注意total是可空的类型(int?)所以界面层可以选择是否传入:

Img4使用传入的总记录数实现一条sql语句完成分页查询

现在服务端代码已经实现了一个接收可空的total参数的契约,如果本模块确实要使用这个一次查询机制的话则只需在客户端传入total参数即可。像下面这样在beforload事件中附加一个total参数:这里使用的是国产的miniui框架,成熟的js表现层框架都有类似的事件

Img5使用传入的总记录数实现一条sql语句完成分页查询

另外要记得在前台的js方法search中将total置为0,因为search不是翻页,调用search即表示查询条件有变化,查询条件变化则总记录数通常会变化,所以需将total置为0表示让服务器端进行count查询(还记得前文的PagingInput模型上有个只读的IsNullOrZero属性吗?服务器端就是根据它来判断是否要count一次数据库的)。下面是归档模块中的一个search方法,它在每次search的时候都将传向服务器端的total参数置为0:

Img6使用传入的总记录数实现一条sql语句完成分页查询

适用场景:

适用这个解决方案的场景并不常见,因为它要求查询的总记录数是固定的或者是不频繁改变的,在我们的项目中目前发现有两个场景适用:1查询归档档案(Record),因为数据归档后它的总记录数是不变的;2查询本人的登录记录(VisitingLog),因为自己的登录次数在下次登录的时候才会改变。

当然如果界面上配上一个刷新按钮,刷新的时候将传向服务器端的total参数置为0的话,增删改记录不频繁的模块也是可以这么干的。

分类: ASP.NET MVC