Paging inquiry operation technology about Java
An article saw online, combined with your own experience and everyone exchanges
Description of Paging Processing Problem for Large Quantities: Background 1: A customer query data through IE, and query results are thousands of or even tens of thousands of records, requiring query results to the IE client and displays. Background 2: A customer requests the web server to query the data through IE or other means, and the results of the query are thousands of or even tens of thousands of records, and ask the query result to send the package to the customer's e-mail. Q: How is the result set having a lot of data, how is the Web server? Problems that may be involved: 1. Memory occupies a large amount of data, which may take up very large memory 2. Transmission speed and strategy specific paging processing technology
Treatment method 1 The cursor query is directly handled directly. ResultSet is a record of the specified line location directly on the database and then locates the entry through the Row of ResultSet. When the user first requests the data query, the SQL statement query is executed, and the obtained resultSet object and the connection object thereto are saved to its corresponding session object. The subsequent paging query acquires the record of the specified line location by the first execution of the ResultSet object positioning. Finally, when the user is no longer paging query or when the session is closed, the database access resources such as the database connection and the ResultSet object are released. Explanation: During the entire session of the use of an example paging query, a user's paging query will occupy a database of database connection objects and result sets, which is relatively large for the database access resource, and its utilization is not very high. All database products. Advantages: Reduce multiple assignments of database connection objects, reduce the execution of SQL query on the database. Disadvantages: Occupation Database Access Resources - Database Connection Object, and occupies resources on the database - cursors. These resources are very valuable. Conclusion: This database query paging processing method is not optimal. This method is generally not applicable.
2 Location Rows SQL query is mainly SQL interface technology that directly uses the result set of the query to the results of the query. In the user's patch query request, each time you can obtain the parameters of the row range of the query request, then use these parameters to obtain the SQL query statement that specifies the range, and then requests a database connection object and performs SQL query, Returning the results of the query to the user, and finally releases the database to access the resources. Description: This method requires the database's SQL query statement to execute the database at a request; the access resource for the database is released immediately, and the database access resources are used to occupy the database. Database products for specific (provide a set of positionable functionality of query results). Such as: Oracle, DB2, PostgreSQL, MySQL, etc. (MS SQL Server does not provide this technology.) Such as: 1. Oracle Database Use Keywords: RowID or RowNum 2. DB2: RowID or Rownum () 3. PostgreSQL uses Limit and Offset4. MySQL uses the LIMIT Advantage: This technology is the function of filtering the search result to the query result set bit line range directly using the database product, so the performance of this paging query is directly utilized. There is no waste of access resources (database connection objects, database cursors, etc.) for databases (database connection objects, database cursors, etc.). There is no special request for the results of the query. Disadvantages: To perform multiple database SQL query operations. Each time the page operation request is specified to specify the result set of the corresponding range to perform the database query operation of the SQL statement, this has a certain impact on the database. Get database access resources (database connection objects and database cursors) from the Web container for each page query request. To rely on specific database products. This method is not available because database products that do not provide this technology are not available. Conclusion: Since each time the data resources are relatively small, the data resources are relatively consumed, and in the actual user's operation, it is possible that all result sets of the query only need to view some of the page. So this way is the best.
3 Specially processed positioning row set SQL query This method is a database product that does not provide a set line range of the query result in the basis of the way 2. It is the same as the WEB container ends. Just first need to query the database table to be queried to have a piece of data that can distinguish each different data record. When the first query is obtained, all result sets used to uniquely identify different records are obtained, and cache it to specify the row range of the result set to be queried. Mainly for database products obtained by different setup ranges to query rows, such as MS SQL Server, etc. Assume that data is selected from the three tables A, B, and C. And a field ID is used to distinguish different records. So when I query, I will query twice 1. SELECT A.ID from A, B, c where condition.2. Cache A to session? 3. From the session. The ID can be obtained in order, and the next query statement: SELECT A.NAME, B.Add from A, B, C Where Condition && (id in this page Id) When you turn your page, you can obtain the only page for the corresponding page. It doesn't matter of size, order? This way, the sessions cache is just a column rather than all columns. Of course, the effect is not good for the number of columns. You can also use the stored procedure implementation, please refer to: http: //expert.9cbs.net/expert/topic/2365/2365596.xml? Temp = .7529261 Advantages: Time 2 Disadvantages: Time 2; A corresponding ID is created in the database table, which is used to distinguish each record. Conclusion: The same paragraph 2.4 Caches a result set of SQL queries: Disadvantages: Since we want to cache the result, then the user may see expired data.
Explanation: For applications in the actual situation, generally combine actual conditions, combined with mode 2 (or ways 3) and mode 4. Such as: an application scenario: query is often the company's products, but the type of product is not a lot, then the cache method can be used; but the network access between some query results, the network access between the database and the Web container is possible It is a distance, which can be considered in the way (or 3).
/ / -------------------------------------------------------------------------------------------- -----------
Most of the previously doing it uses a paging similar to the way. If the query is frequent, the system access speed is the problem.
Method 2 is really a good method, but it is too dependent on the database, not the "best method" mentioned in the article.
Individuals believe that methods 1 and method 2 are combined to achieve better results. That is, the larger result set is first queried (may be 1/100 of the maximum collection), and then the page of the result set uses the paging of method 1. This is neither frequent access to the database, nor does it create too many objects. Of course, as mentioned in the article: MS SQL Server does not support positioning technology> _ <, so the positioning of the cursor needs to be resolved by the program.
Method 3 seems to be complementary to method 4. . . . Research in ing
Below is the implementation of the method 4 / / About the paging display, the system speeds due to excessive system, the system speed is too slow, suitable for the database update is not frequent
// Get an idListList IDList = New ArrayList (); rs = select id from db where conditionwhile (rs.next) {idlist.add (rs.getstring ("id"));}
// Get formListList Formlist = Test.GetIstListance (). GetInfo (IDLIST); // Create unique instance, only when the system calls this method for the first time, create an instance map m = New TreeMap (); rs = select * from dbWhile (rs.next) {Testform testform = new testform (); testform.setValue (...); m.put (testform.getf_id (), testform);} // IDLIST acquires the corresponding data from Map Public List GetInfo (List Idlist) {List INFOLIST = New ArrayList (); for (INT i = 0; i
String id = idlist.get (i);
Infolist.add (M.Get));
}
Return Infolist;
}
/ / Display content in FormList
