number of rows in a resultset

[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.

 

[Dirk Michaelsen]

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

 

[Michael Rauscher]

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

 

[Peter Kirk]

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

 

[Elrod]

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.

 

[steve]

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

 

[Dale King]

Hello, steve !

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.