Hi,
Your revised solution is a good idea however it will not work with an ORDER
BY clause in the SQL statement. We use Oracle here and originally that was
what I wanted to do but I found through much experimentation and consulting
the Oracle docs that Oracle will first return the rows up to and/or
including what was specified for the ROWNUM setting and THEN it does the
ORDER BY!
So what ends up happening is you find yourself missing some rows that you
feel should be there such as when you run the statement without the ROWNUM
clause using the ORDER BY and vice-versa.
You can confirm this behaviour by applying the ROWNUM and ORDER BY clauses
independent of each other or in different combinations in the original SQL
statement to see what I'm trying to explain here.
HTH,
Greg.
Don Brown <[EMAIL PROTECTED]> on 11/21/2002 04:18:46 PM
Please respond to "JDJList" <[EMAIL PROTECTED]>
To: "JDJList" <[EMAIL PROTECTED]>
cc:
Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
This code looks similar to what I used. However, I'll make one point about
making it a bit quicker (at least in the earlier pages).
The code basically returns the entire resultset, and loops through it,
adding
rows for the page in question to a vector while ignoring the rows before
and
after those of interest. Okay. ORACLE provides a pseudo column called
ROWNUM which is similar to, yet different from the LIMIT of other DBs like
PostgresQL. SQL to the effect of
"SELECT <whatever> FROM <whatever> WHERE <whatever> AND ROWNUM BETWEEN 100
and
120"
****will not work****. It will always return 0 rows. However it will work
if
you put only the upper limit on it. EG:
"SELECT <whatever> FROM <whatever> WHERE <whatever> AND ROWNUM <= 120"
will work fine.
In other words, you need to loop and skip all rows before the disired rows,
but you don't need to return nor loop through skipping the ones following.
Similarily, you could change the while loop from:
-- <snip> ---
> while (rs.next()) {
> nNumRows++;
>
> if ( (nNumRows >= p_nStartRow) && (nNumRows <
> p_nStartRow + p_nMaxRows) ) {
> // Populate our transaction list objects
> TransactionList transactionlist = new
> TransactionList(rs.getString(3), rs.getString(5),
> rs.getInt(6), rs.getString(7),
> rs.getString(8),
> rs.getBigDecimal(9, 2), rs.getBigDecimal(10, 2),
> rs.getBigDecimal(11, 2), rs.getBigDecimal(12, 4),
> rs.getBigDecimal(13, 4), rs.getBigDecimal(14, 4),
> rs.getString(15), rs.getString(17),
> rs.getString(22),
> rs.getLong(1), rs.getString(19),
> rs.getString(23), rs.getString(24),
> rs.getString(25));
> vData.addElement(transactionlist);
> }
> }
--- <end snip> ----
To:
_-- <snip> ---
> while (rs.next() && (nNumRows < p_nStartRow +
p_nMaxRows)) {
--- cut (rest of the loop is the same) -----
to do the same thing without the pseudo column.
Someone else had reply about a scrollable resultset which might be more
effiecient than this solution, but I have no experience with that.
Hope this helps,
Don Brown
On November 21, 2002 11:56 am, [EMAIL PROTECTED] wrote:
> Ok guys I'm giving you all a gift.... I had the same problem
understanding
> how PetStore does it and after reading much documentation on the design
> pattern and various other people's implementation of said pattern here is
> my interpretation of the Page-by-Page Iterator pattern...
>
> This is actually still being used in production for a project developed
> from three years ago so I know it works... Don't give me a hard time
about
> using a Vector though... If you want to use an ArrayList instead by all
> means feel free to change it!
>
> Debug is a class I wrote for outputting values while developing/unit
> testing so implement your own such as Log4J or whatever. I'ved also
removed
> the bulk of the SQL statement as it is somewhat proprietary but I think
you
> will get the drift...
> --- <Code is in original reply from [EMAIL PROTECTED]> ---
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
******************************************************************************
This email message is confidential to the intended recipient and may be
legally privileged. It is intended solely for the addressee. Access to this
email by anyone else is unauthorized. Any unauthorised use, dissemination
of the information, or copying of this message is prohibited. If you are
not the intended addressee, kindly notify the sender by return e-mail
and delete this message.
******************************************************************************
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
