There is a reason why I don't want the while loop to stop executing.... I
want to find out how many total rows are returned by the SQL statement
itself so that I can pass that back to the calling page for display
purposes, ie: "Results: 1 - 20 of 112". This saves me from running a prior
query to COUNT(*) all of the rows returned for the same conditions in the
FROM and WHERE clauses. That is also why there is an if statement inside of
the while loop instead of placing the condition on the while loop itself
like you have previously suggested...
Greg.
Don Brown <[EMAIL PROTECTED]> on 2002/11/21 09:46:08 PM
Please respond to "JDJList" <[EMAIL PROTECTED]>
To: "JDJList" <[EMAIL PROTECTED]>
cc:
Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
On November 21, 2002 06:04 pm, you wrote:
> 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.
>
Good point. I forgot about that.
But you can do the same thing by stopping the loop via the extra condition
in
the while loop.
>
> 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
> ____________________________________________________
____________________________________________________
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
____________________________________________________
