Paging is a very important technology in Web applications. There may be tens of thousands of data in the database, and it is impossible to display that much data in the browser at once. Typically, a number of rows are displayed on each page based on how much space each row occupies on the page. For example, 20 rows is an ideal display state. At present, there are many kinds of paging technologies. One is to save all the query results in the Session object, and take out a page of data from the Session object and display it when turning pages. This approach has two disadvantages: the user may see outdated data, and if the data volume is very large, it will take a long time to obtain the result set, and the result set will also be stored in memory, occupying a large amount of memory space. The popular practice of paging is to extract only the data needed for the page from the database at each turn of the page. In this way, although every page turn needs to query the database, the number of database queries increased, but the query result set is relatively small, the network transmission is low, users can quickly see the query results, in conjunction with the database connection pool technology, the efficiency will be greatly improved. The following is Oracle as the background database, introduced how to achieve paging display data. Oracle uses the ROWNUM keyword, MySQL uses the Limit keyword, SQL Server uses the TOP keyword, and SQL Server uses the ROWNUM keyword. The specific SQL statement is as follows:

SELET employee_id, last_name,hire_date FROM(
    SELECT ROWNUM AS m,t * FORM(
        SELECT*FROM emplyees ORDER BY hire_date desc
    ) t WHERE ROWNUM <= 20
)WHERE M >10
Copy the code

Analysis of the SQL statement, from inside to outside, it is composed of three layers of query, detailed analysis is as follows:

  • Innermost query – The innermost query syntax is as follows:
SELECT*FROM employees ORDER BY hire_date desc
Copy the code

The result is that all resets in the employee information table are sorted in descending order by date of employment

  • Middle layer query – – Set the maximum number of records. Based on the innermost layer query, the middle layer query statements are decomposed as follows:
SELECT ROWNUM AS M,T.*FROM T WHERE ROWNUM <=20Copy the code

Select top 20 rows in order of number of ROWNUM rows based on innermost query results. Select top 20 rows in order of number of ROWNUM rows based on innermost query results The mid-tier query needs to query the ROWNUM pseudo-column and set the alias (m) for use in the outermost query.

  • Outermost query – – set the minimum number of records – the middle layer query set the maximum number of records, the outermost query will set the minimum number of records, decomposed outermost query SQL statement is as follows:
SELECT employee_id,last_name,hire_date FROM(middle layer query result) WHERE M >10Copy the code

In the outermost query, the minimum number of records is set by limiting the field alias M in the middle-tier query result. The purpose of the entire SQL statement is to query for rows 11 through 20 in reverse order by date of employment.

  • If the paging requirement is to display 20 records on each page, the minimum record on the first page is 1, the maximum record is 20, the minimum record on the second page is 21, and the maximum record is 40. If the number of records displayed on each page is represented by pageSize, and the current page is represented by pageIndex, The maximum number of records is pageSize*(pageIndex-1)+1, and the maximum number is pageSize*pageIndex.

Implement paging display

  1. Determine the number of records to display per page – pageSize
  2. Count the totalPages – totalPages
  3. Gets the current page count
  4. Execute the paging query SQL statement to get the result set and display the information
  5. Complete the paging navigation links