分页查询详解

一、例题

二、分页查找的SQL语句

select * from 表名 limit 0,20;//第一页
select * from 表名 limit 20,20;//第二页
select * from 表名 limit 40,20;//第三页
1
2
3

三、要解决的问题

3.1

对于Sql语句，我们在Service层要给queryRunner.query()方法的参数就是limit后面的两个数字，分别代表从第几行开始，查多少行
但是例题中我们是通过上一页，下一页的a标签来赋值的
所以，要通过点击+1的操作，+1的操作是对第几页进行的，queryRunner.query()要给的参数却是第几行，查多少行
解决办法：
定义一个Page实体类，通过Page的pageIndex（页码）属性，计算出第几行
通过Page的pageSize（页大小）属性，定义查多少行
通过Page的pageStartRow（起始行）属性，定义第几行
例如：点击a标签后,页码pageIndex+1，要显示下一页，我们通过计算可得
要显示的第几行pageStartRow =（pageIndex-1）* pageSize，
例如要显示第三页，即pageIndex = 3，假设一次显示5行数据pageSize = 5，我们通过计算可得
要显示的第几行pageStartRow = （ 3 - 1 ）*5 = 10,插入到sql语句里面

select * from 表名 limit 10,5;//第三页
1

3.2

当我们点击下一页到了最后一页的时候，后面没有数据了，要设置a标签不能再点击
同样的上一页到了pageIndex = 1后也不能点击
所以在Page实体类中还有定义一个属性totalPage（总页码）
然而总页码可以通过查询总行数来计算，所以再定义一个属性totalRows（总行数）
通过totalPage的set方法，当totalRows被赋值时，直接给totalPage赋值

public void setTotalRows(Integer totalRows) {
        this.totalRows = totalRows;
        this.setTotalPage(totalRows % pageSize == 0 ? totalRows / pageSize : totalRows / pageSize + 1);

    }
1
2
3
4
5

3.3 Page实体类

package com.qf.project.entity;

public class Page {
    private Integer pageStartRow;
    private Integer pageIndex;
    private Integer pageSize;
    private Integer totalRow;
    private Integer totalPages;

    public Page(Integer pageIndex) {
        this.pageIndex = pageIndex;
        this.pageSize = 5;
        this.setPageStartRow((pageIndex - 1) * 5);
    }

    public Page(Integer pageIndex, Integer pageSize) {
        this.pageIndex = pageIndex;
        this.pageSize = pageSize;
        this.setPageStartRow((pageIndex - 1) * pageIndex);
    }

    public Integer getPageStartRow() {
        return pageStartRow;
    }

    public void setPageStartRow(Integer pageStartRow) {
        this.pageStartRow = pageStartRow;
    }

    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(Integer totalRow) {
        this.totalRow = totalRow;
        this.setTotalPages(totalRow % pageSize == 0 ? totalRow / pageSize : totalRow / pageSize + 1);
    }

    public Integer getTotalPages() {
        return totalPages;
    }

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

Dao层

   public List<Product> selectAll(Page page) {
        List<Product> productList = new ArrayList<>();
        try {
            productList = queryRunner.query(DbUtils.getConnection(),"select *from product limit ?,?",new BeanListHandler<Product>(Product.class),page.getPageStartRows(),page.getPageSize());
            return productList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
1
2
3
4
5
6
7
8
9
10

Service层

    public List<Product> showAll(Page page) {
        List<Product> productList = new ArrayList<>();
        try {
            DbUtils.begin();
            long t = productDao.selectCount();
            page.setTotalRows((int)t);
            productList = productDao.selectAll(page);

            DbUtils.commit();
        } catch (Exception e) {
            DbUtils.rollback();
            e.printStackTrace();
        }

        return productList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

ShowAllProductController

@WebServlet(name = "ShowAllProductController", value = "/admin/safe/showAllProductController")
public class ShowAllProductController extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String pageIndex = request.getParameter("pageIndex");
        if (pageIndex == null) {
            pageIndex = "1";
        }
        Page page = new Page(Integer.valueOf(pageIndex));
        ProductService productService = new ProductServiceImpl();
        List<Product> productList = productService.showAll(page);
        request.setAttribute("products", productList);
        request.setAttribute("page", page);
        request.getRequestDispatcher("/admin/safe/showAllProduct.jsp").forward(request, response);
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

五、ShowAllProduct.jsp

<%@ page import="text.frist.Emp.entity.Product" %>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
</head>
<body>

<table border="1">
    <tr>
        <td>商品ID</td>
        <td>商品名</td>
        <td>商品价格</td>
        <td>商品生产日期</td>
        <td>删除商品</td>
        <td>增加商品</td>
    </tr>
    <c:forEach var="p" items="${products}">
        <tr>
            <td>${p.id}</td>
            <td>${p.name}</td>
            <td>${p.price} </td>
            <td>${p.firstdate} </td>
            <td>
                <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/removeProductController?id=${p.id}'></c:url>">删除</a>
            </td>
            <td>
                <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/addProductInfo.jsp'></c:url>">增加</a>
            </td>
        </tr>
    </c:forEach>
    <tr>
        <td colspan="6">
            <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/showAllProductController?pageIndex=1'></c:url>">首页</a>&nbsp;
            &nbsp;
            <c:if test="${page.pageIndex > 1}">
                <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/showAllProductController?pageIndex=${page.pageIndex - 1}'></c:url>">上一页</a>&nbsp;
            </c:if>
            <c:if test="${page.pageIndex == 1}">
                <a>上一页</a>
            </c:if>
            <c:if test="${page.pageIndex < page.totalPage }">
            <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/showAllProductController?pageIndex=${page.pageIndex + 1}'></c:url>">下一页</a>&nbsp;
            </c:if>
            <c:if test="${page.pageIndex == page.totalPage }">
                <a>下一页</a>
            </c:if>
            <a href="<c:url context='${pageContext.request.contextPath}' value='/admin/safe/showAllProductController?pageIndex=${page.totalPage}'></c:url>">尾页</a>&nbsp;

        </td>
    </tr>
</table>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57