In Web applications, it is inevitable to deal with databases. What should I do with the returned result set?
In particular, when a query may return tens of thousands of records, you can also use RS as described in JSP spam books that cheat money and cheat energy. next?
Well, I used to do this. I directly returned the resultset and processed it one by one in the while (Rs. Next () loop in JSP. Also, the issue of paging processing of returned results. In many cases, our web applications (or others) are targeted at a certain database (I think most commercial applications are Oracle, most of them are MySql in their spare time, And they dare to connect to sqlserver using JDBC, it is estimated that it has been abandoned by M $ ). Therefore, I often write some special SQL statements or stored procedures for some database features to determine the size of the returned result set to achieve paging processing (in fact, I am a database idiot, except select/insert/update, others do not know !). I used to use pseudo columns to implement the paging of the record result set processed on Oracle. Slowly, for the so-called lowerCodeCoupling, I reconstructed the code of the query result set, so I improved a little bit. An enumeration exists in a class such as querymanager, each object corresponds to a record in the database. Hey, you can even deal with more than 500 users.
However, this is not the best way. After all, if tens of thousands of records are returned, such a processing method will not only occupy the memory, but also the efficiency. I remember seeing a discussion about "RS returned from the query result or set" in bqlr.com. The answer is iterator.
However, the code inside is very general. For those who have never used the iterator mode, there is basically nothing to imitate and reference.
Fortunately, we have a ready-made product, that is, jive.
Let's see how it is implemented in jive. (A) Take the Forum. jsp page as an example. By default, 15 threads are displayed on each page. You can specify to display n + page_size entries starting from N.
The Code is as follows: Resultfilter filter = new resultfilter ();
Filter. setstartindex (start); // starts from the start thread.
Filter. setnumresults (range); // page size
// Set the moderation level minimum
Filter. setmoderationrangemin (Forum. getmoderationminthreadvalue ());
// More Forum Properties
Int numthreads = Forum. getthreadcount (filter );
Int nummessages = Forum. getmessagecount (filter );
// Iterator of threads
Forumthreaditerator threads = Forum. threads (filter); // return thread iterator, By analyzing the code above, it is clear that the resultfilter, dbforum, and forumthreaditerator classes need to be analyzed. (B) Check resultfilter. java.
This class is actually dedicated to saving query conditions. For example, you can query the posts published by someone a day ago. All query conditions of a query operation are saved in this class.
There is a function in it that is interesting: Public void setsortpropertyname (string sortpropertyname). You can use "whether a property exists" as the query condition. I originally wanted to add the thread topping function to jive. It seems that I only need to add a topmost attribute to the thread to be topped, and then modify the skin. It can be said that it will not take any effort. After filling in the query conditions, you can pass the resultfilter to Forum. threads () to obtain the result. (C) view dbforum. threads () Public forumthreaditerator threads (resultfilter ){
// Construct a query SQL statement based on the imported resultfilter (* Note 1)
String query = getthreadlistsql (resultfilter, false );
// Return a list of IDs to be returned. The default size is 400. For example, I want to see 1 ~ 15 records
// Threadblock length is 400, from 1 ~ 400 (* Note 2)
Long [] threadblock = getblock (query. tostring (), resultfilter. getstartindex ());
Int startindex = resultfilter. getstartindex ();
Int endindex;
// If Number of results is set to inifinite, set endindex to the total
// Number of threads in the Forum.
If (resultfilter. getnumresults () = resultfilter. null_int ){
Endindex = (INT) getthreadcount (resultfilter );
}
Else {
Endindex = resultfilter. getnumresults () + startindex;
}
// Hey hey, understand the above Code first, and then let's take a look at this iterator
Return new forumthreadblockiterator (threadblock, query. tostring (),
Startindex, endindex, this. ID, factory );
} OK, about (Note 1)
I have analyzed getthreadlistsql. Here, an SQL query statement is constructed. The first part of this statement is:
Select jivethread. threadid from jivethread, XXXXX
Xxxxx is only a query condition and is automatically generated based on the query conditions in resultfilter.
It turns out that if the SQL statement is executed, only the idnumber of the thread meeting the conditions can be retrieved. (Note 2) getblock ()
My understanding of the block here is that it refers to the sequence number set after the query results meet the query conditions are sorted, which is different from the threadid in the database.
The starindex here is the array, that is, the subscript of the block, and the content inside (long object) is the real threadid.
The size of each block array in the Code is fixed to 400.
For example:
If a query returns 1000 records, I want to start from 510th records (startindex = 510), that is, take the second block (blockid = 1), and the block start position (blockstart) is
500. Int blockid = startindex/block_size;
Int blockstart = blockid * block_size; If you still have patience and read the jive code, you will find that the addition is nothing more than retrieving the block array from the cache. If it is not found, then it is honestly retrieved from the database, put it into the array, put it into the cache, and finally throw this array.
The database processing code here is relatively common Con = connectionmanager. getconnection ();
Stmt = connectionmanager. createscrollablestatement (CON );
// Set the maximum number of rows to end at the end of this block.
Connectionmanager. setmaxrows (stmt, block_size * (blockid + 1 ));
Resultset rs = stmt.exe cutequery (query );
// Grab block_size rows at a time.
Connectionmanager. setfetchsize (RS, block_size); // a maximum of 400 block_size entries can be obtained at a time.
// Position the cursor right before the first row that we're re insterested in.
Connectionmanager. scrollresultset (RS, blockstart); // scroll cursor to blockstart
// Keep reading results until the result set is exhausted or
// We come to the end of the block.
Int COUNT = 0;
While (Rs. Next () & count <block_size ){
Objectlist. Add (Rs. getlong (1 ));
Count ++;
} Seeing this, I suddenly realized that the code here is just a copy, but it is much more advanced than copying it to enumeration.
First, the size of the result set returned by JDBC is limited.
Second, the copied content is just a "Pointer" (hey, I like to use this word ).
In this way, the speed is slow and the memory is wasted. (C) Hey, the next thing is relatively simple.
1. Calculate the relative position in the block according to startindex, that is, the subscript in the blockarray, and obtain the content, that is, the real threadid.
2. Go to threadcache to get the threadobject. If it cannot be found, the old New thread (threadid) will be retrieved from the database.
All of this is implemented through hasnext ()/next ()/getelement () in forumthreaditerator. It's easy for everyone to figure it out. |
