Thoughts on implementing the paging function using the inverted top paging algorithm (implemented by Java code)

1. Select a paging algorithm

The following is an inverted top paging algorithm.

 

Pagesize: number of records displayed per page
Cureentpage: Current page number

Select * from (select top pagesize * cureentpage * from news order by addtime DESC) as a order by addtime ASC) as B order by addtime DESC

 

In this way, the data on each page can be returned, and the index can improve the query efficiency.

 

Select count (*) from news

 

Total number of returned records

 

Ii. Program Design

Page. Java

1. Package com. Test. util
3. Public class page (){
5. Private int pagesize; // number of records on one page
6. Private int currentpage; // current page
7. Private int rowcount; // The total number of records.
8. Private int pagecount; // the total number of pages calculated
10. // Variable Initialization
11. Public page (INT currentpage, int rowcount ){
12. Pagesize = 10;
13. Pagecount = rowcount/pagesize + 1; // calculate the total number of pages
14. This. currentpage = currentpage;
15. This. rowcount = rowcount;
16. }
18. // Returns the total number of records.
19. Public int getpagecount (){
20. Return pagecount;
21. }
23. // Return the number of records per page
24. Public int getpagesize (){
25. Return pagesize;
26. }
28. // Return the judgment status. If the first page returns 0, the last page returns 1, and if the normal page returns 2
29. Public int checkcurrent (){
30. If (current = 1 ){
31. Return 0;
32. }
33. If (current = pagecount ){
34. Return 1;
35. }
36. Else {
37. Return 2;
38. }
39. }
40. }

Call in action

Int currentpage = integer. parseint (request. getparameter ("page "));

 

String SQL = "select count (*) from News ";

Resultset rs = stms.exe cutequery (SQL );

Rs. Next ();

Int rowcount = Rs. getint (1 );

Rs. Close ();

 

Page P = new page (currentpage, rowcount );

Int pagecount = P. getpagecount ();

Int flag = P. checkcurrent ();

Int pagesize = P. getpagesize ();

Resultset RS1 = NULL;

If (flag = 0 ){

SQL = "select top" + pagesize + "* from news order by addtime DESC ";

RS1 = stms.exe cutequery (SQL );

....................

}

If (flag = 1 ){

Int pagelast = rowcount-(pagecount-1) * pagesize );

SQL = "select * from (select top" + pagelast + "* from news order by addtime ASC) as a order by addtime DESC ";

RS1 = stms.exe cutequery (SQL );

....................

}

Else {

Int pagelast = rowcount-(pagecount-1) * pagesize );

SQL = "select * from (select top pagesize * cureentpage * from news order by addtime DESC) as a order by addtime ASC) as B order by addtime DESC ";

RS1 = stms.exe cutequery (SQL );

....................

}

.....................

 

 

 

 

The above is my paging function implemented by using the inverted top method, which is very simple.

In terms of efficiency, it should be no problem to Support 2 million pieces of data.

 

 

PS: will it be more efficient to apply SQL queries to stored procedures?