View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
17   */
18  
19  import java.io.IOException;
20  import java.io.ObjectInputStream;
21  import java.io.Serializable;
22  import java.lang.reflect.Method;
23  import java.sql.Connection;
24  import java.sql.SQLException;
25  import java.util.ArrayList;
26  import java.util.Hashtable;
27  import java.util.Iterator;
28  import java.util.List;
29  import java.util.Set;
30  
31  import org.apache.commons.logging.Log;
32  import org.apache.commons.logging.LogFactory;
33  import org.apache.torque.Torque;
34  import org.apache.torque.TorqueException;
35  import org.apache.torque.adapter.DB;
36  
37  import com.workingdogs.village.DataSetException;
38  import com.workingdogs.village.QueryDataSet;
39  
40  /***
41   * This class can be used to retrieve a large result set from a database query.
42   * The query is started and then rows are returned a page at a time.  The <code>
43   * LargeSelect</code> is meant to be placed into the Session or User.Temp, so
44   * that it can be used in response to several related requests.  Note that in
45   * order to use <code>LargeSelect</code> you need to be willing to accept the
46   * fact that the result set may become inconsistent with the database if updates
47   * are processed subsequent to the queries being executed.  Specifying a memory
48   * page limit of 1 will give you a consistent view of the records but the totals
49   * may not be accurate and the performance will be terrible.  In most cases
50   * the potential for inconsistencies data should not cause any serious problems
51   * and performance should be pretty good (but read on for further warnings).
52   *
53   * <p>The idea here is that the full query result would consume too much memory
54   * and if displayed to a user the page would be too long to be useful.  Rather
55   * than loading the full result set into memory, a window of data (the memory
56   * limit) is loaded and retrieved a page at a time.  If a request occurs for
57   * data that falls outside the currently loaded window of data then a new query
58   * is executed to fetch the required data.  Performance is optimized by
59   * starting a thread to execute the database query and fetch the results.  This
60   * will perform best when paging forwards through the data, but a minor
61   * optimization where the window is moved backwards by two rather than one page
62   * is included for when a user pages past the beginning of the window.
63   *
64   * <p>As the query is performed in in steps, it is often the case that the total
65   * number of records and pages of data is unknown.  <code>LargeSelect</code>
66   * provides various methods for indicating how many records and pages it is
67   * currently aware of and for presenting this information to users.
68   *
69   * <p><code>LargeSelect</code> utilises the <code>Criteria</code> methods
70   * <code>setOffset()</code> and <code>setLimit()</code> to limit the amount of
71   * data retrieved from the database - these values are either passed through to
72   * the DBMS when supported (efficient with the caveat below) or handled by
73   * the Village API when it is not (not so efficient).  At time of writing
74   * <code>Criteria</code> will only pass the offset and limit through to MySQL
75   * and PostgreSQL (with a few changes to <code>DBOracle</code> and <code>
76   * BasePeer</code> Oracle support can be implemented by utilising the <code>
77   * rownum</code> pseudo column).
78   *
79   * <p>As <code>LargeSelect</code> must re-execute the query each time the user
80   * pages out of the window of loaded data, you should consider the impact of
81   * non-index sort orderings and other criteria that will require the DBMS to
82   * execute the entire query before filtering down to the offset and limit either
83   * internally or via Village.
84   *
85   * <p>The memory limit defaults to 5 times the page size you specify, but
86   * alternative constructors and the class method <code>setMemoryPageLimit()
87   * </code> allow you to override this for a specific instance of
88   * <code>LargeSelect</code> or future instances respectively.
89   *
90   * <p>Some of the constructors allow you to specify the name of the class to use
91   * to build the returnd rows.  This works by using reflection to find <code>
92   * addSelectColumns(Criteria)</code> and <code>populateObjects(List)</code>
93   * methods to add the necessary select columns to the criteria (only if it
94   * doesn't already contain any) and to convert query results from Village
95   * <code>Record</code> objects to a class defined within the builder class.
96   * This allows you to use any of the Torque generated Peer classes, but also
97   * makes it fairly simple to construct business object classes that can be used
98   * for this purpose (simply copy and customise the <code>addSelectColumns()
99   * </code>, <code>populateObjects()</code>, <code>row2Object()</code> and <code>
100  * populateObject()</code> methods from an existing Peer class).
101  *
102  * <p>Typically you will create a <code>LargeSelect</code> using your <code>
103  * Criteria</code> (perhaps created from the results of a search parameter
104  * page), page size, memory page limit and return class name (for which you may
105  * have defined a business object class before hand) and place this in user.Temp
106  * thus:
107  *
108  * <pre>
109  *     data.getUser().setTemp("someName", largeSelect);
110  * </pre>
111  *
112  * <p>In your template you will then use something along the lines of:
113  *
114  * <pre>
115  *    #set($largeSelect = $data.User.getTemp("someName"))
116  *    #set($searchop = $data.Parameters.getString("searchop"))
117  *    #if($searchop.equals("prev"))
118  *      #set($recs = $largeSelect.PreviousResults)
119  *    #else
120  *      #if($searchop.equals("goto"))
121  *        #set($recs = $largeSelect.getPage($data.Parameters.getInt("page", 1)))
122  *      #else
123  *        #set($recs = $largeSelect.NextResults)
124  *      #end
125  *    #end
126  * </pre>
127  *
128  * <p>...to move through the records.  <code>LargeSelect</code> implements a
129  * number of convenience methods that make it easy to add all of the necessary
130  * bells and whistles to your template.
131  *
132  * @author <a href="mailto:john.mcnally@clearink.com">John D. McNally</a>
133  * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
134  * @version $Id: LargeSelect.java 280639 2005-09-13 20:17:34Z tfischer $
135  */
136 public class LargeSelect implements Runnable, Serializable
137 {
138     /*** The number of records that a page consists of.  */
139     private int pageSize;
140     /*** The maximum number of records to maintain in memory. */
141     private int memoryLimit;
142 
143     /*** The record number of the first record in memory. */
144     private transient int blockBegin = 0;
145     /*** The record number of the last record in memory. */
146     private transient int blockEnd;
147     /*** How much of the memory block is currently occupied with result data. */
148     private volatile int currentlyFilledTo = -1;
149 
150     /*** The SQL query that this <code>LargeSelect</code> represents. */
151     private String query;
152     /*** The database name to get from Torque. */
153     private String dbName;
154 
155     /*** The memory store of records. */
156     private transient List results = null;
157 
158     /*** The thread that executes the query. */
159     private transient Thread thread = null;
160     /***
161      * A flag used to kill the thread when the currently executing query is no
162      * longer required.
163      */
164     private transient volatile boolean killThread = false;
165     /*** A flag that indicates whether or not the query thread is running. */
166     private transient volatile boolean threadRunning = false;
167     /***
168      * An indication of whether or not the current query has completed
169      * processing.
170      */
171     private transient volatile boolean queryCompleted = false;
172     /***
173      * An indication of whether or not the totals (records and pages) are at
174      * their final values.
175      */
176     private transient boolean totalsFinalized = false;
177 
178     /*** The cursor position in the result set. */
179     private int position;
180     /*** The total number of pages known to exist. */
181     private int totalPages = -1;
182     /*** The total number of records known to exist. */
183     private int totalRecords = 0;
184 
185     /*** The criteria used for the query. */
186     private Criteria criteria = null;
187     /*** The last page of results that were returned. */
188     private transient List lastResults;
189 
190     /***
191      * The class that is possibly used to construct the criteria and used
192      * to transform the Village Records into the desired OM or business objects.
193      */
194     private Class returnBuilderClass = null;
195     /***
196      * A reference to the method in the return builder class that will
197      * convert the Village Records to the desired class.
198      */
199     private transient Method populateObjectsMethod = null;
200 
201     /***
202      * The default value ("&gt;") used to indicate that the total number of
203      * records or pages is unknown.
204      */
205     public static final String DEFAULT_MORE_INDICATOR = "&gt;";
206 
207     /***
208      * The value used to indicate that the total number of records or pages is
209      * unknown (default: "&gt;"). You can use <code>setMoreIndicator()</code>
210      * to change this to whatever value you like (e.g. "more than").
211      */
212     private static String moreIndicator = DEFAULT_MORE_INDICATOR;
213 
214     /***
215      * The default value for the maximum number of pages of data to be retained
216      * in memory.
217      */
218     public static final int DEFAULT_MEMORY_LIMIT_PAGES = 5;
219 
220     /***
221      * The maximum number of pages of data to be retained in memory.  Use
222      * <code>setMemoryPageLimit()</code> to provide your own value.
223      */
224     private static int memoryPageLimit = DEFAULT_MEMORY_LIMIT_PAGES;
225 
226     /*** A place to store search parameters that relate to this query. */
227     private Hashtable params = null;
228 
229     /*** Logging */
230     private static Log log = LogFactory.getLog(LargeSelect.class);
231 
232     /***
233      * Creates a LargeSelect whose results are returned as a <code>List</code>
234      * containing a maximum of <code>pageSize</code> Village <code>Record</code>
235      * objects at a time, maintaining a maximum of
236      * <code>LargeSelect.memoryPageLimit</code> pages of results in memory.
237      *
238      * @param criteria object used by BasePeer to build the query.  In order to
239      * allow this class to utilise database server implemented offsets and
240      * limits (when available), the provided criteria must not have any limit or
241      * offset defined.
242      * @param pageSize number of rows to return in one block.
243      * @throws IllegalArgumentException if <code>criteria</code> uses one or
244      * both of offset and limit, or if <code>pageSize</code> is less than 1;
245      */
246     public LargeSelect(Criteria criteria, int pageSize)
247             throws IllegalArgumentException
248     {
249         this(criteria, pageSize, LargeSelect.memoryPageLimit);
250     }
251 
252     /***
253      * Creates a LargeSelect whose results are returned as a <code>List</code>
254      * containing a maximum of <code>pageSize</code> Village <code>Record</code>
255      * objects at a time, maintaining a maximum of <code>memoryPageLimit</code>
256      * pages of results in memory.
257      *
258      * @param criteria object used by BasePeer to build the query.  In order to
259      * allow this class to utilise database server implemented offsets and
260      * limits (when available), the provided criteria must not have any limit or
261      * offset defined.
262      * @param pageSize number of rows to return in one block.
263      * @param memoryPageLimit maximum number of pages worth of rows to be held
264      * in memory at one time.
265      * @throws IllegalArgumentException if <code>criteria</code> uses one or
266      * both of offset and limit, or if <code>pageSize</code> or
267      * <code>memoryLimitPages</code> are less than 1;
268      */
269     public LargeSelect(Criteria criteria, int pageSize, int memoryPageLimit)
270             throws IllegalArgumentException
271     {
272         init(criteria, pageSize, memoryPageLimit);
273     }
274 
275     /***
276      * Creates a LargeSelect whose results are returned as a <code>List</code>
277      * containing a maximum of <code>pageSize</code> objects of the type
278      * defined within the class named <code>returnBuilderClassName</code> at a
279      * time, maintaining a maximum of <code>LargeSelect.memoryPageLimit</code>
280      * pages of results in memory.
281      *
282      * @param criteria object used by BasePeer to build the query.  In order to
283      * allow this class to utilise database server implemented offsets and
284      * limits (when available), the provided criteria must not have any limit or
285      * offset defined.  If the criteria does not include the definition of any
286      * select columns the <code>addSelectColumns(Criteria)</code> method of
287      * the class named as <code>returnBuilderClassName</code> will be used to
288      * add them.
289      * @param pageSize number of rows to return in one block.
290      * @param returnBuilderClassName The name of the class that will be used to
291      * build the result records (may implement <code>addSelectColumns(Criteria)
292      * </code> and must implement <code>populateObjects(List)</code>).
293      * @throws IllegalArgumentException if <code>criteria</code> uses one or
294      * both of offset and limit, if <code>pageSize</code> is less than 1, or if
295      * problems are experienced locating and invoking either one or both of
296      * <code>addSelectColumns(Criteria)</code> and <code> populateObjects(List)
297      * </code> in the class named <code>returnBuilderClassName</code>.
298      */
299     public LargeSelect(
300             Criteria criteria,
301             int pageSize,
302             String returnBuilderClassName)
303             throws IllegalArgumentException
304     {
305         this(
306             criteria,
307             pageSize,
308             LargeSelect.memoryPageLimit,
309             returnBuilderClassName);
310     }
311 
312     /***
313      * Creates a LargeSelect whose results are returned as a <code>List</code>
314      * containing a maximum of <code>pageSize</code> objects of the type
315      * defined within the class named <code>returnBuilderClassName</code> at a
316      * time, maintaining a maximum of <code>memoryPageLimit</code> pages of
317      * results in memory.
318      *
319      * @param criteria object used by BasePeer to build the query.  In order to
320      * allow this class to utilise database server implemented offsets and
321      * limits (when available), the provided criteria must not have any limit or
322      * offset defined.  If the criteria does not include the definition of any
323      * select columns the <code>addSelectColumns(Criteria)</code> method of
324      * the class named as <code>returnBuilderClassName</code> will be used to
325      * add them.
326      * @param pageSize number of rows to return in one block.
327      * @param memoryPageLimit maximum number of pages worth of rows to be held
328      * in memory at one time.
329      * @param returnBuilderClassName The name of the class that will be used to
330      * build the result records (may implement <code>addSelectColumns(Criteria)
331      * </code> and must implement <code>populateObjects(List)</code>).
332      * @throws IllegalArgumentException if <code>criteria</code> uses one or
333      * both of offset and limit, if <code>pageSize</code> or <code>
334      * memoryLimitPages</code> are less than 1, or if problems are experienced
335      * locating and invoking either one or both of <code>
336      * addSelectColumns(Criteria)</code> and <code> populateObjects(List)</code>
337      * in the class named <code>returnBuilderClassName</code>.
338      */
339     public LargeSelect(
340             Criteria criteria,
341             int pageSize,
342             int memoryPageLimit,
343             String returnBuilderClassName)
344             throws IllegalArgumentException
345     {
346         try
347         {
348             this.returnBuilderClass = Class.forName(returnBuilderClassName);
349 
350             // Add the select columns if necessary.
351             if (criteria.getSelectColumns().size() == 0)
352             {
353                 Class[] argTypes = { Criteria.class };
354                 Method selectColumnAdder =
355                     returnBuilderClass.getMethod("addSelectColumns", argTypes);
356                 Object[] theArgs = { criteria };
357                 selectColumnAdder.invoke(returnBuilderClass.newInstance(),
358                         theArgs);
359             }
360         }
361         catch (Exception e)
362         {
363             throw new IllegalArgumentException(
364                     "The class named as returnBuilderClassName does not "
365                     + "provide the necessary facilities - see javadoc.");
366         }
367 
368         init(criteria, pageSize, memoryPageLimit);
369     }
370 
371     /***
372      * Access the populateObjects method.
373      */
374     private Method getPopulateObjectsMethod()
375             throws SecurityException, NoSuchMethodException
376     {
377         if (null == populateObjectsMethod)
378         {
379             Class[] argTypes = { List.class };
380             populateObjectsMethod
381                     = returnBuilderClass.getMethod("populateObjects", argTypes);
382         }
383         return populateObjectsMethod;
384     }
385 
386     /***
387      * Called by the constructors to start the query.
388      *
389      * @param criteria Object used by <code>BasePeer</code> to build the query.
390      * In order to allow this class to utilise database server implemented
391      * offsets and limits (when available), the provided criteria must not have
392      * any limit or offset defined.
393      * @param pageSize number of rows to return in one block.
394      * @param memoryLimitPages maximum number of pages worth of rows to be held
395      * in memory at one time.
396      * @throws IllegalArgumentException if <code>criteria</code> uses one or
397      * both of offset and limit and if <code>pageSize</code> or
398      * <code>memoryLimitPages</code> are less than 1;
399      */
400     private void init(Criteria criteria, int pageSize, int memoryLimitPages)
401             throws IllegalArgumentException
402     {
403         if (criteria.getOffset() != 0 || criteria.getLimit() != -1)
404         {
405             throw new IllegalArgumentException(
406                     "criteria must not use Offset and/or Limit.");
407         }
408 
409         if (pageSize < 1)
410         {
411             throw new IllegalArgumentException(
412                     "pageSize must be greater than zero.");
413         }
414 
415         if (memoryLimitPages < 1)
416         {
417             throw new IllegalArgumentException(
418                     "memoryPageLimit must be greater than zero.");
419         }
420 
421         this.pageSize = pageSize;
422         this.memoryLimit = pageSize * memoryLimitPages;
423         this.criteria = criteria;
424         dbName = criteria.getDbName();
425         blockEnd = blockBegin + memoryLimit - 1;
426         startQuery(pageSize);
427     }
428 
429     /***
430      * Retrieve a specific page, if it exists.
431      *
432      * @param pageNumber the number of the page to be retrieved - must be
433      * greater than zero.  An empty <code>List</code> will be returned if
434      * <code>pageNumber</code> exceeds the total number of pages that exist.
435      * @return a <code>List</code> of query results containing a maximum of
436      * <code>pageSize</code> results.
437      * @throws IllegalArgumentException when <code>pageNo</code> is not
438      * greater than zero.
439      * @throws TorqueException if invoking the <code>populateObjects()<code>
440      * method runs into problems or a sleep is unexpectedly interrupted.
441      */
442     public List getPage(int pageNumber) throws TorqueException
443     {
444         if (pageNumber < 1)
445         {
446             throw new IllegalArgumentException(
447                     "pageNumber must be greater than zero.");
448         }
449         return getResults((pageNumber - 1) * pageSize);
450     }
451 
452     /***
453      * Gets the next page of rows.
454      *
455      * @return a <code>List</code> of query results containing a maximum of
456      * <code>pageSize</code> reslts.
457      * @throws TorqueException if invoking the <code>populateObjects()<code>
458      * method runs into problems or a sleep is unexpectedly interrupted.
459      */
460     public List getNextResults() throws TorqueException
461     {
462         if (!getNextResultsAvailable())
463         {
464             return getCurrentPageResults();
465         }
466         return getResults(position);
467     }
468 
469     /***
470      * Provide access to the results from the current page.
471      *
472      * @return a <code>List</code> of query results containing a maximum of
473      * <code>pageSize</code> reslts.
474      * @throws TorqueException if invoking the <code>populateObjects()<code>
475      * method runs into problems or a sleep is unexpectedly interrupted.
476      */
477     public List getCurrentPageResults() throws TorqueException
478     {
479         return null == lastResults && position > 0
480                 ? getResults(position) : lastResults;
481     }
482 
483     /***
484      * Gets the previous page of rows.
485      *
486      * @return a <code>List</code> of query results containing a maximum of
487      * <code>pageSize</code> reslts.
488      * @throws TorqueException if invoking the <code>populateObjects()<code>
489      * method runs into problems or a sleep is unexpectedly interrupted.
490      */
491     public List getPreviousResults() throws TorqueException
492     {
493         if (!getPreviousResultsAvailable())
494         {
495             return getCurrentPageResults();
496         }
497 
498         int start;
499         if (position - 2 * pageSize < 0)
500         {
501             start = 0;
502         }
503         else
504         {
505             start = position - 2 * pageSize;
506         }
507         return getResults(start);
508     }
509 
510     /***
511      * Gets a page of rows starting at a specified row.
512      *
513      * @param start the starting row.
514      * @return a <code>List</code> of query results containing a maximum of
515      * <code>pageSize</code> reslts.
516      * @throws TorqueException if invoking the <code>populateObjects()<code>
517      * method runs into problems or a sleep is unexpectedly interrupted.
518      */
519     private List getResults(int start) throws TorqueException
520     {
521         return getResults(start, pageSize);
522     }
523 
524     /***
525      * Gets a block of rows starting at a specified row and containing a
526      * specified number of rows.
527      *
528      * @param start the starting row.
529      * @param size the number of rows.
530      * @return a <code>List</code> of query results containing a maximum of
531      * <code>pageSize</code> reslts.
532      * @throws IllegalArgumentException if <code>size &gt; memoryLimit</code> or
533      * <code>start</code> and <code>size</code> result in a situation that is
534      * not catered for.
535      * @throws TorqueException if invoking the <code>populateObjects()<code>
536      * method runs into problems or a sleep is unexpectedly interrupted.
537      */
538     private synchronized List getResults(int start, int size)
539             throws IllegalArgumentException, TorqueException
540     {
541         if (log.isDebugEnabled())
542         {
543             log.debug("getResults(start: " + start
544                     + ", size: " + size + ") invoked.");
545         }
546 
547         if (size > memoryLimit)
548         {
549             throw new IllegalArgumentException("size (" + size
550                     + ") exceeds memory limit (" + memoryLimit + ").");
551         }
552 
553         // Request was for a block of rows which should be in progess.
554         // If the rows have not yet been returned, wait for them to be
555         // retrieved.
556         if (start >= blockBegin && (start + size - 1) <= blockEnd)
557         {
558             if (log.isDebugEnabled())
559             {
560                 log.debug("getResults(): Sleeping until "
561                         + "start+size-1 (" + (start + size - 1)
562                         + ") > currentlyFilledTo (" + currentlyFilledTo
563                         + ") && !queryCompleted (!" + queryCompleted + ")");
564             }
565             while (((start + size - 1) > currentlyFilledTo) && !queryCompleted)
566             {
567                 try
568                 {
569                     Thread.sleep(500);
570                 }
571                 catch (InterruptedException e)
572                 {
573                     throw new TorqueException("Unexpected interruption", e);
574                 }
575             }
576         }
577 
578         // Going in reverse direction, trying to limit db hits so assume user
579         // might want at least 2 sets of data.
580         else if (start < blockBegin && start >= 0)
581         {
582             if (log.isDebugEnabled())
583             {
584                 log.debug("getResults(): Paging backwards as start (" + start
585                         + ") < blockBegin (" + blockBegin + ") && start >= 0");
586             }
587             stopQuery();
588             if (memoryLimit >= 2 * size)
589             {
590                 blockBegin = start - size;
591                 if (blockBegin < 0)
592                 {
593                     blockBegin = 0;
594                 }
595             }
596             else
597             {
598                 blockBegin = start;
599             }
600             blockEnd = blockBegin + memoryLimit - 1;
601             startQuery(size);
602             // Re-invoke getResults() to provide the wait processing.
603             return getResults(start, size);
604         }
605 
606         // Assume we are moving on, do not retrieve any records prior to start.
607         else if ((start + size - 1) > blockEnd)
608         {
609             if (log.isDebugEnabled())
610             {
611                 log.debug("getResults(): Paging past end of loaded data as "
612                         + "start+size-1 (" + (start + size - 1)
613                         + ") > blockEnd (" + blockEnd + ")");
614             }
615             stopQuery();
616             blockBegin = start;
617             blockEnd = blockBegin + memoryLimit - 1;
618             startQuery(size);
619             // Re-invoke getResults() to provide the wait processing.
620             return getResults(start, size);
621         }
622 
623         else
624         {
625             throw new IllegalArgumentException("Parameter configuration not "
626                     + "accounted for.");
627         }
628 
629         int fromIndex = start - blockBegin;
630         int toIndex = fromIndex + Math.min(size, results.size() - fromIndex);
631 
632         if (log.isDebugEnabled())
633         {
634             log.debug("getResults(): Retrieving records from results elements "
635                     + "start-blockBegin (" + fromIndex + ") through "
636                     + "fromIndex + Math.min(size, results.size() - fromIndex) ("
637                     + toIndex + ")");
638         }
639 
640         List returnResults;
641 
642         synchronized (results)
643         {
644             returnResults = new ArrayList(results.subList(fromIndex, toIndex));
645         }
646 
647         if (null != returnBuilderClass)
648         {
649             // Invoke the populateObjects() method
650             Object[] theArgs = { returnResults };
651             try
652             {
653                 returnResults = (List) getPopulateObjectsMethod().invoke(
654                         returnBuilderClass.newInstance(), theArgs);
655             }
656             catch (Exception e)
657             {
658                 throw new TorqueException("Unable to populate results", e);
659             }
660         }
661         position = start + size;
662         lastResults = returnResults;
663         return returnResults;
664     }
665 
666     /***
667      * A background thread that retrieves the rows.
668      */
669     public void run()
670     {
671         boolean dbSupportsNativeLimit;
672         try
673         {
674             dbSupportsNativeLimit 
675                     = (Torque.getDB(dbName).getLimitStyle()
676                         != DB.LIMIT_STYLE_NONE);
677         }
678         catch (TorqueException e)
679         {
680             log.error("run() : Exiting :", e);
681             // we cannot execute further because Torque is not initialized
682             // correctly
683             return;
684         }
685         
686         int size;
687         if (dbSupportsNativeLimit)
688         {
689             // retrieve one page at a time
690             size = pageSize;
691         }
692         else 
693         {
694             // retrieve the whole block at once and add the offset,
695             // and add one record to check if we have reached the end of the 
696             // data
697             size = blockBegin + memoryLimit + 1;
698         }
699         /* The connection to the database. */
700         Connection conn = null;
701         /*** Used to retrieve query results from Village. */
702         QueryDataSet qds = null;
703 
704         try
705         {
706             // Add 1 to memory limit to check if the query ends on a page break.
707             results = new ArrayList(memoryLimit + 1);
708 
709             if (dbSupportsNativeLimit)
710             {
711                 // Use the criteria to limit the rows that are retrieved to the
712                 // block of records that fit in the predefined memoryLimit.
713                 criteria.setOffset(blockBegin);
714                 // Add 1 to memory limit to check if the query ends on a 
715                 // page break.
716                 criteria.setLimit(memoryLimit + 1);
717             }
718             query = BasePeer.createQueryString(criteria);
719 
720             // Get a connection to the db.
721             conn = Torque.getConnection(dbName);
722 
723             // Execute the query.
724             if (log.isDebugEnabled())
725             {
726                 log.debug("run(): query = " + query);
727                 log.debug("run(): memoryLimit = " + memoryLimit);
728                 log.debug("run(): blockBegin = " + blockBegin);
729                 log.debug("run(): blockEnd = " + blockEnd);
730             }
731             qds = new QueryDataSet(conn, query);
732 
733             // Continue getting rows one page at a time until the memory limit
734             // is reached, all results have been retrieved, or the rest
735             // of the results have been determined to be irrelevant.
736             while (!killThread
737                 && !qds.allRecordsRetrieved()
738                 && currentlyFilledTo + pageSize <= blockEnd)
739             {
740                 // This caters for when memoryLimit is not a multiple of
741                 //  pageSize which it never is because we always add 1 above.
742                 // not applicable if the db has no native limit where this
743                 // was already considered
744                 if ((currentlyFilledTo + pageSize) >= blockEnd
745                         && dbSupportsNativeLimit)
746                 {
747                     // Add 1 to check if the query ends on a page break.
748                     size = blockEnd - currentlyFilledTo + 1;
749                 }
750 
751                 if (log.isDebugEnabled())
752                 {
753                     log.debug("run(): Invoking BasePeer.getSelectResults(qds, "
754                             + size + ", false)");
755                 }
756 
757                 List tempResults
758                         = BasePeer.getSelectResults(qds, size, false);
759 
760                 synchronized (results)
761                 {
762                     for (int i = 0, n = tempResults.size(); i < n; i++)
763                     {
764                         if (dbSupportsNativeLimit
765                                 || (i >= blockBegin))
766                         results.add(tempResults.get(i));
767                     }
768                 }
769 
770                 if (dbSupportsNativeLimit)
771                 {
772                     currentlyFilledTo += tempResults.size();
773                 }
774                 else
775                 {
776                     currentlyFilledTo = tempResults.size() - 1 - blockBegin;
777                 }
778                 
779                 boolean perhapsLastPage = true;
780 
781                 // If the extra record was indeed found then we know we are not
782                 // on the last page but we must now get rid of it.
783                 if ((dbSupportsNativeLimit 
784                         && (results.size() == memoryLimit + 1))
785                     || (!dbSupportsNativeLimit 
786                             && currentlyFilledTo >= memoryLimit))
787                 {
788                     synchronized (results)
789                     {
790                         results.remove(currentlyFilledTo--);
791                     }
792                     perhapsLastPage = false;
793                 }
794 
795                 if (results.size() > 0
796                     && blockBegin + currentlyFilledTo >= totalRecords)
797                 {
798                     // Add 1 because index starts at 0
799                     totalRecords = blockBegin + currentlyFilledTo + 1;
800                 }
801 
802                 // if the db has limited the datasets, we must retrieve all 
803                 // datasets. If not, we are always finished because we fetch
804                 // the whole block at once.
805                 if (qds.allRecordsRetrieved()
806                         || !dbSupportsNativeLimit)
807                 {
808                     queryCompleted = true;
809                     // The following ugly condition ensures that the totals are
810                     // not finalized when a user does something like requesting
811                     // a page greater than what exists in the database.
812                     if (perhapsLastPage
813                         && getCurrentPageNumber() <= getTotalPages())
814                     {
815                         totalsFinalized = true;
816                     }
817                 }
818                 qds.clearRecords();
819             }
820 
821             if (log.isDebugEnabled())
822             {
823                 log.debug("run(): While loop terminated because either:");
824                 log.debug("run(): 1. qds.allRecordsRetrieved(): "
825                         + qds.allRecordsRetrieved());
826                 log.debug("run(): 2. killThread: " + killThread);
827                 log.debug("run(): 3. !(currentlyFilledTo + size <= blockEnd): !"
828                         + (currentlyFilledTo + pageSize <= blockEnd));
829                 log.debug("run(): - currentlyFilledTo: " + currentlyFilledTo);
830                 log.debug("run(): - size: " + pageSize);
831                 log.debug("run(): - blockEnd: " + blockEnd);
832                 log.debug("run(): - results.size(): " + results.size());
833             }
834         }
835         catch (TorqueException e)
836         {
837             log.error(e);
838         }
839         catch (SQLException e)
840         {
841             log.error(e);
842         }
843         catch (DataSetException e)
844         {
845             log.error(e);
846         }
847         finally
848         {
849             try
850             {
851                 if (qds != null)
852                 {
853                     qds.close();
854                 }
855                 Torque.closeConnection(conn);
856             }
857             catch (SQLException e)
858             {
859                 log.error(e);
860             }
861             catch (DataSetException e)
862             {
863                 log.error(e);
864             }
865             threadRunning = false;
866         }
867     }
868 
869     /***
870      * Starts a new thread to retrieve the result set.
871      *
872      * @param initialSize the initial size for each block.
873      */
874     private synchronized void startQuery(int initialSize)
875     {
876         if (!threadRunning)
877         {
878             pageSize = initialSize;
879             currentlyFilledTo = -1;
880             queryCompleted = false;
881             thread = new Thread(this);
882             thread.start();
883             threadRunning = true;
884         }
885     }
886 
887     /***
888      * Used to stop filling the memory with the current block of results, if it
889      * has been determined that they are no longer relevant.
890      *
891      * @throws TorqueException if a sleep is interrupted.
892      */
893     private synchronized void stopQuery() throws TorqueException
894     {
895         if (threadRunning)
896         {
897             killThread = true;
898             while (thread.isAlive())
899             {
900                 try
901                 {
902                     Thread.sleep(100);
903                 }
904                 catch (InterruptedException e)
905                 {
906                     throw new TorqueException("Unexpected interruption", e);
907                 }
908             }
909             killThread = false;
910         }
911     }
912 
913     /***
914      * Retrieve the number of the current page.
915      *
916      * @return the current page number.
917      */
918     public int getCurrentPageNumber()
919     {
920         return position / pageSize;
921     }
922 
923     /***
924      * Retrieve the total number of search result records that are known to
925      * exist (this will be the actual value when the query has completeted (see
926      * <code>getTotalsFinalized()</code>).  The convenience method
927      * <code>getRecordProgressText()</code> may be more useful for presenting to
928      * users.
929      *
930      * @return the number of result records known to exist (not accurate until
931      * <code>getTotalsFinalized()</code> returns <code>true</code>).
932      */
933     public int getTotalRecords()
934     {
935         return totalRecords;
936     }
937 
938     /***
939      * Provide an indication of whether or not paging of results will be
940      * required.
941      *
942      * @return <code>true</code> when multiple pages of results exist.
943      */
944     public boolean getPaginated()
945     {
946         // Handle a page memory limit of 1 page.
947         if (!getTotalsFinalized())
948         {
949             return true;
950         }
951         return blockBegin + currentlyFilledTo + 1 > pageSize;
952     }
953 
954     /***
955      * Retrieve the total number of pages of search results that are known to
956      * exist (this will be the actual value when the query has completeted (see
957      * <code>getQyeryCompleted()</code>).  The convenience method
958      * <code>getPageProgressText()</code> may be more useful for presenting to
959      * users.
960      *
961      * @return the number of pages of results known to exist (not accurate until
962      * <code>getTotalsFinalized()</code> returns <code>true</code>).
963      */
964     public int getTotalPages()
965     {
966         if (totalPages > -1)
967         {
968             return totalPages;
969         }
970 
971         int tempPageCount =  getTotalRecords() / pageSize
972                 + (getTotalRecords() % pageSize > 0 ? 1 : 0);
973 
974         if (getTotalsFinalized())
975         {
976             totalPages = tempPageCount;
977         }
978 
979         return tempPageCount;
980     }
981 
982     /***
983      * Retrieve the page size.
984      *
985      * @return the number of records returned on each invocation of
986      * <code>getNextResults()</code>/<code>getPreviousResults()</code>.
987      */
988     public int getPageSize()
989     {
990         return pageSize;
991     }
992 
993     /***
994      * Provide access to indicator that the total values for the number of
995      * records and pages are now accurate as opposed to known upper limits.
996      *
997      * @return <code>true</code> when the totals are known to have been fully
998      * computed.
999      */
1000     public boolean getTotalsFinalized()
1001     {
1002         return totalsFinalized;
1003     }
1004 
1005     /***
1006      * Provide a way of changing the more pages/records indicator.
1007      *
1008      * @param moreIndicator the indicator to use in place of the default
1009      * ("&gt;").
1010      */
1011     public static void setMoreIndicator(String moreIndicator)
1012     {
1013         LargeSelect.moreIndicator = moreIndicator;
1014     }
1015 
1016     /***
1017      * Retrieve the more pages/records indicator.
1018      */
1019     public static String getMoreIndicator()
1020     {
1021         return LargeSelect.moreIndicator;
1022     }
1023 
1024     /***
1025      * Sets the multiplier that will be used to compute the memory limit when a
1026      * constructor with no memory page limit is used - the memory limit will be
1027      * this number multiplied by the page size.
1028      *
1029      * @param memoryPageLimit the maximum number of pages to be in memory
1030      * at one time.
1031      */
1032     public static void setMemoryPageLimit(int memoryPageLimit)
1033     {
1034         LargeSelect.memoryPageLimit = memoryPageLimit;
1035     }
1036 
1037     /***
1038      * Retrieves the multiplier that will be used to compute the memory limit
1039      * when a constructor with no memory page limit is used - the memory limit
1040      * will be this number multiplied by the page size.
1041      */
1042     public static int getMemoryPageLimit()
1043     {
1044         return LargeSelect.memoryPageLimit;
1045     }
1046 
1047     /***
1048      * A convenience method that provides text showing progress through the
1049      * selected rows on a page basis.
1050      *
1051      * @return progress text in the form of "1 of &gt; 5" where "&gt;" can be
1052      * configured using <code>setMoreIndicator()</code>.
1053      */
1054     public String getPageProgressText()
1055     {
1056         StringBuffer result = new StringBuffer();
1057         result.append(getCurrentPageNumber());
1058         result.append(" of ");
1059         if (!totalsFinalized)
1060         {
1061             result.append(moreIndicator);
1062             result.append(" ");
1063         }
1064         result.append(getTotalPages());
1065         return result.toString();
1066     }
1067 
1068     /***
1069      * Provides a count of the number of rows to be displayed on the current
1070      * page - for the last page this may be less than the configured page size.
1071      *
1072      * @return the number of records that are included on the current page of
1073      * results.
1074      * @throws TorqueException if invoking the <code>populateObjects()<code>
1075      * method runs into problems or a sleep is unexpectedly interrupted.
1076      */
1077     public int getCurrentPageSize() throws TorqueException
1078     {
1079         if (null == getCurrentPageResults())
1080         {
1081             return 0;
1082         }
1083         return getCurrentPageResults().size();
1084     }
1085 
1086     /***
1087      * Provide the record number of the first row included on the current page.
1088      *
1089      * @return The record number of the first row of the current page.
1090      */
1091     public int getFirstRecordNoForPage()
1092     {
1093         if (getCurrentPageNumber() < 1)
1094         {
1095             return 0;
1096         }
1097         return (getCurrentPageNumber() - 1) * getPageSize() + 1;
1098     }
1099 
1100     /***
1101      * Provide the record number of the last row included on the current page.
1102      *
1103      * @return the record number of the last row of the current page.
1104      * @throws TorqueException if invoking the <code>populateObjects()<code>
1105      * method runs into problems or a sleep is unexpectedly interrupted.
1106      */
1107     public int getLastRecordNoForPage() throws TorqueException
1108     {
1109         if (0 == getCurrentPageNumber())
1110         {
1111             return 0;
1112         }
1113         return (getCurrentPageNumber() - 1) * getPageSize()
1114                 + getCurrentPageSize();
1115     }
1116 
1117     /***
1118      * A convenience method that provides text showing progress through the
1119      * selected rows on a record basis.
1120      *
1121      * @return progress text in the form of "26 - 50 of &gt; 250" where "&gt;"
1122      * can be configured using <code>setMoreIndicator()</code>.
1123      * @throws TorqueException if invoking the <code>populateObjects()<code>
1124      * method runs into problems or a sleep is unexpectedly interrupted.
1125      */
1126     public String getRecordProgressText() throws TorqueException
1127     {
1128         StringBuffer result = new StringBuffer();
1129         result.append(getFirstRecordNoForPage());
1130         result.append(" - ");
1131         result.append(getLastRecordNoForPage());
1132         result.append(" of ");
1133         if (!totalsFinalized)
1134         {
1135             result.append(moreIndicator);
1136             result.append(" ");
1137         }
1138         result.append(getTotalRecords());
1139         return result.toString();
1140     }
1141 
1142     /***
1143      * Indicates if further result pages are available.
1144      *
1145      * @return <code>true</code> when further results are available.
1146      */
1147     public boolean getNextResultsAvailable()
1148     {
1149         if (!totalsFinalized || getCurrentPageNumber() < getTotalPages())
1150         {
1151             return true;
1152         }
1153         return false;
1154     }
1155 
1156     /***
1157      * Indicates if previous results pages are available.
1158      *
1159      * @return <code>true</code> when previous results are available.
1160      */
1161     public boolean getPreviousResultsAvailable()
1162     {
1163         if (getCurrentPageNumber() <= 1)
1164         {
1165             return false;
1166         }
1167         return true;
1168     }
1169 
1170     /***
1171      * Indicates if any results are available.
1172      *
1173      * @return <code>true</code> of any results are available.
1174      */
1175     public boolean hasResultsAvailable()
1176     {
1177         return getTotalRecords() > 0;
1178     }
1179 
1180     /***
1181      * Clear the query result so that the query is reexecuted when the next page
1182      * is retrieved.  You may want to invoke this method if you are returning to
1183      * a page after performing an operation on an item in the result set.
1184      *
1185      * @throws TorqueException if a sleep is interrupted.
1186      */
1187     public synchronized void invalidateResult() throws TorqueException
1188     {
1189         stopQuery();
1190         blockBegin = 0;
1191         blockEnd = 0;
1192         currentlyFilledTo = -1;
1193         results = null;
1194         // TODO Perhaps store the oldPosition and immediately restart the
1195         // query.
1196         // oldPosition = position;
1197         position = 0;
1198         totalPages = -1;
1199         totalRecords = 0;
1200         queryCompleted = false;
1201         totalsFinalized = false;
1202         lastResults = null;
1203     }
1204 
1205     /***
1206      * Retrieve a search parameter.  This acts as a convenient place to store
1207      * parameters that relate to the LargeSelect to make it easy to get at them
1208      * in order to repopulate search parameters on a form when the next page of
1209      * results is retrieved - they in no way effect the operation of
1210      * LargeSelect.
1211      *
1212      * @param name the search parameter key to retrieve.
1213      * @return the value of the search parameter.
1214      */
1215     public String getSearchParam(String name)
1216     {
1217         return getSearchParam(name, null);
1218     }
1219 
1220     /***
1221      * Retrieve a search parameter.  This acts as a convenient place to store
1222      * parameters that relate to the LargeSelect to make it easy to get at them
1223      * in order to repopulate search parameters on a form when the next page of
1224      * results is retrieved - they in no way effect the operation of
1225      * LargeSelect.
1226      *
1227      * @param name the search parameter key to retrieve.
1228      * @param defaultValue the default value to return if the key is not found.
1229      * @return the value of the search parameter.
1230      */
1231     public String getSearchParam(String name, String defaultValue)
1232     {
1233         if (null == params)
1234         {
1235             return defaultValue;
1236         }
1237         String value = (String) params.get(name);
1238         return null == value ? defaultValue : value;
1239     }
1240 
1241     /***
1242      * Set a search parameter.  If the value is <code>null</code> then the
1243      * key will be removed from the parameters.
1244      *
1245      * @param name the search parameter key to set.
1246      * @param value the value of the search parameter to store.
1247      */
1248     public void setSearchParam(String name, String value)
1249     {
1250         if (null == value)
1251         {
1252             removeSearchParam(name);
1253         }
1254         else
1255         {
1256             if (null != name)
1257             {
1258                 if (null == params)
1259                 {
1260                     params = new Hashtable();
1261                 }
1262                 params.put(name, value);
1263             }
1264         }
1265     }
1266 
1267     /***
1268      * Remove a value from the search parameters.
1269      *
1270      * @param name the search parameter key to remove.
1271      */
1272     public void removeSearchParam(String name)
1273     {
1274         if (null != params)
1275         {
1276             params.remove(name);
1277         }
1278     }
1279 
1280     /***
1281      * Deserialize this LargeSelect instance.
1282      *
1283      * @param inputStream The serialization input stream.
1284      * @throws IOException
1285      * @throws ClassNotFoundException
1286      */
1287     private void readObject(ObjectInputStream inputStream)
1288             throws IOException, ClassNotFoundException
1289     {
1290         inputStream.defaultReadObject();
1291         startQuery(pageSize);
1292     }
1293 
1294     /***
1295      * Provide something useful for debugging purposes.
1296      *
1297      * @return some basic information about this instance of LargeSelect.
1298      */
1299     public String toString()
1300     {
1301         StringBuffer result = new StringBuffer();
1302         result.append("LargeSelect - TotalRecords: ");
1303         result.append(getTotalRecords());
1304         result.append(" TotalsFinalised: ");
1305         result.append(getTotalsFinalized());
1306         result.append("\nParameters:");
1307         if (null == params || params.size() == 0)
1308         {
1309             result.append(" No parameters have been set.");
1310         }
1311         else
1312         {
1313             Set keys = params.keySet();
1314             for (Iterator iter = keys.iterator(); iter.hasNext();)
1315             {
1316                 String key = (String) iter.next();
1317                 String val = (String) params.get(key);
1318                 result.append("\n ").append(key).append(": ").append(val);
1319             }
1320         }
1321         return result.toString();
1322     }
1323 
1324 }