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

Discussion in 'Java' started by junk1@davidbevan.co.uk, Jan 20, 2005.

  1. Guest

    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
     
    , Jan 20, 2005
    #1
    1. Advertising

  2. wrote:
    > 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
     
    Collin VanDyck, Jan 20, 2005
    #2
    1. Advertising

  3. Guest

    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
     
    , Jan 21, 2005
    #3
  4. wrote:

    >
    > ...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
     
    Tom Arne Orthe, Jan 21, 2005
    #4
  5. wrote:
    > ...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
     
    Daniel Dittmar, Jan 21, 2005
    #5
  6. anonymous Guest

    wrote:
    > 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.
     
    anonymous, Jan 21, 2005
    #6
  7. frebe Guest

    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
     
    frebe, Jan 21, 2005
    #7
  8. Chris Smith Guest

    Tom Arne Orthe <> wrote:
    > 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
     
    Chris Smith, Jan 23, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ike
    Replies:
    2
    Views:
    448
    Andrew Thompson
    Nov 18, 2003
  2. circuit_breaker
    Replies:
    2
    Views:
    2,045
    Jack Jia
    Apr 4, 2004
  3. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    769
    Keith Wansbrough
    Aug 16, 2004
  4. Betty
    Replies:
    1
    Views:
    9,889
    Juha Laiho
    May 21, 2005
  5. Jesus M. Salvo Jr.
    Replies:
    2
    Views:
    4,282
    robert
    Feb 11, 2006
Loading...

Share This Page