In web applications, it is inevitable to deal with the database. How to deal with the result set of returns? 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. code show as below:
ResultFilter filter = new ResultFilter (); filter.setStartIndex (start); // Section start thread start filter.setNumResults (range) from; // Set the moderation level minimumfilter.setModerationRangeMin // page size (forum.getModerationMinThreadValue ()) ; // More forum propertiesint numThreads = forum.getThreadCount (filter); int numMessages = forum.getMessageCount (filter); // Iterator of threadsForumThreadIterator 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 DBFORUM.THREADS ()
public ForumThreadIterator threads (ResultFilter resultFilter) {// passed in a configuration resultFilter the SQL query (* Note 1) String query = getThreadListSQL (resultFilter, false); // Returns a list id returned determined, where 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, it is understood that the above code to a few, and then look at the 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); // get a maximum of BLOCK_SIZE article, i.e. 400 // Position the cursor right before the first row that we'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 Seeing this, I suddenly realized that the code here is just copying, but it is much higher than my previous copy to Enumeration. First, here limits the size of the JDBC return result set. Second, the content of the copy is just a "pointer" (嘿, I like this word). This is better to solve the problem of slower and waste memory.
