A new pageable interface and its implementation paging technology

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 int id=rs.getint ("id");
.../Continue processing
}


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.