You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "[ Muliawan Sjarif ]" <m4...@gmail.com> on 2004/10/25 13:39:48 UTC
Paged query result
Hi,
Has anyone realized that doing paged query (limit query like in MySQL)
directly from SQL statement is faster than using setStartAtIndex &
setEndAtIndex? I did a little experiment on this -- on Oracle to be
specific. Now I encounter a weird problem when I apply the SQL
statement on PersistenceBroker.
What I did was only override getPreparedSelectStatement method of
org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl and
added the following:
-- snipped existing code --
if (query.getEndAtIndex() > 0 && query.getStartAtIndex() >= 0) {
result = "select b.* from ( select a.*, rownum as myrownum from (" +
result +
") a where rownum <= " +
query.getEndAtIndex() +
" ) b where b.myrownum > " +
query.getStartAtIndex();
}
-- snipped existing code --
The problem I'm having is that on the first page to display the first
10 records I get the correct result. But, on the subsequent page I get
only 1, 0, 0 and 0 record till the last page as in fact there are 156
records. Am I missing something here?
Thanks in advance for any input.
FYI, I got the above SQL statement from the Torque team mailing list.
That's the latest and enhanced SQL statement for paged query for
Oracle database.
Regards,
Muliawan
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Paged query result
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi muliawan,
yes i know its faster (an i already had a primitive working version). the
Platform interface defines some methods to support sql-paging, but it's not yet
implemented.
i think the strange behaviour is caused by some kind of 'double paging' because
the PagingIterator is still active.
jakob
[ Muliawan Sjarif ] schrieb:
> Hi,
>
> Has anyone realized that doing paged query (limit query like in MySQL)
> directly from SQL statement is faster than using setStartAtIndex &
> setEndAtIndex? I did a little experiment on this -- on Oracle to be
> specific. Now I encounter a weird problem when I apply the SQL
> statement on PersistenceBroker.
>
> What I did was only override getPreparedSelectStatement method of
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl and
> added the following:
>
> -- snipped existing code --
>
> if (query.getEndAtIndex() > 0 && query.getStartAtIndex() >= 0) {
> result = "select b.* from ( select a.*, rownum as myrownum from (" +
> result +
> ") a where rownum <= " +
> query.getEndAtIndex() +
> " ) b where b.myrownum > " +
> query.getStartAtIndex();
> }
>
> -- snipped existing code --
>
> The problem I'm having is that on the first page to display the first
> 10 records I get the correct result. But, on the subsequent page I get
> only 1, 0, 0 and 0 record till the last page as in fact there are 156
> records. Am I missing something here?
> Thanks in advance for any input.
>
> FYI, I got the above SQL statement from the Torque team mailing list.
> That's the latest and enhanced SQL statement for paged query for
> Oracle database.
>
> Regards,
> Muliawan
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org