分页查询

网页中显示表中所有数据，当数据过多时，采用分页查询，一页只显示少量数据。可以跳转到首页或末页，可以直接跳转到某一页。

实体类

/**
 * 查询列表分页显示
 */
public class Page {
    private Integer pageNum; //当前第几页
    private Integer pageSize; //每页几条
    private Integer totalCount; //总共几条
    private Integer totalPages; // 总共几页
    private Integer startRows; //从第几行开始

    public Page() {
    }

    public Page(Integer pageNum, Integer pageSize) {
        this.setPageNum(pageNum);
        this.setPagesize(pageSize);
        this.startRows = (this.pageNum - 1) * this.pageSize;
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        if(pageNum == null || pageNum < 0){
            pageNum = 1;
        }
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPagesize(Integer pageSize) {
        //当pageSize过大过小时，默认为2
        if(pageSize == null || pageSize < 0 || pageSize > 5){
            pageSize = 2;
        }
        this.pageSize = pageSize;
    }

    public Integer getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
        //计算总页数
        if(this.totalCount % this.pageSize == 0){
            this.totalPages = this.totalCount / this.pageSize;
        }else {
            this.totalPages = this.totalCount / this.pageSize + 1;
        }
    }

    public Integer getTotalPages() {
        return totalPages;
    }

    public void setTotalPages(Integer totalPages) {
        this.totalPages = totalPages;
    }

    public Integer getStartRows() {
        return startRows;
    }

    public void setStartRows(Integer startRows) {
        this.startRows = startRows;
    }
}

---

Dao层，操作数据库

    /**
     * 查询所有书籍，并分页显示
     * @param offset 从第几行开始
     * @param pageSize 每页几条
     * @return
     */
    public List<Book> getBookList(Integer offset,Integer pageSize){
        String sql = "select id,name,author,publish,publish_date publishDate,borrower from book limit ?,?";
        try {
            return queryRunner.query(sql,new BeanListHandler<Book>(Book.class),offset,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查找共有多少条数据
     * @return
     */
    public long selectCount(){
        String sql = "select count(*) from book";
        try {
            return queryRunner.query(sql,new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

---

service层

    /**
     * 查询所有数据
     * @param page
     * @return
     */
    public List<Book> getBookList(Page page){
        BookDao bookDao = new BookDao();
        long count = bookDao.selectCount();
        page.setTotalCount((int) count);
        return bookDao.getBookList(page.getStartRows(),page.getPageSize());
    }

---

contrallor层

import entity.Book;
import entity.Page;
import service.BookService;
import util.ParameterUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet(name = "BookListServlet", urlPatterns = "/booklist")
public class BookListServlet extends HttpServlet {
    BookService bookService = new BookService();
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Integer pageNum = ParameterUtil.getInteger(request,"pn");//页码
        Integer pageSize = ParameterUtil.getInteger(request,"ps");//每页几条
        Page page = new Page(pageNum,pageSize);
        List<Book> bookList = bookService.getBookList(page);
        request.setAttribute("booklist",bookList);
        request.setAttribute("page",page);
        request.getRequestDispatcher("booklist.jsp").forward(request,response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}

---

前端JSP页面

<!--数据列表代码省略-->
            <a href="booklist?pn=1">首页</a>&nbsp;&nbsp;
            <%--第1页没有上一页--%>
            <c:if test="${requestScope.page.pageNum gt 1}">
                <a href="booklist?pn=${requestScope.page.pageNum-1}">上一页</a>&nbsp;&nbsp;
            </c:if>
            <c:forEach begin="1" end="${requestScope.page.totalPages}" varStatus="i">
                <a href="booklist?pn=${i.count}">第${i.count}页</a>&nbsp;
            </c:forEach>
            <%--最后一页没有下一页--%>
            <c:if test="${requestScope.page.pageNum lt requestScope.page.totalPages}">
                <a href="booklist?pn=${requestScope.page.pageNum+1}">下一页</a>&nbsp;&nbsp;
            </c:if>
            <a href="booklist?pn=${requestScope.page.totalPages}">末页</a>&nbsp;&nbsp;
            总共${requestScope.page.totalCount}条 / 共${requestScope.page.totalPages}页<br/>
            当前第${requestScope.page.pageNum}页 / 每页${requestScope.page.pageSize}条

---

效果