The best JSP paging technology right now

2005-08-24 Source: CSDN Author: wanchao2001

Objective
In the process of using the database, it is unavoidable to use the function of paging, but the specification of JDBC does not solve it very well. Many friends have their own solution to this requirement, such as using a collection class such as vectors to save the extracted data before paging. However, the usability of such a method is very poor, unlike the interface of JDBC itself, and the support for different types of fields is not good. This provides a very good solution for JDBC compatibility.
JDBC and Paging
The development of the Sun's JDBC specification is sometimes very annoying, and in JDBC1.0, you can even run the next () operation for a result set (ResultSet) without having to scroll backwards. This leads directly to the inability to get the size of the result set in the case of a SQL query that runs only once. So, assuming you're using a JDBC1.0 driver, it's almost impossible to achieve paging.
Fortunately, the Sun's JDBC2 specification makes up for this shortcoming, adding? The pre-and post-scrolling operations of the result set, although still not directly supporting paging, we have been able to write our own resultset to support paging on this basis.

and detailed database-related implementation methods
Some databases, such as MySQL, Oracle, etc. have their own paging methods, for example, MySQL can use the limit clause, and Oracle can use RowNum to limit the size and starting position of the result set. Here, for example, the typical code for MySQL is the following:
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 the current page and total pages
Tpages = (int) Math.ceil ((double) this. Total/this. MaxLine);
Cpages = (int) Math.floor ((double) offset/this. MAXLINE+1);
Infer by condition, take out the required records
if (Total > 0) {
SQL = Query + "LIMIT" + Offset + "," + MaxLine;
rs = Db.executequery (SQL);
}
Return RS;
}
There's no doubt that this code will be beautiful when the database is MySQL, but as a generic class (What I'm going to provide is part of a general-purpose class library) that needs to be adapted to different databases, and based on this class (library) application, it's possible to use a different database, so We will not use such a method.

There is also a tedious way to implement
I have seen some people's practice (in fact, including me, in the beginning is also used in this way), that is, no matter what package, in the need to page, the direct operation of the ResultSet roll to the corresponding position, and then read the corresponding number of records. Its 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";
Run 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 page numbers 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 on the first record of the page you want to display
Sqlrst.absolute ((intPage-1) * intpagesize + 1);
Show 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, such a method does not take into account the problem of code reuse, not only the number of code is huge, and in the case of code needs to change, will be at a loss.

Using Vectors for paging
Also saw some of the implementation of paging class, is to first select all the records, and then the data in the resultset get out, into the vector collection class, and then according to the size of the paging, the number of pages, positioning to the corresponding 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, just from the procedural structure and ease of use, is very bad. For example, this practice supports a limited number of fields, int, double, string type is also more advantageous, assuming that the blob, text and other types, implementation is very troublesome. This is a much less desirable option.

A new pageable interface and its implementation
Obviously, having seen the three implementations above, we have a goal for the new paging mechanism: not to be related to the detailed database, to do the code reuse as much as possible, to be consistent with the usage of the original JDBC interface, and to be as efficient as possible.
First, we need to provide an interface that is backwards compatible with Java.sql.ResultSet, name it pageable, and interface definitions such as the following:
Public interface pageable extends java.sql.resultset{
/** return Total Pages
*/
int Getpagecount ();
/** returns the number of record bars for the current page
*/
int Getpagerowscount ();
/** Return 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 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;
/** returns the current page number
*/
int Getcurpage ();
}
This is a java.sql.ResultSet extension of the interface, mainly added to the page support, such as setting page 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 resultset, so a simple decorator mode is used here.
PageableResultSet2 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 = "";
}
Can see, in the PageableResultSet2, including a resultset instance (this instance is only implemented ResultSet interface, in fact, it is implemented by each database vendor separately), And all the methods inherited by ResultSet are forwarded directly to the instance for processing.
The main ways to inherit 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? The contents of some error messages are easy to debug
throw new SQLException (e.tostring () + "Columnname="
+columnname+ "sql=" +this.getcommand ());
}
}
......
Only the new method in the Pageable interface needs to be handled by its own writing method.
/** method Gaze can be 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;
}
}
PageableResultSet2 Method of Construction:
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 record count and move the record cursor back to the initial position (before first), assigning the resultset in the parameter to the member variable at the same time.

The use of pageable
Since the pageable interface inherits from the resultset, it is consistent with resultset in usage, especially when it is not necessary to use the paging function directly as resultset. And in need of paging, just need simple setpagesize, gotoPage, 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 page 2nd
for (int i=0; I<rs.getpagerowscount (); i++) {//Loop processing
int Id=rs.getint ("id");
...//Continue processing

Rs.next ();
}

  Summary
A good base class should be easy to use and sufficiently portable to ensure that its functionality is intact at the same time. In the above implementation, we inherit the pageable from the Java.sql.ResultSet interface and implement it. This ensures consistency with the original JDBC operation in use, with no reduction in the original functionality at the same time. The
is also easy to use at the same time, and because it encapsulates all the necessary actions, the only place in your code that looks "ugly" and "uncomfortable" is the need to construct a PageableResultSet2. Just want you to be willing, this also can solve.
Of course it also has full portability, and you can still use the paging code when you turn the database from Oracle to MySQL or SQL Server. Its only limitation in use (or in the process of porting) is that you need to use a driver that supports JDBC2 (and now it's clear why I named the class PageableResultSet2. :P), but fortunately JDBC2 has become a standard, the vast majority of databases (such as Oracle, MYSQL, SQL Server) have their own or third-party-provided JDBC2 drivers.
OK, does the implementation of this paging help your programming? Look carefully, in fact, the actual code is not much, most of it is simply a simple forwarding operation. A suitable pattern app can help you a lot.