欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

分页查询

程序员文章站 2022-06-21 15:08:38
...

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

实体类
/**
 * 查询列表分页显示
 */
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}条

效果

分页查询

相关标签: 工具代码