Paging in Java

In the process of using the database, it is inevitable to use the paging function, but the JDBC specification does not solve this 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.

JDBC and paging
Sun's JDBC specification is sometimes dumbfounded. In jdbc1.0, you can only perform the next () operation on a result set instead of rolling it backwards, 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.
Fortunately, sun's jdbc2 specification makes up for this deficiency and adds the frontend and backend scrolling operations of the result set. Although it still cannot directly support paging, however, you can write your own resultset that supports pagination on this basis.

Implementation methods related to specific databases
Some databases, such as MySQL and Oracle, have their own paging methods. For example, MySQL can use the limit clause, and 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:
// Calculate the total number of records
String SQL = "select count (*) as total" + this. querypart;
Rs = db.exe cutequery (SQL );
If (Rs. Next ())
Total = Rs. getint (1 );
// Set the current page number and total page number
Tpages = (INT) math. Ceil (double) This. Total/This. maxline );
Cpages = (INT) math. Floor (double) offset/This. maxline + 1 );
// Retrieve the required records based on conditions
If (total> 0 ){
SQL = query + "Limit" + Offset + "," + maxline;
Rs = db.exe cutequery (SQL );
}
Return Rs;
}
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 part of a general class library ), we need to adapt to different databases, and applications based on this class (database) may also use different databases. Therefore, we will not use this method.

Another tedious Implementation Method
I have seen some people's practices (in fact, this method was used at the beginning, including me), that is, no encapsulation is used, where paging is required, directly perform the resultset operation to roll to the corresponding location and then read the corresponding number of records. The typical code is as follows:
<%
Sqlstmt = sqlcon. createstatement (Java. SQL. resultset. type_scroll_insensitive,
Java. SQL. resultset. concur_read_only );
Strsql = "Select name, age from test ";
// Execute the SQL statement and obtain the result set
Sqlrst = sqlstmt.exe cutequery (strsql );
// Obtain the total number of records
Sqlrst. Last ();
Introwcount = sqlrst. getrow ();
// Calculate the total number of pages
Intpagecount = (introwcount + intPageSize-1)/intpagesize;
// Adjust the page number to be displayed
If (intpage> intpagecount) intpage = intpagecount;
%>
<Table border = "1" cellspacing = "0" cellpadding = "0">
<Tr>
<TH> name </Th>
<TH> age </Th>
</Tr>
<%
If (intpagecount> 0 ){
// Locate the record pointer to the first record on the page to be displayed.
Sqlrst. Absolute (intPage-1) * intpagesize + 1 );
// Display data
I = 0;
While (I <intpagesize &&! Sqlrst. isafterlast ()){
%>
<Tr>
<TD> <% = sqlrst. getstring (1) %> </TD>
<TD> <% = sqlrst. getstring (2) %> </TD>
</Tr>
<%
Sqlrst. Next ();
I ++;
}
}
%>
</Table>
Obviously, this method does not take into account the issue of code reuse. It not only has a large number of codes, but also will be at a loss when the code needs to be modified.

Pagination Using Vector
We have also seen other paging classes. They first select all the records, then get the data in the resultset, store the data in the collection classes such as vector, and then according to the size of the required pages, page number, locate the corresponding location, and read the data. Or use the two paging methods mentioned above to obtain the required page and store it in the vector.
The efficiency of code throwing is not to mention. Simply speaking about the program structure and ease of use, it is very bad. 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.

A new pageable interface and its implementation
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; it is best to reuse code; make sure that it is consistent with the usage of the original JDBC interface as much as possible.
First, we need to provide an interface that is backward compatible with Java. SQL. resultset and name it pageable. The interface is defined as follows:
Public interface pageable extends java. SQL. resultset {
/** Total number of returned pages
*/
Int getpagecount ();
/** Return the number of records on the current page
*/
Int getpagerowscount ();
/** Return page size
*/
Int getpagesize ();
/** Go to the specified page
*/
Void gotopage (INT page );
/** Set the page size
*/
Void setpagesize (INT pagesize );
/** Total number of returned records
*/
Int getrowscount ();
/**
* Go to the first record on the current page
* @ Exception java. SQL. sqlexception exception description.
*/
Void pagefirst () throws java. SQL. sqlexception;
/**
* Go to the last record on the current page
* @ Exception java. SQL. sqlexception exception description.
*/
Void pagelast () throws java. SQL. sqlexception;
/** Return the current page number
*/
Int getcurpage ();
}
This is an interface that extends java. SQL. resultset. It mainly adds paging support, such as setting the page size, redirecting to a page, and returning the total number of pages.
Next, we need to implement this interface. Because this interface inherits from the resultset, and most of its functions are the same as the original functions of the resultset, a simple decorator mode is used here.
The class declaration and member declaration of pageableresultset2 are as follows:
Public class pageableresultset2 implements pageable {
Protected java. SQL. resultset rs = NULL;
Protected int rowscount;
Protected int pagesize;
Protected int curpage;
Protected string command = "";
}
As you can see, pageableresultset2 contains a resultset instance (this instance only implements the resultset interface, in fact it is implemented by various database vendors ), all methods inherited by resultset are directly forwarded to the instance for processing.
Main methods inherited from resultset in pageableresultset2:
//......
Public Boolean next () throws sqlexception {
Return Rs. Next ();
}
//......
Public String getstring (string columnname) throws sqlexception {
Try {
Return Rs. getstring (columnname );
}
Catch (sqlexception e) {// here is to add some error information for debugging
Throw new sqlexception (E. tostring () + "columnname ="
+ Columnname + "SQL =" + this. getcommand ());
}
}
//......
Only the newly added methods in the pageable interface need their own write Method Processing.
/** For method annotations, see pageable. java.
*/
Public int getcurpage (){
Return curpage;
}
Public int getpagecount (){
If (rowscount = 0) return 0;
If (pagesize = 0) return 1;
// Calculate pagecount
Double tmpd = (double) rowscount/pagesize;
Int tmpi = (INT) tmpd;
If (tmpd> tmpi) tmpi ++;
Return tmpi;
}
Public int getpagerowscount (){
If (pagesize = 0) return rowscount;
If (getrowscount () = 0) return 0;
If (curpage! = Getpagecount () return pagesize;
Return rowscount-(getpagecount ()-1) * pagesize;
}
Public int getpagesize (){
Return pagesize;
}
Public int getrowscount (){
Return rowscount;
}
Public void gotopage (INT page ){
If (rs = NULL)
Return;
If (page <1)
Page = 1;
If (page> getpagecount ())
Page = getpagecount ();
Int ROW = (page-1) * pagesize + 1;
Try {
Rs. Absolute (ROW );
Curpage = page;
}
Catch (Java. SQL. sqlexception e ){
}
}
Public void pagefirst () throws java. SQL. sqlexception {
Int ROW = (curPage-1) * pagesize + 1;
Rs. Absolute (ROW );
}
Public void pagelast () throws java. SQL. sqlexception {
Int ROW = (curPage-1) * pagesize + getpagerowscount ();
Rs. Absolute (ROW );
}
Public void setpagesize (INT pagesize ){
If (pagesize> = 0 ){
This. pagesize = pagesize;
Curpage = 1;
}
}
The constructor of pageableresultset2 is as follows:
Public pageableresultset2 (Java. SQL. resultset RS) throws java. SQL. sqlexception {
If (rs = NULL) throw new sqlexception ("given resultset is null", "user ");

Rs. Last ();
Rowscount = Rs. getrow ();
Rs. beforefirst ();

This. rs = RS;
}
Here, we simply get a total number of records, move the record cursor back to the initial position (before first), and assign the resultset In the parameter to the member variable.

How to Use pageable
Because the pageable interface inherits from the resultset, it is used in the same way as the resultset, especially when the paging function is not required, it can be directly used as a resultset. When paging is required, you only need a simple setpagesize and gotopage.
Preparedstatement pstmt = NULL;
Pageable rs = NULL;
...... // Construct the SQL statement and prepare a pstmt.
Rs = new pageableresultset2(pstmt.exe cutequery (); // construct a pageable
Rs. setpagesize (20); // 20 records per page
Rs. gotopage (2); // jump to page 2nd
For (INT I = 0; I <Rs. getpagerowscount (); I ++) {// loop Processing
Int id = Rs. getint ("ID ");
...... // Continue processing
}

Summary
A good basic class should be easy to use and have sufficient portability, while ensuring its function perfection. 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.
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.
Of course, it also has full portability. When you change the database from Oracle to MySQL or SQL Server, 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.
OK. Is the implementation of this paging function 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.