1. 定义分页实体类Pager
@Data
//写一个泛型用来包装真实数据
public class Pager<E> {
//当前页
private Long currentPage;
//每页显示数据量
private Long pageSize;
//数据表中总数据量
private Long totalRows;
//页面显示页码总量
private Long pageNumShowLength;
//总页数 totalRows / pageSize
private Long totalPages;
//数据库分页查询的开始索引 (currentPage - 1) * pageSize
private Long startIndex;
//页面显示的开始页码
private Long startPage;
//页面显示的结束页码
private Long endPage;
//数据集合
private List<E> dataList;
public Pager(Long currentPage, Long pageSize, Long totalRows, Long pageNumShowLength) {
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalRows = totalRows;
//计算总页数
if(this.totalRows % this.pageSize == 0) {
this.totalPages = this.totalRows / this.pageSize;
} else {
this.totalPages = this.totalTows / this.pageSize + 1;
}
//判断currentPage合法性
if(this.currentPage > this.totalPages) {
this.currentPage = this.totalPages;
}
//计算数据库分页开始索引
this.startIndex = (this.currentPage - 1) * this.pageSize;
//计算页面显示的起始页码和结束页码
if(this.totalPages <= pageNumShowLength) {
this.startPage = 1L;
this.endPage = this.totalPages;
} else if (this.currentPage <= (pageNumShowLength / 2 + 1)) {
this.startPage = 1L;
if(this.totalPages <= pageNumShowLength) {
this.endPage = this.totalPages;
} else {
this.endPage = pageNumShowLength;
}
} else {
if (pageNumShowLength % 2 == 0) {
if (this.totalPages <= (this.currentPage + (pageNumShowLength / 2) - 1)) {
this.startPage = this.totalPages - pageNumShowLength + 1;
this.endPage = this.totalPages;
} else {
this.startPage = this.currentPage - (pageNumShowLength / 2);
this.endPage = this.currentPage + (pageNumShowLength / 2) - 1;
}
} else {
if (this.totalPages <= (this.currentPage + (pageNumShowLength / 2))) {
this.startPage = this.totalPages - pageNumShowLength + 1;
this.endPage = this.totalPages;
} else {
this.startPage = this.currentPage - (pageNumShowLength / 2);
this.endPage = this.currentPage + (pageNumShowLength / 2);
}
}
}
}
}
Dao层
public Pager<Post> selectPage(Long currentPage, Long pageSize, Long pageNumShowLength) throws SQLException {
//获取总数据量
Long totalRows = count();
//初始化pager对象
Pager<Post> pager = new Pager<><currentPage, pageSize, totalRows, pageNumShowLength);
//分页查询
QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
String sql = "select junki_post.*,junki_user.nickname,count(junki_comment.id) as comment_count from junki_post left join junki_comment on junki_post.id = junki_comment.post_id, junki_user where junki_post.user_id = junki_user.id group by junki_post.id limit ?,?";
List<Post> postList = qr.query(sql, new BeanListHandler<>(Post.class), pager.getStartIndex(), pager.getPageSize());
// 将数据封装到pager对象
pager.setDataList(postList);
return pager;
}
public Long count() throws SQLException {
QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
String sql = "select count(*) from post;";
Long count = (Long)qr.query(sql, new ScalarHandler<>(1));
return count;
}
前端
<div id="content">
<table border="1" cellpadding="5px" cellspacing="0">
<tr>
<th>序号</th>
<th>标题</th>
<th>作者</th>
<th>类别</th>
<th>摘要</th>
<th>时间</th>
<th>评论数</th>
</tr>
<c:forEach items="${ pager.dataList }" var="post" varStatus="vs">
<tr>
<td>${ vs.count }</td>
<td><a href="${ bp }/post?method=get&id=${ post.id }">${ post.title }</a></td>
<td>${ post.nickname }</td>
<td>${ PostTypeEnum.values()[post.type].getDesc() }</td>
<td>${ post.summary }</td>
<td><fmt:formatDate value="${ post.create_time }" pattern="yyyy年MM月dd日 HH点mm分"/></td>
<td>${ post.comment_count }</td>
</tr>
</c:forEach>
</table>
<div id="paging">
<c:if test="${ pager.currentPage gt 1 }">
<a href="${ bp }/post?method=page¤tPage=1&pageSize=${ pager.pageSize }">首页</a>
<a href="${ bp }/post?method=page¤tPage=${ pager.currentPage - 1 }&pageSize=${ pager.pageSize }">上一页</a>
</c:if>
<c:forEach begin="${ pager.startPage }" end="${ pager.endPage }" step="1" var="i">
<c:if test="${ i eq pager.currentPage }">
${ i }
</c:if>
<c:if test="${ i ne pager.currentPage }">
<a href="${ bp }/post?method=page¤tPage=${ i }&pageSize=${ pager.pageSize }">${ i }</a>
</c:if>
</c:forEach>
<c:if test="${ pager.currentPage lt pager.totalPages }">
<a href="${ bp }/post?method=page¤tPage=${ pager.currentPage + 1 }&pageSize=${ pager.pageSize }">下一页</a>
<a href="${ bp }/post?method=page¤tPage=${ pager.totalPages }&pageSize=${ pager.pageSize }">尾页</a>
</c:if>
<span>
共${ pager.totalPages }页
</span>
<form action="${ bp }/post?method=page" method="post" style="display:inline-block;">
每页
<select name="pageSize">
<option value="5" ${ pager.pageSize eq 5 ? "selected='selected'" : "" }>5</option>
<option value="10" ${ pager.pageSize eq 10 ? "selected='selected'" : "" }>10</option>
<option value="20" ${ pager.pageSize eq 20 ? "selected='selected'" : "" }>20</option>
</select>
条
转到
<input type="number" name="currentPage" value="${ pager.currentPage }" style="width:40px">
页
<input type="submit" value="跳转">
</form>
</div>
</div>