Use tool class to implement universal paging processing original published in JavaResearch.org
Currently, the paging mode is to cacize the query results in httpsession or state beans, and remove one page data from the cache when turning pages. This approach has two main disadvantages: First, the user may see that expired data; the second is that the first query traversal assembly takes a long time if the data volume is very large, and the cached data will take up a lot of memory. The efficiency is significantly dropped.
Other common methods include querying a database each time, taken only one page data from the ResultSet (using rs.last (); rs.getrow () get the total number of recordings, use rs.absolute () to locate This page started record). This approach is similar to the JDBC implementation of some databases (such as Oracle). It is also necessary to traverse all records. The experiment proves that the speed is very slow when the number of records is very slow.
As for the cache result set, the method of ResultSet is completely a wrong approach. Because the ResultSet is closed when Statement or Connection is closed, if you want to make the ResultSet effect, you will always take up a database connection.
Therefore, the better paging approach should be data from the block size of the page size from the database every time you turn pages. Thus, although the database is queried each time, the number of records that queries is small, the network transmission data is not large, and if the connection pool can be used, it can be skipped by the most time consuming database connection process. There are various mature optimization techniques in the database to improve query speed, which is much more effective than making a cache in the application server.
The line number of the query result in the Oracle database uses a pseudo column ROWNUM (starting from 1). For example, Select * from Employee Where Rownum <10 Returns the top 10 records. However, because rownum is assigned before the query is sorted, the query Employee is written in the 100th to 120 records sorted by BirthDay:
Select * from (select my_table. *, rownum as my_rownum from (select name, birthday from employee) my_table where rownum <120) where my_rownum> = 100
Mysql can use the LIMIT clause:
Select Name, Birthday from Employee ORDER BY BIRTHDAY LIMIT 99, 20
DB2 has a ROWNUMBER () function to get the current number of rows.
SQL Server has not studied, you can refer to this article:
http://www.9cbs.net/develop/Article/18/18627.shtm
The paging in the web program will be frequently used, but the implementation details of the paging are more troublesome during the programming process. Most pags displayed query operations need to handle complex multiple query conditions, SQL statements require dynamic splicing composed, plus paging required record positioning, total records of records, traversal, package, and display, program meeting Becomes complicated and difficult to understand. Therefore, some tool classes simplify the paging code, so that the programmer focuses on the business logic. Here is two tools I have designed:
PageDStatement encapsulates a database connection, a total record number query, a page query, a result of data package, and shuts down a database connection, and uses preparedStatement supports dynamic setting parameters.
ROWSETPAGE Reference Page By Page Iterator Mode for PetStore, Design RowSetPage for Packaging Query Results (one page with OracleCachedRowSet Cache queries, please refer to the JSP page query display common mode for the use of the CachedRowSet package database query) The number of strips, current record number, etc., and can generate a simple HTML paging code.
The result of the PageDStatement query is encapsulated into rowsetpage.
The following is simple
Use example:
// DAO data query part of the code: ... public RowSetPage getEmployee (String gender, int pageNo) throws Exception {String sql =; // use paging query Oracle database "select emp_id, emp_code, user_name, real_name from employee where gender =?" Implementation, 5 PageDStatement PST = New PageDStatementOracleImpl (SQL, PAGENO, 5); PST.STSTRING (1, GENDER); Return Pst.executeQuery ();} // servlet Processing Query Request section code: ... int Pageno; Try {// You can get user-selected page number Pageno = Integer.PARSEINT (Request.getParameter ("Pageno"));} Catch (Exception EX) {// The default is the first page Pageno = 1;} String Gender = Request.getParameter ("gender"); request.setttribute ("EMPPAGE", MyBean.geTemPloyee (GENDER, PAGENO)); ... // JSP Display Some Code <% @ Page Import = "Page.RowSetPage"%> ... < Script language = "javascript"> Function doQuery () {form1.ActionType.Value = "doQuery"; form1.Submit ();} ...
