数据库查询中的分页和排序方案

分页和排序是数据库提供的基本功能之二。

以MySQL为例，一条典型的SQL查询语句如下：

SELECT * FROM t_user
ORDER BY create_time DESC, username ASC
LIMIT 10 OFFSET 20

那么在前后端交互中，前端应该如何向后端传递分页和排序有关的信息呢？需要传递哪些参数？参数的意义和格式又是什么？

分页

分页的语句为LIMIT 10 OFFSET 20，其中10为每页的大小，20为查询的偏移量，也就是查询表中的第21到30条共计10条数据。

但是在设计接口时，通常不会要求前端直接传入OFFSET参数，而是通过传入页号和分页大小，来计算出OFFSET的值。

在这个例子中，假设前端的页号从1开始，那么需要查询的数据就是分页大小为10的第3页数据，对应的OFFSET的值的计算式为(3-1)*10 = 20。

前端在调用后端查询接口时，通常会传入页号和每页大小，由后端计算出OFFSET用于分页查询。这里页号的参数名称定义为pageNumber，分页大小的参数名定义为pageSize, 则以起始页号为1为例，OFFSET的计算公式为：

(pageNumber - 1) * pageSize

而前端的传值应为

?pageNumber=3&pageSize=10

分页接口

不同的数据库的分页语句并不一致，但是一般都需要三个参数：SQL语句，分页大小，偏移量，所以定义一个构建分页语句的接口Dialect，根据访问的数据库提供对应的分页实现。

public interface Dialect {
    String buildPageSql(String sql, int limit, long offset);
}

排序

排序的语句为ORDER BY create_time DESC, username ASC, 其中ORDER BY为SQL的关键字，可以将其定义为参数名。因为是用于排序，所以我将其取名为sort，对应的值为create_time DESC, username ASC。因为前端GET请求需要对参数值里的空格进行转义，为避免这个问题，将值里的,转为;，将DESC/ASC前的空格转为,，最终前端传值如下：

?sort=create_time,desc;username,asc

请求对象

综合以上分页和排序的参数定义和说明，前端的传值如下：

?pageNumber=3&pageSize=10&sort=create_time,desc;username,asc

那么后端就可以定义如下PageQuery类用于分页和排序参数的处理：

public class PageQuery {
    private Integer pageNumber;
    private Integer pageSize;
    private String sort;
}

每条查询都有进行显示或隐式的分页和排序，比如：

SELECT * FROM t_user

等价于

SELECT * FROM t_user
ORDER BY id ASC
LIMIT ∞ OFFSET 0

所以PageQuery应当作为所有查询对象的父类，以便为数据查询提供分页和排序的能力。

响应对象

对于前端的分页查询请求，除了返回对应的数据列表外，还需要返回总的数据条数total以帮助前端计算总页数，计算公式为⌈total/size⌉。对应的响应对象定义如下：

public class PageList<T> {
    private final List<T> list;
    private final long total;
}

小结

本篇主要介绍了数据库查询中有关分页和排序的一种通用的解决方案，就这。

DOYToWin