Solution to Display Problem (JSP, SQLServer, MySQL, Oracle)

I met this problem when I did JSP to implement an online trading platform. I think this problem must be representative because the paging display technology is much more. In order to reduce the time spent in this regard, I summarize the method of paging display on the basis of some information.

Method 1: The most common method is to use the result set of all rows in the database, and then use next (). Sample code (database uses mysql): // Variable declaration connection sqlcon; // Database connection object statement SQLSTMT; ResultSet SQLRST; / / Result set object string string strCon; // Database connection string string strsql; // sql statement int intebitsize; // One page shows the number INTIT INTROUNT; / / Record the total number of rules int intepageCount; // Total number INT INTPAGE; // To display the page number INT i; / ** * Get the total number of records ** / Class. Forname ("com.mysql.jdbc.driver). NewInstance (); strcon =" jdbc: mysql: // localhost: 3306 / test "; sqlcon = java.sql.driverManager.getConnection (strident," root "," 1 "); SQLSTMT = Sqlcon.createStatement (); strsql =" select count (*) from message "; sqlrst = sqlstmt.executeQuery (strsql); // execute SQL statement and acquire SQLRST.NEXT (); // When the recording is just opened, the pointer is in the first record before INTROWCOUNT = SQLRST.GETINT (1); // Get the total number of data records SQLRST.CLOSE (); // Close the result set

/ ** * Mode total page ** / intPageCount = (Introwcount INTPAGESIZE-1) / INTPAGESIZE

/ ** * Get results set ** / strsql = "select time, mail, content from message order, time time"; sqlrst = sqlstmt.executeQuery (strsql); // Position the record pointer to the first one of the page to be displayed Record i = (INTPAGE-1) * INTPAGESIZE; For (int J = 0; j

/ ** * Use Next () and the number of row flags Limit the data displayed by the current page ** / while (i User Name: <% = SQLRST.GETSTRING ("Time")%> ............}

This method is most common, which is acceptable for small amounts of data. However, if the data in Table has hundreds of thousands of rows? All put the result set back? At this time, this method will not work. Method 2: Use database control to return the data that needs to be displayed in the current page. a. Use mysql control: Select * from userorder by HostLimit M, N results returned to the data set of the M 1 line to the nth line. For example, Select * from userorder by HostLimit 1, 5 Returns the 2nd line to the 5th line of data set

b. The result set data obtained by using SQLServerseelect * from (SELECT TOP N * FROM CUSTOMERS a Order By Customerid DESC) Border By Customerid is N-m 1 line to the Nth line. Interpretation of the entire process: First, obtain the result set A of the previous N row according to the ascending order, and then obtain the result set B of the M row in descending order from A, and finally re-sort in ascending order, return the result set. Where Customerid is the primary key

For example: select * from (select top 5 * from (select top 10 * from customers) a order by customerid desc) Border by Customerid means returning to the data result set containing Chain 6 to 10th lines.

c. Use Oracle: Select * from (SELECT ROWNUM R, * from test) TT WHERE TT.R> 50 and TT.R <= 100;