1.1 mysql分页
SELECT * FROM table LIMIT [offset,] rows
limit offset , rows
第一个参数表示偏移量,第二个参数代表偏移个数
1.2 mysql 分页计算
假设有16条数据,每页数据是5,分页查询的SQL应该如下所示
select * from t_question limit 0, 5
select * from t_question limit 5, 5
select * from t_question limit 10, 5
select * from t_question limit 15, 5
- 偏移量计算
Integer offset = pageSize * (pageNum - 1);
int offset = 5 * (1-1);
int offset = 5 * (2-1);
int offset = 5 * (3-1);
int offset = 5 * (4-1);
pageNum 代表页码,pageSize 代表每页个数
- 总页数计算
totalPage = (total + pageSize - 1) / pageSize;
1.3 构建显示的导航页码范围
-
假设总页数有3页,而要显示的页码范围是 7,实际显示应为:
1 2 3
-
如果总页数为10页, 大于页面范围,这时需要根据当前页去确定范围
一般情况下, 都会将当前页作为最中间的那一项。
-
7 / 2 = 3 意思是左右两边各三个,假设当前页为1
-
计算 startNum :1 - 3 = -2
-
计算endNum: 1 + 3 = 4
-
startNum = -2 < 1,所以开始页码应该为 1, 结束页码为 7
-
-
假设当前页为 10
-
计算 startNum :10 - 3 = 7
-
计算endNum: 10 + 3 = 13
-
结束页码为 13 > 10, 所以结束页码设置为 10, 开始页码为:10 - 7 - 1 = 6
-
-
假设当前页为 4
-
计算 startNum :4 - 3 = 1
-
计算endNum: 4 + 3 = 7
-
1.4 代码实现
package com.example.demo.dto;
import lombok.Data;
import java.util.ArrayList;
import java.util.List;
@Data
public class PaginationDTO<T> {
// 分页数据
private List<T> data;
// 总页数
private int totalPage;
// 当前页
private Integer currentPage;
// 每页显示记录数
private int pageSize;
// 是否显示上一页
private boolean hasPrevious;
// 是否显示下一页
private boolean hasNext;
// 是否是第一页
private boolean isFirstPage;
// 是否是最后一页
private boolean isLastPage;
//导航页码数
private int navigatePages = 7;
// 显示的页号
List<Integer> pageNums = new ArrayList<>();
// 逻辑处理
public void setPagination(int total, Integer pageNum, Integer pageSize) {
// 总页数
totalPage = (total + pageSize - 1) / pageSize;
// 页码校验
if (pageNum < 1) {
currentPage = 1;
} else if (pageNum > totalPage) {
currentPage = totalPage;
} else {
this.currentPage = pageNum;
}
// 构建显示的页号范围
buildPageNums();
isFirstPage = currentPage == 1;
isLastPage = currentPage == totalPage;
hasPrevious = currentPage > 1;
hasNext = currentPage < totalPage;
}
/**
* 构建导航页
*/
private void buildPageNums() {
// 如果总页数小于或等于导航页码
if (totalPage < navigatePages) {
addNums(1, totalPage);
} else {
int startNum = currentPage - navigatePages / 2;
int endNum = currentPage + navigatePages / 2;
if (startNum < 1) {
addNums(1, navigatePages);
} else if (endNum > totalPage) {
endNum = totalPage;
startNum = endNum - navigatePages + 1;
addNums(startNum, endNum);
} else {
addNums(startNum, endNum);
}
}
}
/**
* 增加页码范围
* @param start 开始页
* @param count 增加个数
*/
private void addNums(int start, int count) {
for (int i = start; i <= count; i++) {
pageNums.add(i);
}
}
/**
* 设置偏移量
*/
public int setOffset(Integer pageNum, Integer pageSize) {
if (pageNum < 1) {
return 0;
}
return pageSize * (pageNum - 1);
}
}
- controller
/**
* 跳转首页
*/
@RequestMapping("index")
public String index(@RequestParam(name = "page", defaultValue = "1") Integer pageNum,
@RequestParam(name = "pageSize", defaultValue = "5") Integer pageSize,
@RequestParam(name = "search", required = false) String search, Model model) {
PaginationDTO<Question> pagination = questionService.getQuestions(search, pageNum, pageSize);
model.addAttribute("pagination", pagination);
model.addAttribute("search", search);
return "index";
}
- 实现类
@Override
public PaginationDTO<Question> getQuestions(String search, Integer pageNum, Integer pageSize) {
PaginationDTO<Question> pagination = new PaginationDTO<>();
if (StringUtils.isNotBlank(search)) {
search = search.trim();
}
// 设置偏移量
int offset = pagination.setOffset(pageNum, pageSize);
List<Question> questionList = baseMapper.getQuestions(search, offset, pageSize);
if (CollectionUtils.isEmpty(questionList)) {
return pagination;
}
// 总数
int total = baseMapper.getQuestionCount(search);
pagination.setPagination(total, pageNum, pageSize);
pagination.setData(questionList);
return pagination;
}
- dao
/**
* 首页文章数据
*/
List<Question> getQuestions(@Param("search") String search, @Param("offset") int offset, @Param("pageSize") int pageSize);