“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!”

Overview of paging query

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

1.1 classification of paging query

1.1.1 True paging

Query data from the database at each page turn.

  1. Advantage: not easy to cause memory overflow.
  2. Disadvantages: Complex implementation, relatively low performance.

1.1.2. False paging

Query all data into memory at one time, and turn pages to obtain data from memory.

  1. Advantages: Simple implementation and high performance.
  2. Disadvantages: Easy to cause memory overflow.

1.2. Paging effect

Send a request to access data with a paging page and find that it consists of two main parts:

  1. Result set data for the current page, such as what items are on the page.
  2. Page bar information, such as [home] [Previous] [Next] [Last] and so on.

Second, the design of paging

2.1 parameters that need to be passed in paging

2.1.1 Parameters to be passed by the user

  1. CurrentPage: currentPage, page to jump to, int type, set the default value, such as 1.
  2. PageSize: Maximum number of pieces of data per page, int, default value, such as 10.

2.1.2 Paging the data to be displayed

  1. Start: home page.
  2. PrevPage: You’re on the last prevPage.
  3. NextPage: nextPage.
  4. TotalPage: Last page number.
  5. TotalCounts: Total number of records.
  6. CurrentPage: indicates the currentPage.
  7. PageSize: Indicates the number of records per page.

2.1.3 Source of data to be displayed in paging

  • From user incoming:

    1. CurrentPage: indicates the currentPage, of type int.

    2. PageSize: How many pieces of data to display per page, int type.

  • Derived from two SQL queries:

    1. TotalCount /rows: Indicates the total number of data rows. The type is int.
    2. Data /list: Result set data for each page, list type.

  • Derived from program calculation:

    1. TotalPage: totalPage/last page, type int.
    2. PrevPage: previous page, int.
    3. NextPage: nextPage, int type.

2.2. Principle of paging

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

  1. First SQL: query the total number and return a number (total number of records).
select count(*) from province
Copy the code
  1. Query the result set that meets the condition (return one result set).
# It takes two parameters, one is the page number of the start page and the other is the number of records per page
# start ：(currentPage - 1) * pageSize
# pageSize: Front desk gives
select * from province limit #{start} ,#{pageSize}
Copy the code
  1. Calculate the remaining parameters (total page, previous page, next page)
  // The paged 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;
    // Count the total number of pages
    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, then it will not have a previous page, so it will be the first page, otherwise, the current page will be subtracted by 1 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, then there is no next page, do not let it next page to add, otherwise the current page increment
    this.nextPage = currentPage + 1 <= totalPage ? currentPage + 1 : totalPage;

  }
Copy the code

Three, the realization of paging query

3.1 Access Process

3.2 Encapsulation of paging parameters

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

The JSP.

If we didn’t encapsulate it, then all seven parameters would have to be fetched in the Session domain, which is complicated and disgusting.

We usually encapsulate multiple data that need to be shared into one object, and then we just need to encapsulate the data into that object and then share that 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 the result data (a page of data) *@author Xiao_Lin
 */
public class PageResult<T> {
  // Data entered by the user
  private int currentPage;  // Current page number
  private int pageSize; // The number of items displayed per page

  // The result of SQL execution
  private int totalCount; / / the total number of article
  private List<T> data; // The current page data result set

  // Use the program to calculate
  private int prevPage; / / back
  private int nextPage; / / the next page
  private int totalPage; // Last page

  // The paged 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;
    // Count the total number of pages
    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, then it will not have a previous page, so it will be the first page, otherwise, the current page will be subtracted by 1 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, then there is no next page, do not let it next page to add, otherwise the current page increment
    this.nextPage = currentPage + 1 <= totalPage ? currentPage + 1: totalPage; }}Copy the code

Mapper (Dao)

 // Query the total number of records and pass in an enclosed query object with parameters including the number of records on the current page and the number of records per page.
  int queryCount(QueryObject qo);

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

3.5. Modify Service and ServiceImpl

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

/ * * *@author Xiao_Lin
 * @date2021/1/22 "* /
public class ProvinceServiceImpl implements ProvinceService {

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

  @Override
  public PageResult<Province> query(ProvinceQueryObject qo) {
    // Get the number of queried records
    int totalCount = mapper.queryCount(qo);
    // If the total number of records is 0, then there is no data for the next query.
    if (totalCount == 0) {// Return a query result set with 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);
    }
      // If the number of records is not 0, a result set is queried
    List<Province> provinces = mapper.query(qo);
      // Return a query result set that returns the current page, the number of records per page, and the result set
    return new PageResult<Province>(qo.getCurrentPage(),qo.getPageSize(),totalCount,provinces);
  }
Copy the code

3.6. Write QueryObject

package com.domain;

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

/** * Encapsulates the paging parameters passed in the two requests required by the paging query@author Xiao_Lin
 * @date2021/1/22 21:49 * /
@Data
@AllArgsConstructor
@NoArgsConstructor
public class QueryObject {
  private int currentPage = 1; // The current page number, which page number to jump to (need to give the default value)
  private int pageSize = 3 ; // Display number of items per page (default value)
 // For the first? Value, the start page number
  public int getStart(a){
    return (currentPage-1)*pageSize; }}Copy the code

3.7. Write test classes

	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 number of records on current page: + pageResult.getTotalCount());
    System.out.println("Number of items:" + pageResult.getData().size());
    System.out.println("Total pages:" + pageResult.getTotalPage());
    System.out.println("Previous page:" + pageResult.getPrePage());
    System.out.println("Next page:" + pageResult.getNextPage());
Copy the code

3.7. Write servlets

package com.servlet;

/ * * *@author Xiao_Lin
 * @date2021/1/24 10:17 * /
@WebServlet(urlPatterns = "/listall")
public class ProvinceServlet extends HttpServlet {

  @Override
  protected void service(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    ProvinceService provinceService = new ProvinceServiceImpl();
    // Create a QueryObject
    QueryObject qo = new QueryObject();
    // Get the parameter
    String currentPage = req.getParameter("currentPage");
    // Use the utility class to determine if this parameter is passed
    if (StringUtil.hasLength(currentPage)){
      // Assign a value if passed
      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 to query a page of data
    PageResult<Province> query = provinceService.query(qo);
    // Share the data with the JSP
    req.setAttribute("pageResult", query);
    // Control jump to list.jsp page
    req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(req, resp); }}Copy the code

3.7 foreground implementation

Includes writing servlets and JSPS, servlets handle requests, invoke business methods, query to share data in JSP, display to users. Operation steps:

  1. The browser issues paging request parameters (pages to go/items per page), receives these parameters in the Servlet, encapsulates them into the QueryObject, and calls the paging query method in the Service.
  2. The paging query result object (PageResult) is shared in the request scope, jump to JSP, display.
  3. Modify the JSP page to 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(a) {
        document.forms[0].submit();
      }
    </script>
</head>
<body>

<form action="/product">
    <table border="1" cellspacing="0" cellpadding="0" width="80%"Number > < tr > < th > < / th > < th > name < / th > < th > or < / th > < / tr > < c: forEachvar="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}"> </a> </td> </tr> </c:forEach> <tr align="center">
            <td colspan="9">
                <a href="/listall? currentPage=1"> <a href="/listall? currentPage=${pageResult.prevPage}"> <a href="/listall? currentPage=${pageResult.nextPage}"> < span style = "text-align: center"/listall? currentPage=${pageResult.totalPage}"${pageResult. TotalCount = ${pageResult. TotalCount = ${pageResult. TotalCount = ${pageResult"number" onchange="changePageSize()"
                          name="currentPage" value="${pageResult.currentPage}" style="width: 60px;"<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' : ' '} >8Data article < option > < / select > < / td > < / tr > < / table > < / form > < / body > < / HTML >Copy the code