Paging Processing in JIVE BY ShyGuy2002 / 09/18 Reprint Please indicate that the author and you are in a web application, inevitably deal with the database, how to deal with the result set, how to deal with? Especially when your query may return tens of thousands of records, you can also deal with rsp.next () as some JSP garbage books that deceive money. Oh, I used to do this, I passed back to the ResultSet, and a one in the JSP (RS.Next ()) loop. Also, the problem of returning the resulting paging processing. Many times our web application (or other) is for a database (mostly the most commercial application is oracle, amateur is much mysql, and dare to connect SQLServer with JDBC, it is estimated to be scrapped by M. ). So I often write some special SQL statements or stored procedures for the characteristics of some databases to determine the size of the return result set to achieve paging processing (in fact even database, except for select / insert / update, others don't know! ). I used to use a pseudo column to implement the paging of the record result set on Oracle. Slowly, in order to reduce the code coupling degree, I repeat the code of the query result set, so I have made a little progress, and I have an enumeration in a class such as a QueryManager, and each object inside corresponds to each object. A record in the database. Hey, actually can also make more than 500 people used. However, this is not the best way. After all, if you really return tens of thousands of records, such processing methods not only account for memory, but also efficient. I remember to see a discussion about "Query Results Returns RS or Collection" in bqlr.com, the answer is to use Iterator. However, the code inside is generally, and for those who have never used Iterator mode, there is basically nothing to imitate and refer to. Fortunately, we have ready, that is JIVE. See how JIVE is implemented. (A) We use the page of Forum.jsp as an example, the default, each page displays 15 Thread, and the user can specify that the N Page_Size strip is displayed from the NOS. The code is as follows: resultfilter filter = new resultfilter ();
Filter.setStartIndex (start); // Starting from the Start Bar 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);
// item of threads
FORUMTHREADITERATOR THREADS = forum.threads (filter); // Return Thread Iterator,
By analyzing the above code, it is clear that the three classes of Resultfilter, DBForum, and ForumThreadItemator are analyzed. (B) Seeing the result of the resultfilter.java is actually saving the query condition, for example, you can query a post before a day, the post, etc. All query conditions for a query operation are saved in this class. There is a function in the inside: public void setsortpropertyName (String SortpropertyName), 嘿嘿 You can use a protroperty as a query condition. I have just want to add THREAD to JIVE to add a TopMost property to the THREAD that needs to be set, and then change Skin. It can be said that it is not enough to work hard. After filling the query condition, you can pass the resultfilter to forum.threads (). (C) Look at dbForum.threads () public foruMthreadITERATOR THREADS (RESULTFILTER RESULTFILTER) {// Constructs a query SQL statement based on incoming Resultfilter (* Note 1)
String query = getthreadListSql (Resultfilter, false);
// Return a list of IDs that are determined, the default size is 400, for example, I want to see 1 ~ 15 records
// Threadblock's Length is 400, from 1 to 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, understand the above code first, then look at this Iterator
Return New ForumThreadBlockITerator (Threadblock, Query.toString (),
StartIndex, EndIndex, this.id, factory;
}
OK, about (Note 1) I analyzed GetThreadListSQL, here is a SQL query statement, some of the content in front of this statement is: select jivethread.threadid from jivethread, xxxxx Those xxxxx is nothing more than some query conditions, according to Resultfilter Query conditions are automatically generated. It turns out that if the role of this SQL can only retrieve the ID number of the THREAD that satisfies the condition. (Note 2) GetBlock () I understand the BLOCK here is that the query result satisfying the query condition is sorted by the serial number set, and the ThreadId in the database is different. The StarIndex here is an array, that is, the BLOCK's subscript, and the contents of the content (long object), is a fixed 400 in the true ThreadID code for each Block array. For example: if a query returns 1000 records, I have to start from paragraph 510 (StartIndex = 510), then take the second block (blockID = 1), the block start position is 500. Int blockid = startIndex / block_size; int blockstart = blockid * block_size;
If you still have tolerance, then read the JIVE code, it will find that add to the block array from cache. If you don't find it, you will take it out from the database, old and old Put it in an array, put it in 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.executeQuery (Query);
// Grab Block_Size Rows At A Time.
ConnectionManager.SetFetchSize (RS, block_size); // Take up to Block_Size strips once, that is, 400
// Position The Cursor Right Before The First Row That We're INSTERESTED IN.
ConnectionManager.ScrollResultSet (RS, BlockStart); // Scroll the cursor to BlockStart
// Keep Reading Results Until The Result Set Is Exhausted OR
// We com to the end of the block.
INT count = 0;
While (rs.next () && count ObjectList.Add (rs.getlong (1)); COUNT ; }
