Foreword JDBC and pagination and specific databases Realization Methods Another cumbersome implementation method uses Vector to page a new pageable interface and its implementation of the use of the reference information About the author
Preface In the process of using the database, inevitably need to use the paging function, but JDBC's specifications are not well solved. For this requirement, many friends have their own solutions, such as using the set classes such as Vector, first save the extracted data and then paging. However, this method is very good, and it is completely different from the JDBC itself, and the support of different types of fields is not good. Here is a very good program with JDBC compatibility.
JDBC and page SUN's JDBC specification, sometimes it is very cried, in JDBC1.0, for a result set (Resultset), you can even perform Next () operations, and can not make it backward, this is directly Resulting in the case where only one SQL query is executed, the size of the result set cannot be obtained. So, if you use the driver of JDBC1.0, it is almost unable to implement pagination. It is good to make up for this shortcoming in Sun's JDBC2 specification, adding the front and rear rolling operations of the result set, although it is still not directly supporting paging, but we can already write your own supportable ResultSet on this basis. .
There are some databases, such as MySQL, Oracle, etc., such as mysql, oracle, etc. Here is the mysql as an example, and the typical code is as follows: // Calculate the total number of records String SQL = "Select count (*) as total this.querypart; rs = db.executeQuery (sql); if (rs.next ()) Total = rs.Getint (1); // Set the current number of pages and total page TPAGES = (int) Math.ceil ((double) this.total/thismaxline); cpages = (int) Math. FLOOR ((Double) Offset / this.maxline 1); // Depending on the condition, remove the required record IF (Total> 0) {SQL = query " Offset ", " Maxline; rs = DB This code will be beautiful when the database is mysql when the database is mysql, but as a universal class (in fact I have to provide later). Some), there is a need to adapt to different databases, and this class (library) application may also use different databases, so we will not use this method.
Another cumbersome implementation method I have seen some people's approach (in fact, I use this method, I use this method at the beginning), that is, I don't use any packages, where you need paging, directly Operate the resultset roll to the corresponding The location, read the corresponding number of records. 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 SQL statements and obtain result set SQLRST = SQLSTMT.EXECUTEQUERY (STRSQL); // Get the total number of records sqlrst.last (); IntrowCount = Sqlrst.Getrow (); // Mode total page INTPAGECOUNT = (Introwcount INTPAGESIZE-1) / INTPAGESIZE; // Adjustment Page code if (INTPAGE> INTPAGECUNT) INTPAGE = INTPAGECUNT;%>
Name
Age < / TH> <% if (INTPAGECUNT> 0) {// Position the record pointer to the first record of the page to be displayed on SQLRST.ABSOLUTE ((INTPAGE-1) * INTPAGESIZE 1); // Display Data i = 0; While (i
<% = SQLRST.GETSTRING (1)%>
<% = SQLRST. GetString (2)%> <% sqlrst.next (); i ;}}%> It is clear that this method does not take into account the problem of code reuse, not only the number of code is huge And if the code needs to be modified, it will be obeyed. Use the Vector to make some other class that implements paging, first to pick all the records SELECT, then put the data in the resultSet, save the Vector and other collections, then according to the size, page of the page Number, positioned to the corresponding position, read data. Or use the two paging methods mentioned earlier, after obtaining the desired page, then deposit into the Vector. The efficiency of throwing an open code does not say that the order is very bad on the convenience of the program structure and the use of use. For example, this practice supports the field type, int, double, and string types are also better. If you encounter blob, text, you are very troublesome. This is a more unsuitable solution.
A new pageable interface and its implementation are clear, after reading the above three implementation methods, we have a goal for the new paging mechanism, namely: not related to the specific database; doing code reuse; as far as possible The method of use of JDBC interfaces is consistent; as high efficiency as possible. First, we need to provide an interface compatible with java.sql.resultSet, name it as pageable, the interface is defined as follows: public interface pageable extends java.sql.resultset {/ ** Return to the total page * / int getPageCount ); / ** Return to the current page number * / int getpagerowscount (); / ** Return to the page size * / int getpageSize (); / ** Go to the specified page * / void gotopage (int page); / * * Set the page size * / void setpageSize (int pageize); / ** Return to the total number of records * / int achieveScount (); / ** * 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 of 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 java.sql.resultset, mainly to increase the paging Support, such as setting the page size, jump to a page, return to the total number of questions, and so on. Next, we need to implement this interface, because this interface is inherited from the Resultset, and its most features are the same as the result of the result, so you use a simple Decorator mode. And members of the class declaration PageableResultSet2 following statement: public class PageableResultSet2 implements Pageable {protected java.sql.ResultSet rs = null; protected int rowsCount; protected int pageSize; protected int curPage; protected String command = "";} can be seen, PageAbleresultSet2, contains an instance of a resultset (this instance just implements the ResultSet interface, in fact it is implemented by each database vendor), and all methods inherited by the ResultSet are directly forwarded to this instance.
PageableResultSet2 main methods inherited from the ResultSet: // ...... public boolean next () throws SQLException {return rs.next ();} // ...... public String getString (String columnName) throws SQLException {try {return rs.getString ( COLumnName);} catch (sqlexception e) {// This is to add some error information to easily debug new sqlexception (e.tostring () "columnname =" colornname "/ r / nsql =" this.getCommand ());}} // ...... Only the new method in the Pageable interface is required to handle your write method.
/ ** Method Note You 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 (IF (rs == null) Return; IF (Page> getPageCount ()) Page = getPageCount (); int = (PAGE - 1) * PageSize 1; try {r.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 constructor: 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 just a total of a total number of records and moves the record cursor back to the initial location (Before first), and assigns ResultSet in the parameter to a member variable.
Pageable How to Use Because the pageable interface is inherited from the ResultSet, it can be used directly as a Resultset while using the RESULTSET, especially when there is no patch function. When you need a paging, you only need simple setPagesize, gotopage, you can. PreparedStatement PSTMT = NULL; pageable = null; ... // Construct SQL, and prepare a pSTMT.RS = New pageableResultSet2 (pstmt.executeQuery ()); // Construct a pageabless.setpagesize (20); // Each page 20 Record rs.gotopage (2); // Jump to Page 2 for (int i = 0; i
Summarizing a good basic class should be easy to use, and have sufficient portability, while ensuring the improvement of its functions. In the above implementation, we inherit the pageable from the java.sql.resultset interface and implemented it. This guarantees the consistency of the original operation with the JDBC in use, while the original function is not reduced. At the same time, it is also easy to use, because all the necessary operations are encapsulated, so the only place "ugly" and "uncomfortable" place in your code is to construct a pageableresultset2. However, as long as you are willing, this is also solved. Of course, it also has sufficient portability, when you turn the database from Oracle to MySQL or SQL Server, you can still use these paging code. It is in use (or in the process of transplantation) The only limit is that you have to use a driver that supports JDBC2 (now I understand why I named PageAperesultset2.: P), but in JDBC2 has become The standard, most databases such as Oracle, MySQL, SQLServer have their own or third-party drivers for JDBC2 provided by third parties. OK, is this paging to achieve help your programming? Take a closer look, in fact, there is not much code you write, most of which are just simple forwarding operations. A suitable mode application can help you very busy.
