Currently, the best JSP paging technology

Preface <br/> the paging function is inevitable during database usage, but JDBC specifications do not solve this problem well. Many of my friends have their own solutions for this demand. For example, we use a collection class such as vector to save the Retrieved Data first and then pagination. However, this method has poor availability, which is completely different from the JDBC interface and does not support different types of fields. A solution with good compatibility with JDBC is provided here. <Br/> JDBC and paging <br/> The establishment of Sun's JDBC specifications sometimes makes people laugh. In jdbc1.0, for a result set (resultset) you can only execute the next () operation, but cannot scroll back. This directly leads to the failure to obtain the size of the result set when only one SQL query is executed. Therefore, if you are using the jdbc1.0 driver, paging is almost impossible. <Br/> fortunately, sun's jdbc2 specification makes up for this deficiency and adds the result set's scroll operations, although it still cannot directly support paging, however, you can write your own resultset that supports pagination on this basis. </P> <p> implementation methods related to specific databases <br/> some databases, such as MySQL and Oracle, have their own paging methods. For example, MySQL can use the limit clause, oracle can use rownum to limit the size and start position of the result set. Take MYSQL as an example. The typical code is as follows: </P> <p> 1. // calculate the total number of records </P> <p> 2. string SQL = "select count (*) as total" + this. querypart; </P> <p> 3.rs = db.exe cutequery (SQL); </P> <p> 4.if (RS. next () </P> <p> 5. total = Rs. getint (1); </P> <p> 6. // set the current page number and total page number </P> <p> 7. tpages = (INT) math. ceil (double) This. total/This. maxline); </P> <p> 8. cpages = (INT) math. floor (double) offset/This. maxline + 1); </P> <p> 9. // obtain the required records based on the conditions. </P> <p> 10.if (total> 0) {</P> <p> 11. SQL = query + "Limit" + Offset + "," + maxline; </P> <p> 12.rs = db.exe cutequery (SQL ); </P> <p> 13 .} </P> <p> 14. return Rs; </P> <p> 15 .} <br/> copy the Code <br/> without a doubt, this code will be beautiful when the database is MySQL, but as a general class (in fact, what I want to provide later is a part of a general class library), we need to adapt to different databases, and applications based on this class (Library, you may also use different databases, so we will not use this method. </P> <p> another tedious implementation method <br/> I have seen some people's practices (in fact, this method was used at the beginning, including me ), that is, no encapsulation is used. When paging is required, the resultset is directly rolled to the corresponding position and then the corresponding number of records are read. The typical code is as follows: </P> <p> 1. <% </P> <p> 2. sqlstmt = sqlcon. createstatement (Java. SQL. resultset. type_scroll_insensitive, </P> <p> 3. java. SQL. resultset. concur_read_only); </P> <p> 4. strsql = "Select name, age from test"; </P> <p> 5. // execute the SQL statement and obtain the result set </P> <p> 6. sqlrst = sqlstmt.exe cutequery (strsql); </P> <p> 7. // obtain the total number of records </P> <p> 8. sqlrst. last (); </P> <p> 9. introwcount = sqlrst. getrow (); </P> <p> 10. // calculate the total number of pages </P> <p> 11. intpagecount = (introwcount + IntPageSize-1)/intpagesize, </P> <p> 12. // adjust the page number to be displayed </P> <p> 13.if( intpage> intpagecount) intpage = intpagecount; </P> <p> 14. %> </P> <p> 15. <Table border = "1" cellspacing = "0" cellpadding = "0"> </P> <p> 16. <tr> </P> <p> 17. <TH> name </Th> </P> <p> 18. <TH> age </Th> </P> <p> 19. </tr> </P> <p> 20. <% </P> <p> 21.if( intpagecount> 0) {</P> <p> 22. // locate the record pointer to the first record on the page to be displayed </P> <p> 23. sqlrst. absolute (intPage-1) * intpagesize + 1); </P> <p> 24. // display data </P> <P> 25. I = 0; </P> <p> 26. While (I <intpagesize &&! Sqlrst. isafterlast () {</P> <p> 27. %> </P> <p> 28. <tr> </P> <p> 29. <TD> <% = sqlrst. getstring (1) %> </TD> </P> <p> 30. <TD> <% = sqlrst. getstring (2) %> </TD> </P> <p> 31. </tr> </P> <p> 32. <% </P> <p> 33. sqlrst. next (); </P> <p> 34. I ++; </P> <p> 35 .} </P> <p> 36 .} </P> <p> 37. %> </P> <p> 38. </table> intpagecount) intpage = intpagecount; </P> <p> 39. %> <br/> copy the Code <br/> obviously, this method does not take into account the code reuse issue, not only the large number of codes, but also the code needs to be modified, it will be at a loss. </P> <p> pagination using vector <br/> you have also seen some other paging classes. Select All records first, then, get all the data in the resultset and store the data in the collection classes such as vector. Then, locate the corresponding location and read the data based on the size and number of pages of the required pages. Or use the two paging methods mentioned above to obtain the required page and store it in the vector. <Br/> the efficiency of code throwing is not mentioned. It is very bad in terms of the program structure and convenience of use. For example, the field types supported by this method are limited. The Int, double, and string types are better processed. If blob, text, and Other types are involved, it is very troublesome to implement them. This is an undesirable solution. </P> <p> A new pageable interface and its implementation <br/> obviously, after reading the above three implementation methods, we have a goal for the new paging mechanism, that is, it is not related to a specific database; code reuse is possible; it should be consistent with the usage of the original JDBC interface as much as possible; and the efficiency should be as high as possible. <Br/> first, we need to provide. SQL. resultset: The downward compatible API named pageable. The API is defined as follows: </P> <p> 1. public interface pageable extends Java. SQL. resultset {</P> <p> 2. /** total number of returned pages </P> <p> 3. */</P> <p> 4.int getpagecount (); </P> <p> 5. /** return the number of records on the current page </P> <p> 6. */</P> <p> 7.int getpagerowscount (); </P> <p> 8. /** return page size </P> <p> 9. */</P> <p> 10.int getpagesize (); </P> <p> 11. /** go to the specified page </P> <p> 12. */</P> <p> 13. void gotopage (INT page); </P> <p> 14. /** set the page size </P> <p> 15. */ </P> <p> 16. void setpagesize (INT pagesize); </P> <p> 17. /** total number of returned records </P> <p> 18. */</P> <p> 19.int getrowscount (); </P> <p> 20. /** </P> <p> 21. * Go to the first record on the current page </P> <p> 22. * @ exception Java. SQL. sqlexception exception description. </P> <p> 23. */</P> <p> 24. void pagefirst () throws Java. SQL. sqlexception; </P> <p> 25. /** </P> <p> 26. * go to the last record on the current page </P> <p> 27. * @ exception Java. SQL. sqlexception exception description. </P> <p> 28. */</P> <p> 29. void pagelast () throws Java. SQL. sqlexception; </P> <p> 30. /** return the current page number </P> <p> 31. */</P> <p> 32.int getcurpage (); </P> <p> 33 .} <br/> copy the Code <br/>. SQL. resultset provides extended interfaces to support paging, such as setting the page size, redirecting to a page, and returning the total number of pages. <Br/> next, we need to implement this interface. Because this interface inherits from resultset, and most of its functions are the same as those of resultset, therefore, a simple decorator mode is used here. <Br/> the class declaration and member declaration of pageableresultset2 are as follows: <br/> public class pageableresultset2 implements pageable {<br/> protected Java. SQL. resultset rs = NULL; <br/> protected int rowscount; <br/> protected int pagesize; <br/> protected int curpage; <br/> protected string command = ""; <br/>}< br/> you can see that in pageableresultset2, an instance of the resultset is included (this instance only implements the resultset interface, in fact, it is implemented by different database vendors), and all the methods inherited by resultset are directly forwarded to the instance for processing. <Br/> main methods inherited from resultset in pageableresultset2: <br/> //...... <Br/> Public Boolean next () throws sqlexception {<br/> Return Rs. Next (); <br/>}< br/> //...... <Br/> Public String getstring (string columnname) throws sqlexception {<br/> try {<br/> Return Rs. getstring (columnname); <br/>}< br/> catch (sqlexception E) {// here is to add some error information for debugging <br/> throw new sqlexception (E. tostring () + "columnname =" <br/> + columnname + "SQL =" + this. getcommand (); <br/>}< br/> //...... <Br/> only the newly added methods in the pageable interface need their own write Method Processing. <Br/>/** for method annotations, see pageable. java <br/> */<br/> Public int getcurpage () {<br/> return curpage; <br/>}< br/> Public int getpagecount () {<br/> If (rowscount = 0) return 0; <br/> If (pagesize = 0) return 1; <br/> // calculate pagecount <br/> double tmpd = (double) rowscount/pagesize; <br/> int tmpi = (INT) tmpd; <br/> If (tmpd> tmpi) tmpi ++; <br/> return tmpi; <br/>}< br/> Public int getpagerowscount () {<br/> If (pagesize = 0) r Eturn rowscount; <br/> If (getrowscount () = 0) return 0; <br/> If (curpage! = Getpagecount () return pagesize; <br/> return rowscount-(getpagecount ()-1) * pagesize; <br/>}< br/> Public int getpagesize () {<br/> return pagesize; <br/>}< br/> Public int getrowscount () {<br/> return rowscount; <br/>}< br/> Public void gotopage (INT page) {<br/> If (rs = NULL) <br/> return; <br/> If (page <1) <br/> page = 1; <br/> If (page> getpagecount () <br/> page = getpagecount (); <br/> int ROW = (PAG E-1) * pagesize + 1; <br/> try {<br/> Rs. absolute (ROW); <br/> curpage = page; <br/>}< br/> catch (Java. SQL. sqlexception e) {<br/>}< br/> Public void pagefirst () throws Java. SQL. sqlexception {<br/> int ROW = (curPage-1) * pagesize + 1; <br/> Rs. absolute (ROW); <br/>}< br/> Public void pagelast () throws Java. SQL. sqlexception {<br/> int ROW = (curPage-1) * pagesize + getpagerowscount (); <br/> Rs. absolute (ROW ); <Br/>}< br/> Public void setpagesize (INT pagesize) {<br/> If (pagesize> = 0) {<br/> This. pagesize = pagesize; <br/> curpage = 1; <br/>}< br/> Construction Method of pageableresultset2: <br/> Public pageableresultset2 (Java. SQL. resultset RS) throws Java. SQL. sqlexception {<br/> If (rs = NULL) throw new sqlexception ("given resultset is null", "user"); </P> <p> Rs. last (); <br/> rowscount = Rs. getrow (); <br/> Rs. beforefirst (); </P> <p> T His. rs = RS; <br/>}< br/> here, we just get a total number of records and move the record cursor back to the initial position (before first ), at the same time, the resultset In the parameter is assigned to the member variable. </P> <p> usage of pageable <br/> because the pageable interface inherits from the resultset, it is used in the same way as the resultset, it can be used as a resultset, especially when the paging function is not required. When paging is required, you only need a simple setpagesize and gotopage. <Br/> preparedstatement pstmt = NULL; <br/> pageable rs = NULL; <br/> ...... // Construct the SQL statement and prepare a pstmt. <br/> rs = new pageableresultset2(pstmt.exe cutequery (); // construct a pageable <br/> Rs. setpagesize (20); // 20 records per page <br/> Rs. gotopage (2); // jump to page 2nd <br/> for (INT I = 0; I <Rs. getpagerowscount (); I ++) {// loop processing <br/> int id = Rs. getint ("ID"); <br/> ...... // Continue processing <br/> Rs. next (); <br/>}</P> <p> conclusion <br/> A good basic class should be easy to use and have sufficient portability, at the same time, it is necessary to ensure the perfection of its functions. In the above implementation, we inherited the pageable from the java. SQL. resultset interface and implemented it. This ensures consistency with the original JDBC operations in use, while the original functions are not reduced. <Br/> It is also easy to use because it encapsulates all necessary operations, so the only difference between "ugly" and "Uncomfortable" in your code is that you need to construct a pageableresultset2. However, this can be solved as long as you are willing. <Br/> Of course, it also has full portability. When you change the database from Oracle to MySQL or sqlserver, you can still use the paging code. The only restriction in its use (or during the porting process) is that you must use a jdbc2-supported Driver (now I understand why I name the class pageableresultset2.: P). Fortunately, jdbc2 has become a standard. Most databases (such as Oracle, MySQL, and sqlserver) have their own or third-party jdbc2 drivers. <Br/> OK. Is the implementation of this page helpful for your programming? Take a closer look. In fact, there are not many codes actually written by myself. Most of them are simple forwarding operations. A suitable application can help you a lot. <Br/>