Solution to paging display problems (JSP, sqlserver, MySQL, Oracle)

When I encountered this problem when I was working on JSP to implement an online transaction platform, I think this problem must be representative, because the paging display technology is actually used in many places. In order to reduce the time spent by beginners in this area, I summarized the paging display method based on some materials.

Method 1:
The most common method is to obtain the result set of all rows in the database directly, and then use next () by locating the mark ().
Sample Code (MySQL for databases ):
// Variable Declaration
Connection sqlcon; // database connection object
Statement sqlstmt;
Resultset sqlrst; // result set object
String strcon; // database connection string
String strsql; // SQL statement
Int intpagesize; // number of records displayed on one page
Int introwcount; // The total number of records.
Int intpagecount; // the total number of pages.
Int intpage; // the page number to be displayed.
Int I;
/**
* Obtain the total number of records
**/
Class. forname ("com. MySQL. JDBC. Driver"). newinstance ();
Strcon = "JDBC: mysql: // localhost: 3306/test ";
Sqlcon = java. SQL. drivermanager. getconnection (strcon, "root", "1 ");
Sqlstmt = sqlcon. createstatement ();
Strsql = "select count (*) from message ";
Sqlrst = sqlstmt.exe cutequery (strsql); // execute the SQL statement and obtain the result set
Sqlrst. Next (); // when the record set is opened, the pointer is located before the first record.
Introwcount = sqlrst. getint (1); // obtain the total number of data records
Sqlrst. Close (); // close the result set.

/**
* Total number of pages
**/
Intpagecount = (introwcount + intPageSize-1)/intpagesize;

/**
* Obtain the result set.
**/
Strsql = "Select time, mail, content from message order by time DESC ";
Sqlrst = sqlstmt.exe cutequery (strsql );
// Locate the record pointer to the first record on the page to be displayed.
I = (intPage-1) * intpagesize;
For (Int J = 0; j <I; j ++)
{
Sqlrst. Next ();
}

/**
* Use the next () and number of rows flag to limit the data displayed on the current page
**/
While (I <intpagesize & sqlrst. Next ())
{
......
......
<Tr>
<TD> User name: <% = sqlrst. getstring ("time") %> </TD>
</Tr>
......
......
}

This method is the most widely used and is acceptable for a small amount of data. However, what if the table contains tens of thousands of rows? Put all into the result set and return? At this time, this method will not work.

Method 2:
Use Database Control to return the data to be displayed on the current page.
A. use MySQL control:
Select * from user
Order by host
Limit m, n
The result returns the dataset from row m + 1 to row n.
For example
Select * from user
Order by host
Limit 1, 5
The returned dataset is from 2nd rows to 5th rows.

B. Use sqlserver
Select *
From (select top M *
From (select Top N *
From MERs mers)
Order by customerid DESC) B
Order by customerid
The data in the result set is from row N-m + 1 to row n.
Explanation of the entire process:
First, the result set a of the First n rows is obtained in ascending order, then the result set B of the last m rows is obtained from a in descending order, and then B is re-ordered in ascending order, returned result set.
Customerid is the primary key.

For example:
Select *
From (select top 5 *
From (select top 10 *
From MERs mers)
Order by customerid DESC) B
Order by customerid
Returns the data result set that contains 6th to 10th rows.

C. use ORACLE:
Select * from (select rownum R, * from test) TT
Where TT. r> 50 and TT. r <= 100;