number of rows in a resultset

Discussion in 'Java' started by Liz, Jun 9, 2004.

  1. Liz

    Liz Guest

    I'm just starting to use JDBC and want
    to know the number of rows in a result set
    but have not been able to figure it out.
     
    Liz, Jun 9, 2004
    #1
    1. Advertising

  2. Hi Liz,

    >I'm just starting to use JDBC and want
    >to know the number of rows in a result set
    >but have not been able to figure it out.


    there is no other way than to run through the result set counting the
    rows and then jump back to the start with Resultset.beforeFirst() for
    further processing.

    cu
    Dirk
     
    Dirk Michaelsen, Jun 9, 2004
    #2
    1. Advertising

  3. Liz schrieb:
    > I'm just starting to use JDBC and want
    > to know the number of rows in a result set
    > but have not been able to figure it out.
    >
    >


    JDBC allows the driver to read the results in chunks. That means, that
    the driver needs not to read the complete result set at once. This is
    important due to perfomance and economical memory management. On the
    other hand that means, that there's no way to know the number of all
    records in advance.

    How comfortable you can determine the record count depends on the type
    of your ResultSet.

    If your ResultSet is scrollable (not of type TYPE_FORWARD_ONLY), then
    you may use absolute positioning, that is for a given ResultSet rs

    rs.last();
    int i = rs.getRow();

    If your ResultSet isn't scrollable, then you have to walk through it:

    int i = 0;
    while ( rs.next() )
    i++;

    But if your ResultSet isn't scrollable, you can't return back, so you
    would have to execute the query twice if you want to know the number of
    records before processing the results.

    Bye
    Michael
     
    Michael Rauscher, Jun 9, 2004
    #3
  4. Liz

    Peter Kirk Guest

    "Michael Rauscher" <> skrev i en meddelelse
    news:ca6im8$bn0$01$-online.com...
    > Liz schrieb:
    > > I'm just starting to use JDBC and want
    > > to know the number of rows in a result set
    > > but have not been able to figure it out.
    > >

    > JDBC allows the driver to read the results in chunks. That means, that
    > the driver needs not to read the complete result set at once. This is
    > important due to perfomance and economical memory management. On the
    > other hand that means, that there's no way to know the number of all
    > records in advance.
    >
    > If your ResultSet isn't scrollable, then you have to walk through it:
    >
    > int i = 0;
    > while ( rs.next() )
    > i++;
    >
    > But if your ResultSet isn't scrollable, you can't return back, so you
    > would have to execute the query twice if you want to know the number of
    > records before processing the results.


    A possible problem of course being that the data could change between
    queries, and the number of rows returned be different.

    Also, maybe the first query could be an SQL "count", and the second query
    the actual data retrieval. This still has the possible problem of changing
    data, and as I am no SQL expert I have no idea if it would provide any
    advantage.

    Or, you could iterate throug the result set, and create "business objects"
    for each row - after this you have your count, and the data to work with.
    Depends on your application of course.

    Peter
     
    Peter Kirk, Jun 9, 2004
    #4
  5. Liz

    Elrod Guest

    I don't know how many languages support this, but I am using MySQL
    4.0, and to get the row count, I execute the statement "SELECT
    FOUND_ROWS();" immediately after executing the "SELECT" statement in
    question, and grab the value from the first column. This method is not
    without a performance hit, since it does require that the entire
    result set be determined, but in my opinion, it's much nicer than
    trying to iterate through the whole result set.

    "Liz" <> wrote in message news:<Mkwxc.65$2i5.47@attbi_s52>...
    > I'm just starting to use JDBC and want
    > to know the number of rows in a result set
    > but have not been able to figure it out.
     
    Elrod, Jun 9, 2004
    #5
  6. Liz

    steve Guest

    On Wed, 9 Jun 2004 12:27:24 +0800, Liz wrote
    (in article <Mkwxc.65$2i5.47@attbi_s52>):

    > I'm just starting to use JDBC and want
    > to know the number of rows in a result set
    > but have not been able to figure it out.
    >
    >


    I suppose you could add a count column to the end of each record, so that
    record 1 had a column that showed 1, by setting the resultset to the last
    record you could get the record count from this column.
    then set the resultset back to the first record.

    it is not clean, but depending on your database, neither are the other
    methods.
    to be honest it is complete shit , that a database cannot return the number
    of records, both in its table & in a selection without resorting to such
    methods.

    steve
     
    steve, Jun 12, 2004
    #6
  7. Liz

    Dale King Guest

    Hello, steve !
    You wrote:

    > On Wed, 9 Jun 2004 12:27:24 +0800, Liz wrote
    > (in article <Mkwxc.65$2i5.47@attbi_s52>):
    >
    > > I'm just starting to use JDBC and want
    > > to know the number of rows in a result set
    > > but have not been able to figure it out.
    > >
    > >

    >
    > I suppose you could add a count column to the end of each

    record, so that
    > record 1 had a column that showed 1, by setting the resultset

    to the last
    > record you could get the record count from this column.
    > then set the resultset back to the first record.
    >
    > it is not clean, but depending on your database, neither are

    the other
    > methods.
    > to be honest it is complete shit , that a database cannot

    return the number
    > of records, both in its table & in a selection without

    resorting to such
    > methods.


    And if you know anything about Databases, you would know that it
    would be pretty darn inefficent to do that on any but the most
    basic query. Calculating the number of results of a query would
    basically entail running the query twice.

    --
    Dale King
    My Blog: http://daleking.homedns.org/Blog
     
    Dale King, Apr 15, 2006
    #7
    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. Subba Rao via DotNetMonster.com

    script for moving rows up and down and traverse thru rows of HTML table

    Subba Rao via DotNetMonster.com, Mar 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    8,242
    Subba Rao via DotNetMonster.com
    Mar 19, 2005
  2. helpful sql
    Replies:
    0
    Views:
    819
    helpful sql
    May 19, 2005
  3. Arjen Hoekstra
    Replies:
    0
    Views:
    565
    Arjen Hoekstra
    Aug 2, 2005
  4. Praveen homkar via JavaKB.com

    how to get total number of rows contained in a resultset

    Praveen homkar via JavaKB.com, Aug 5, 2005, in forum: Java
    Replies:
    1
    Views:
    5,340
  5. Jason James

    Rows the rows of a grid view?

    Jason James, Aug 10, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    398
    Jason James
    Aug 10, 2006
Loading...

Share This Page