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

1, Overview of paging query

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 # ,#

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="$" varStatus="status"> <tr> <td>$</td> <td>$</td> <td>$</td> <td>$</td> <td> <a href="/listall?cmd=delete&id=$">delete</a> <a href="/listall?cmd=input&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=$">previous page </a> <a href="/listall?currentPage=$">next page </a> <a href="/listall?currentPage=$">Last page </a> Current section $ / $ page altogether $ Data bar Jump to<input type="number" onchange="changePageSize()" name="currentPage" value="$" style="width: 60px;">page Display per page <select name="pageSize" onchange="changePageSize()"> <option value="3" $> 3 </option> <option value="5" $> 5 </option> <option value="8" $> 8 </option> </select>Data bar </td> </tr> </table> </form> </body> </html>

Add new comment

0 comments