How do I scroll through multiple pages of JDBC data from a servlet?

[junk1]

I have the following problem with servlets that use JDBC.

Basically if I have a servlet that runs a query and returns a
potentially huge result set to the user then I want to display a 'next'
'previous' kind of dialog and only display the first 10 rows for
example.

This is quite easy to achive, but relies on holding the result set
object open between calls (ie whilst waiting for user to press
'next'/'previous') This is bad since it means hogging a connection
object for that user.

I understand that this must be a common problem and im probably not the
first to try and address it, so rather than reinvent the wheel I would
appreciate it if anyone would be willing to share how they do it, or
point me in the direction of a known pattern for this kind of access.
Thanks

David Bevan

http://www.davidbevan.co.uk

 

[Collin VanDyck]

I have the following problem with servlets that use JDBC.

Basically if I have a servlet that runs a query and returns a
potentially huge result set to the user then I want to display a 'next'
'previous' kind of dialog and only display the first 10 rows for
example.

This is quite easy to achive, but relies on holding the result set
object open between calls (ie whilst waiting for user to press
'next'/'previous') This is bad since it means hogging a connection
object for that user.

I understand that this must be a common problem and im probably not the
first to try and address it, so rather than reinvent the wheel I would
appreciate it if anyone would be willing to share how they do it, or
point me in the direction of a known pattern for this kind of access.
Thanks

David Bevan

http://www.davidbevan.co.uk

If you are certain of the database(s) you will be using you can limit
the result set at the database level. In mySQL, I believe this is done
with the LIMIT() function. That way you don't have to deal with any of
this, and it will be less memory intensive.

If you are not certain of the database(s) you will be using, store the
result set in the session perhaps.

Collin

 

[junk1]

Thanks, but storing the result set in the session would be a BIG no no!
as to fetch the whole result set (ie serveral thousand rows) would be
very inefficient if only a couple of hundered are ever going to be
scrolled through. ...or if you meant the actual result set object then
that wouldnt help since it dies as soon as the connection is closed!

....and limiting the result set wouldnt help either since at query time
there is no way of knowing which rows the user will want to scroll
through!

Thanks anyway!

David Bevan

http://www.davidbevan.co.uk

 

[Tom Arne Orthe]

...and limiting the result set wouldnt help either since at query time
there is no way of knowing which rows the user will want to scroll
through!

Not if you have one query per page you're showing the user.

Ie, the first page he sees shows the first ten rows. Then you run
a query like this (from the MySQL documentation):

SELECT * FROM table LIMIT 0,10; # Retrieve rows 0-10

This returns a resultset containing only the first ten rows.
When the user clicks the "next" link you run this query
(as you know he wants to se the next 10 rows (rows 10 to 20)):

SELECT * FROM table LIMIT 10,10; # Retrieve rows 10-20

This returns a resultset containing only the ten rows from
row 10, ie. rows 10 to 20.

To use this your database needs to support LIMIT.
I know MySQL does: http://dev.mysql.com/doc/mysql/en/SELECT.html



Regards,
Tom

 

[Daniel Dittmar]

...and limiting the result set wouldnt help either since at query time
there is no way of knowing which rows the user will want to scroll
through!

He meant to execute a new query when the user requests the next or
previous page and to LIMIT that query.

An alternative would be to create a new table and to INSERT ... SELECT
the original query into it. If the new table has a autoincrement key,
you could then execute a simple SELECT for each range of rows. This has
of course the disadvantage
- that the first page takes somewhat longer because it has to copy the
whole query
- that you never know when to DROP that table

Daniel

 

[anonymous]

Thanks, but storing the result set in the session would be a BIG no no!
as to fetch the whole result set (ie serveral thousand rows) would be
very inefficient if only a couple of hundered are ever going to be
scrolled through. ...or if you meant the actual result set object then
that wouldnt help since it dies as soon as the connection is closed!

...and limiting the result set wouldnt help either since at query time
there is no way of knowing which rows the user will want to scroll
through!

Thanks anyway!

David Bevan

http://www.davidbevan.co.uk

What we do is pass the current visible start row number to a stored
procedure, which has to run the query again and only return the 'next'
or 'previous' rows. We return them as xml, so we use CLOB to circumvent
the 32Kb limit of varchar2.
If the view is optimized or materialized and the table is not in the
gigabyte range, it actually performs quite well.

 

[frebe]

Look at the class ScrollQuery in http://butler.sourceforge.net. It will
do the job for you.

It adds an extra condition for the start record, in the where-clause.
Every time you make a new search, the last record from the previous
search will be used as start record.

Paging is an example of complex but deterministic logic, that should be
solved in a generic way. Implementing paging in every client is very
time-consuming.

Fredrik Bertilsson

 

[Chris Smith]

Ie, the first page he sees shows the first ten rows. Then you run
a query like this (from the MySQL documentation):

SELECT * FROM table LIMIT 0,10; # Retrieve rows 0-10

This returns a resultset containing only the first ten rows.
When the user clicks the "next" link you run this query
(as you know he wants to se the next 10 rows (rows 10 to 20)):

SELECT * FROM table LIMIT 10,10; # Retrieve rows 10-20

Note that if you do this, you MUST use an ORDER BY clause. Otherwise,
you are relying on implementation characteristics of the DBMS. Without
the ORDER BY clause, the database would be quite justified in returning
the exact same data to you every time, since the order of results in
undefined in the relational model.

To use this your database needs to support LIMIT.
I know MySQL does: http://dev.mysql.com/doc/mysql/en/SELECT.html

Although there is no standard for it, I'm aware of no databases that
don't support some syntax for limiting query results. Support from
MySQL, which generally has one of the poorest query languages of any
DBMS available and even fails often to implement entry-level standard
SQL92, is a good indication that it's practically universal. The only
remaining question mark might be Access.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation