This class can be used to retrieve a large result set from a database query.
The query is started and then rows are returned a page at a time. The
LargeSelect is meant to be placed into the Session or User.Temp, so
that it can be used in response to several related requests. Note that in
order to use LargeSelect you need to be willing to accept the
fact that the result set may become inconsistent with the database if updates
are processed subsequent to the queries being executed. Specifying a memory
page limit of 1 will give you a consistent view of the records but the totals
may not be accurate and the performance will be terrible. In most cases
the potential for inconsistencies data should not cause any serious problems
and performance should be pretty good (but read on for further warnings).
The idea here is that the full query result would consume too much memory
and if displayed to a user the page would be too long to be useful. Rather
than loading the full result set into memory, a window of data (the memory
limit) is loaded and retrieved a page at a time. If a request occurs for
data that falls outside the currently loaded window of data then a new query
is executed to fetch the required data. Performance is optimized by
starting a thread to execute the database query and fetch the results. This
will perform best when paging forwards through the data, but a minor
optimization where the window is moved backwards by two rather than one page
is included for when a user pages past the beginning of the window.
As the query is performed in in steps, it is often the case that the total
number of records and pages of data is unknown. LargeSelect
provides various methods for indicating how many records and pages it is
currently aware of and for presenting this information to users.
LargeSelect utilises the Criteria methods
setOffset() and setLimit() to limit the amount of
data retrieved from the database - these values are either passed through to
the DBMS when supported (efficient with the caveat below) or handled by
the Village API when it is not (not so efficient). At time of writing
Criteria will only pass the offset and limit through to MySQL
and PostgreSQL (with a few changes to DBOracle and
BasePeer Oracle support can be implemented by utilising the
rownum pseudo column).
As LargeSelect must re-execute the query each time the user
pages out of the window of loaded data, you should consider the impact of
non-index sort orderings and other criteria that will require the DBMS to
execute the entire query before filtering down to the offset and limit either
internally or via Village.
The memory limit defaults to 5 times the page size you specify, but
alternative constructors and the class method setMemoryPageLimit()
allow you to override this for a specific instance of
LargeSelect or future instances respectively.
Some of the constructors allow you to specify the name of the class to use
to build the returnd rows. This works by using reflection to find
addSelectColumns(Criteria) and populateObjects(List)
methods to add the necessary select columns to the criteria (only if it
doesn't already contain any) and to convert query results from Village
Record objects to a class defined within the builder class.
This allows you to use any of the Torque generated Peer classes, but also
makes it fairly simple to construct business object classes that can be used
for this purpose (simply copy and customise the addSelectColumns()
, populateObjects(), row2Object() and
populateObject() methods from an existing Peer class).
Typically you will create a LargeSelect using your
Criteria (perhaps created from the results of a search parameter
page), page size, memory page limit and return class name (for which you may
have defined a business object class before hand) and place this in user.Temp
thus:
data.getUser().setTemp("someName", largeSelect);
In your template you will then use something along the lines of:
#set($largeSelect = $data.User.getTemp("someName"))
#set($searchop = $data.Parameters.getString("searchop"))
#if($searchop.equals("prev"))
#set($recs = $largeSelect.PreviousResults)
#else
#if($searchop.equals("goto"))
#set($recs = $largeSelect.getPage($data.Parameters.getInt("page", 1)))
#else
#set($recs = $largeSelect.NextResults)
#end
#end
...to move through the records. LargeSelect implements a
number of convenience methods that make it easy to add all of the necessary
bells and whistles to your template.
author:
John D. McNally
author:
Scott Eade
version:
$Id: LargeSelect.java 534001 2007-05-01 10:46:05Z tv $ | 