The best JSP paging technology at present

js| Paging

Objective
In the use of the database process, the inevitable need to use the paging function, but the JDBC specification is not a good solution. Many friends have their own solutions to this requirement, such as using vectors such as collection classes to save the extracted data before paging. However, the usability of this method is very poor, and the interface with JDBC itself is completely different, the support of different types of fields is not good. This provides a very good solution for JDBC compatibility.
JDBC and Paging
The development of Sun's JDBC specification, sometimes very annoying, in JDBC1.0, for a result set (ResultSet) you can even only perform next () operation, and can not be allowed to scroll backwards, which directly results in the execution of only one SQL query can not get the size of the result set. So, if you're using a JDBC1.0 driver, it's almost impossible to achieve paging.
Fortunately, the Sun's JDBC2 specifications to make up for this shortcoming, increased the result set before and after scrolling operation, although still can not directly support paging, but we can write their own on this basis to support pagination of the resultset.

Implementation methods related to specific database
Some databases, such as MySQL, Oracle, and so on have their own paging methods, such as MySQL can use the limit clause, Oracle can use rownum to limit the size and starting position of the result set. Here, for example, MySQL, the typical code is as follows:
Calculate the total number of record bars
String SQL = "Select Count (*) as Total" + this. Querypart;
rs = Db.executequery (SQL);
if (Rs.next ())
Total = Rs.getint (1);
Set current page and total pages
Tpages = (int) Math.ceil (double) this. Total/this. Maxline);
Cpages = (int) Math.floor (double) offset/this. MAXLINE+1);
According to the conditions, take out the required records
if (Total > 0) {
SQL = Query + "LIMIT" + Offset + "," + maxline;
rs = Db.executequery (SQL);
}
Return RS;
}
There is no doubt that this code will be pretty when the database is MySQL, but as a generic class (in fact, I'm going to provide a part of a common class library later), you need to adapt to different databases, and depending on the application of this class (library), you may also use different databases, so We will not use this method.

Another kind of tedious implementation method
I have seen some people do (in fact, including me, in the first place also use this method), that is, do not use any encapsulation, where the need for paging, direct operation resultset Roll to the appropriate 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 get the result set
Sqlrst = Sqlstmt.executequery (strSQL);
Get Total Records
Sqlrst.last ();
Introwcount = Sqlrst.getrow ();
Count Total Pages
Intpagecount = (introwcount+intpagesize-1)/intpagesize;
Adjust the number of pages 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) {
Position the record pointer over the first record of the page you want to display
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 approach does not take into account the problem of code reuse, not only a large number of code, and the code needs to be modified, will be confused.

Paging by using vector
Also see some other implementation of the paging class, is the first to select all the records, and then the data in the resultset get out, into the vector and other collection classes, and then according to the required paging size, the number of pages, positioned to the appropriate location, read data. Alternatively, use the two paging methods mentioned earlier to get the desired page and then save it in the vector.
The efficiency of throwing the code aside, it's very bad to say from the program structure and ease of use. For example, this practice supports a limited number of field types, int, double, string type is also better to handle, if you encounter blobs, text and other types, to achieve a very cumbersome. This is a more undesirable option.

A new pageable interface and its implementation
Obviously, after looking at the above three implementations, we have a goal for the new paging mechanism: not related to the specific database, as much code reuse as possible, consistent with the way the original JDBC interface is used, as efficiently as possible.
First, we need to provide an interface that is backward compatible with Java.sql.ResultSet, name it pageable, and the interface is defined as follows:
Public interface pageable extends java.sql.resultset{
/** return Total Pages
*/
int Getpagecount ();
/** returns the number of record bars for the current page
*/
int Getpagerowscount ();
/** Back to paging size
*/
int getpagesize ();
/** go to the specified page
*/
void gotoPage (int page);
/** Setting Paging size
*/
void setpagesize (int pageSize);
/** returns the total number of record rows
*/
int Getrowscount ();
/**
* Go to the first record of 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;
/** returns the current page number
*/
int Getcurpage ();
}
This is an extension of the Java.sql.ResultSet interface, mainly increased support for paging, such as setting paging size, jump to a page, return the total number of pages, and so on.
Next, we need to implement this interface, because this interface inherits from ResultSet, and most of its functions are the same as the original function of resultset, so a simple decorator pattern is used here.
PageableResultSet2 's class declarations and member declarations 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, in PageableResultSet2, a resultset instance is included (this example only implements the ResultSet interface, which is actually implemented by individual database vendors), And all methods inherited by ResultSet are forwarded directly to the instance for processing.
The main methods of inheriting 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 increase the content of error messages for easy debugging
throw new SQLException (e.tostring () + "Columnname="
+columnname+ "sql=" +this.getcommand ());
}
}
......
Only the new method in the Pageable interface needs its own write method to handle.
/** method annotation can refer to 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 construction method of PageableResultSet2:
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;
}
This is simply to get a total number of records and move the record cursor back to the original position (before first) while assigning the resultset to the member variable in the parameter.

How to use pageable
Because the Pageable interface inherits from ResultSet, it is consistent with resultset in its use, especially when it does not require paging functionality, and can be used directly as a resultset. And in need of paging, just want simple setpagesize, gotoPage, you can.
PreparedStatement Pstmt=null;
Pageable Rs=null;
.//Construct SQL, and prepare a pstmt.
Rs=new PageableResultSet2 (Pstmt.executequery ());//Construct a pageable
Rs.setpagesize (20);//20 records per page
Rs.gotopage (2);//Jump to 2nd page
for (int i=0; I<rs.getpagerowscount (); i++) {//cyclic processing
int Id=rs.getint ("id");
.../Continue processing

Rs.next ();
}

Summarize
A good base class should be easy to use and portable enough to ensure that its functionality is perfected. In the above implementation, we inherit the pageable from the Java.sql.ResultSet interface and implement it. This ensures consistency with the original operation of JDBC in use and does not shrink from the original functionality.
It's also easy to use because it encapsulates all the necessary operations, so the only place in your code that looks "ugly" and "uncomfortable" is to build a PageableResultSet2 yourself. But as long as you want, it can be solved.
Of course it also has full portability, and you can still use the paging code when you change the database from Oracle to MySQL or SQL Server. The only limitation in its use (or in the process of porting) is that you have to use a driver that supports JDBC2 (now understand why I named the class PageableResultSet2). :P), but fortunately JDBC2 has become standard, most databases (Oracle, MYSQL, SQL Server, for example) have their own or third-party-provided JDBC2 drivers.
OK, is this paging implementation helpful to your programming? Take a closer look, in fact, actually write their own code is not much, most of them are simply forwarding operations. A suitable pattern application can help you a lot.