After using PageHelper for so long, do you know the principle and steps of native paging query

1, Overview of paging query

Pagination query displays a lot of data in sections on the page, and displays the user-defined number of rows on each page. It can improve the user experience and reduce the risk of one-time loading and memory overflow.

1.1. Paging query classification

1.1.1 true paging

Query data from the database every time you turn the page.

  1. Advantages: it is not easy to cause memory overflow.
  2. Disadvantages: complex implementation and relatively low performance.

1.1.2 false paging

Query all data at one time, store it in memory, turn pages and obtain data from memory.

  1. Advantages: simple implementation and high performance.
  2. Disadvantages: easy to cause memory overflow.

1.2 paging effect

When you send a request to access a data page with paging, you will find that it is mainly composed of two parts:

  1. The result set data of the current page, such as the product information on this page.
  2. Page bar information, such as [first page], [previous page], [next page], [last page], etc.

Paging effect

2, Paging design

2.1 parameters to be passed in paging

2.1.1 parameters to be passed in by the user

  1. currentPage: current page, jump to the page, int type, set the default value, such as 1.
  2. pageSize: the maximum number of pieces of data per page, type int, and set the default value, such as 10.

2.1.2 data to be displayed in paging

  1. start: home page.
  2. prevPage: Previous page.
  3. nextPage: next page.
  4. totalPage: the last page number.
  5. totalCounts: total records.
  6. currentPage: current page.
  7. pageSize: number of records per page.

2.1.3 source of data to be displayed in pagination

  • Incoming from user:
  1. currentPage: current page, type int.
  2. pageSize: how many pieces of data are displayed on each page, type int.

From two SQL queries:

  1. totalCount/rows: total number of data, type int.
  2. data/list: result set data of each page, List type.

From program calculation:

  1. totalPage: total pages / last page, int type.
  2. prevPage: Previous page, type int.
  3. nextPage: next page, type int.

2.2 paging principle

The total number of results (totalCount/rows) and the result set (data/list) are derived from two SQL:

  1. The first SQL: the total number of queries and returns a number (total records).
select count(*) from province
  1. Query the qualified result set (return a result set).
# It has two parameters, one is the page number of the starting page, and the other is the number of records per page
# start : (currentPage - 1) * pageSize
# pageSize: given by the front desk
select * from province limit #{start} ,#{pageSize}
  1. Calculate the remaining parameters (total pages, previous page, next page)
  // The paging data is encapsulated by this constructor
  public PageResult(int currentPage, int pageSize, int totalCount, List<T> data) {
    this.currentPage = currentPage;
    this.pageSize = pageSize;
    this.totalCount = totalCount;
    this.data = data;
    // Calculate the total number of pages (first)
    this.totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
    // Use the ternary operator to calculate the previous page. If it is already the first page, it will not have the previous page. Let its previous page be the first page. Otherwise, subtract 1 from the current page to the previous page
    this.prevPage = currentPage - 1 >= 1 ? currentPage - 1 : 1;
    // Use the ternary operator to calculate the next page. If it is the last page, there will be no next page, and it will not be added to the next page. Otherwise, the current page will increase automatically
    this.nextPage = currentPage + 1 <= totalPage ? currentPage + 1 : totalPage;

  }

3, Paging query implementation

3.1 access process

Paging access process

3.2 encapsulation of paging parameters

In order to display the above paging effect on the page, we have to encapsulate every data on the page into an object to share with

JSP.

If we do not encapsulate, all the seven parameters should be retrieved in the Session domain, which is complex and disgusting.

We usually encapsulate multiple data to be shared into an object. In the future, we only need to encapsulate the data into the object and share the object.

3.3. Write PageResult

package com.qo;

import java.util.List;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor

/**
  Encapsulate result data (data of a page)
 * @author Xiao_Lin
 */
public class PageResult<T> {
  // User entered data
  private int currentPage;  // Current page number
  private int pageSize; // Number of items displayed per page

  //Results after SQL execution
  private int totalCount; // Total number
  private List<T> data; // Current page data result set

  // Calculated by program
  private int prevPage; // previous page
  private int nextPage; // next page
  private int totalPage; // last page

  // The paging data is encapsulated by this constructor
  public PageResult(int currentPage, int pageSize, int totalCount, List<T> data) {
    this.currentPage = currentPage;
    this.pageSize = pageSize;
    this.totalCount = totalCount;
    this.data = data;
    // Calculate the total number of pages (first)
    this.totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
    // Use the ternary operator to calculate the previous page. If it is already the first page, it will not have the previous page. Let its previous page be the first page. Otherwise, subtract 1 from the current page to the previous page
    this.prevPage = currentPage - 1 >= 1 ? currentPage - 1 : 1;
    // Use the ternary operator to calculate the next page. If it is the last page, there will be no next page, and it will not be added to the next page. Otherwise, the current page will increase automatically
    this.nextPage = currentPage + 1 <= totalPage ? currentPage + 1 : totalPage;
  }
}

3.4. Modify Mapper (Dao)

 // Query the total number of records. Pass in an encapsulated query object. The parameters include the current page and the number of records per page (it can not be transferred, but try to transfer it as far as possible, which is the basis for fuzzy query)
  int queryCount(QueryObject qo);

// The query result set is passed into a encapsulated query object. The encapsulated parameters include the current page, the number of records per page, and the starting page number
  List<Province> query(QueryObject qo);

3.5. Modify Service and ServiceImpl

  PageResult<Province> query(ProvinceQueryObject qo);
package com.service.impl;

/**
 * @author Amateur grass
 */
public class ProvinceServiceImpl implements ProvinceService {

  ProvinceMapper mapper = ConnUtils.getConnection().getMapper(ProvinceMapper.class);

  @Override
  public PageResult<Province> query(ProvinceQueryObject qo) {
    // Gets the number of records queried
    int totalCount = mapper.queryCount(qo);
    // If the total number of records is 0, it means that there is no data, so there is no need to query in the next step to improve efficiency.
    if (totalCount == 0){
        // Returns a query result set, the current page, the number of records per page, and an empty result set
      return new PageResult<Province>(qo.getCurrentPage(),qo.getPageSize(),totalCount,Collections.EMPTY_LIST);
    }
      // The number of records is not 0. A result set is queried
    List<Province> provinces = mapper.query(qo);
      // Returns a query result set, including the current page, the number of records per page, and the result set
    return new PageResult<Province>(qo.getCurrentPage(),qo.getPageSize(),totalCount,provinces);
  }

3.6. Write QueryObject

package com.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * Encapsulate the two paging parameters passed in by the request for paging query
 * @author Amateur grass
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class QueryObject {
  private int currentPage = 1; // Current page number, page number of which page to jump to (default value is required)
  private int pageSize = 3 ; // Number of display bars per page (default value is required)
 //Used for the first of the Limit clause? Value, starting page
  public int getStart(){
    return (currentPage-1)*pageSize;
  }
}

3.7. Write test class

 ProvinceService provinceService = new ProvinceServiceImpl();
    QueryObject qo = new QueryObject();
 PageResult<Province> pageResult = provinceService.query(qo);
    System.out.println("Current page:"+pageResult.getCurrentPage());
    System.out.println("Result set data:" + pageResult.getData());
    System.out.println("Total records on current page:" + pageResult.getTotalCount());
    System.out.println("Number of articles:" + pageResult.getData().size());
    System.out.println("Total pages:" + pageResult.getTotalPage());
    System.out.println("previous page:" + pageResult.getPrePage());
    System.out.println("Next page:" + pageResult.getNextPage());

Paging test results

3.7. Writing servlets

package com.servlet;

/**
 * @author Amateur grass
 */
@WebServlet(urlPatterns = "/listall")
public class ProvinceServlet extends HttpServlet {

  @Override
  protected void service(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    ProvinceService provinceService = new ProvinceServiceImpl();
    // Create QueryObject object
    QueryObject qo = new QueryObject();
    // Get parameters
    String currentPage = req.getParameter("currentPage");
    // Use the tool class to determine whether this parameter is passed
    if (StringUtil.hasLength(currentPage)){
      // If passed, assign a value
      qo.setCurrentPage(Integer.parseInt(currentPage));
    }
    String pageSize = req.getParameter("pageSize");
    if (StringUtil.hasLength(pageSize)){
      qo.setPageSize(Integer.parseInt(pageSize));
    }
    // Call the business layer method to process the request and query a page of data
    PageResult<Province> query = provinceService.query(qo);
    // Share data with jsp
    req.setAttribute("pageResult", query);
    // Control jumps to the list.jsp page
    req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(req, resp);

  }
}

3.7 foreground implementation

It includes writing servlets and JSPS. Servlet s process requests, call business methods, and share the queried data into JSPS for display to users. Operation steps:

  1. The browser sends paging request parameters (page number / number of data per page), receives these parameters in the Servlet, encapsulates them into the QueryObject object, and calls the paging query method (query) in the Service.
  2. Share the paging query result object (PageResult) in the request scope, jump to JSP and display it.
  3. Modify the JSP page and write the page bar information (the information in the page bar comes from the PageResult object).
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Product list</title>
    <script type="text/javascript">
      window.onload = function () {
        var trClzs = document.getElementsByClassName("trClassName");
        for(var i = 0; i < trClzs.length; i++){
          trClzs[i].onmouseover = function () {
            console.log(1);
            this.style.backgroundColor = "gray";
          }
          trClzs[i].onmouseout = function () {
            console.log(2);
            this.style.backgroundColor = "";
          }
        }
      }
      // Paging JS
      function changePageSize() {
        document.forms[0].submit();
      }
    </script>
</head>
<body>

<form action="/product">
    <table border="1" cellspacing="0" cellpadding="0" width="80%">
        <tr>
            <th>number</th>
            <th>name</th>
            <th>abbreviation</th>
        </tr>
        <c:forEach var="province" items="${pageResult.data}"
                   varStatus="status">
            <tr class="trClassName">
                <td>${status.count}</td>
                <td>${province.id}</td>
                <td>${province.name}</td>
                <td>${province.abbr}</td>
                <td>
                    <a href="/listall?cmd=delete&id=${product.id}">delete</a>
                    <a href="/listall?cmd=input&id=${product.id}">modify</a>
                </td>
            </tr>
        </c:forEach>
        <tr align="center">
            <td colspan="9">
                <a href="/listall?currentPage=1">home page</a>
                <a href="/listall?currentPage=${pageResult.prevPage}">previous page
                </a>
                <a href="/listall?currentPage=${pageResult.nextPage}">next page
                </a>
                <a href="/listall?currentPage=${pageResult.totalPage}">Last page
                </a>
                Current section ${pageResult.currentPage} / ${pageResult.totalPage} page
                altogether ${pageResult.totalCount} Data bar
                Jump to<input type="number" onchange="changePageSize()"
                          name="currentPage" value="${pageResult.currentPage}" style="width: 60px;">page
                Display per page
                <select name="pageSize" onchange="changePageSize()">
                    <option value="3" ${pageResult.pageSize == 3 ?
                            'selected' : ''}> 3 </option>
                    <option value="5" ${pageResult.pageSize == 5 ?
                            'selected' : ''}> 5 </option>
                    <option value="8" ${pageResult.pageSize == 8 ?
                            'selected' : ''}> 8 </option>
                </select>Data bar
            </td>
        </tr>
    </table>
</form>
</body>
</html>

Posted on Mon, 06 Dec 2021 21:03:18 -0500 by gateUK