【JavaWeb】19-分页查询的实现

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&currentPage=1&pageSize=${ pager.pageSize }">首页</a>
			<a href="${ bp }/post?method=page&currentPage=${ 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&currentPage=${ i }&pageSize=${ pager.pageSize }">${ i }</a>
			</c:if>
		</c:forEach>
		<c:if test="${ pager.currentPage lt pager.totalPages }">
			<a href="${ bp }/post?method=page&currentPage=${ pager.currentPage + 1 }&pageSize=${ pager.pageSize }">下一页</a>
			<a href="${ bp }/post?method=page&currentPage=${ 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>