JDBC: Fast way to find the number of rows in a ResultSet?

Thread view:

Ronald Fischer - 21 May 2007 15:46 GMT

After a SELECT query via JDBC, is there a fast way to find out the
number of
rows in the result? Of course I could do something like:

Statement s = ...;
ResultSet r = s.executeQuery("select .....");
int rows=0;
r.first();
while(!r.isLast())
{
++rows;
r.next();
}
// The query resulted in 'rows' lines

but this seems to be a bit cumbersome. I also checked the
ResultSet.getMetaData() method, but it seems to provide only the number
of
columns, not the number of rows...

Regards,

Ronald

Signature

Ronald Fischer <ronaldf@eml.cc>

Reply to this Message

joeNOSPAM@BEA.com - 21 May 2007 19:42 GMT

> After a SELECT query via JDBC, is there a fast way to find out the
> number of
[quoted text clipped - 23 lines]
> Ronald Fischer <rona...@eml.cc>
> Posted viahttp://www.newsoffice.de/

I wish there was a JDBC FAQ. This would have been the first question.
It's been asked since JDBC and before. Ultimately there is no way to
know. For some DBMSes and for some queries, the DBMS starts sending
the data back to the client before it knows how many rows will
qualify.
You will have to ask the DBMS to send you the number of rows that will
qualify, in a separate query that precedes the real data. Depending on
what applications are doing to the data, you might have to lock all
the
data (not easy or fast in some cases) to ensure that nothing changes
between your 'how-may' query and your send-me-the-data query.
So, the practical solution, if you *must* know, is to buffer the
rows in the client (either in application or an insensitive scrollable
result set or rowset). That will allow the client to know exactly
how many, at the cost of the memory to store it all ahead of time.
That strengthens the best-practices recommendation that you never
read more data to the client than you absolutely need at that moment.
Joe Weinstein at BEA Systems

Reply to this Message

Philipp Taprogge - 23 May 2007 22:27 GMT

Hi!

I think there is a slightly better way... it's still not elegant, but it
should work:

result.last();
int rowcount = result.getRow();
result.beforeFirst(); //to reset the resultset for later use.

HTH,

Phil

Reply to this Message

Thomas Kellerer - 23 May 2007 22:54 GMT

Philipp Taprogge wrote on 23.05.2007 23:27:

> Hi!
>
[quoted text clipped - 4 lines]
> int rowcount = result.getRow();
> result.beforeFirst(); //to reset the resultset for later use.

For most drivers this will mean that they will process the whole result set just
to find the number of rows. result.last() is basically identical to

while (rs.next())
{
rowcount ++;
}

Thomas

Reply to this Message

Arne Vajhøj - 24 May 2007 00:53 GMT

> I think there is a slightly better way... it's still not elegant, but it
> should work:
>
> result.last();
> int rowcount = result.getRow();
> result.beforeFirst(); //to reset the resultset for later use.

To do that the ResultSet would likely need to be scrollable
and that will cause many JDBC drivers to read all data
into memory. So this solution works best with small data.

Arne

Reply to this Message

Philipp Taprogge - 25 May 2007 23:11 GMT

Hi!

> To do that the ResultSet would likely need to be scrollable
> and that will cause many JDBC drivers to read all data
> into memory. So this solution works best with small data.

True, but that's a limitation to JDBC. If you can't do a SELECT COUNT(*) far
some reason, then this is AFAICS your only option. Using last() is just
prettier and could be a little bit better performance-wise if the driver is
optimized for it.

Regards,

Phil

Reply to this Message

joeNOSPAM@BEA.com - 26 May 2007 18:14 GMT

> Hi!
>
[quoted text clipped - 10 lines]
>
> Phil

The lion's share of the work involved in a driver implementing last()
is simply the reading in (and maybe having to ask for) the data, and
caching it in memory if it's a scrollable result set. A loop of
next() calls won't be any different in performance. And again, if
someone else adds or deletes rows in between your receiving the
results of you "select count(*)" and when you run your actual query
for the data, then you will get a different number of rows than your
count anyway.

Joe Weinstein at BEA Systems

Reply to this Message

Robert Klemme - 24 May 2007 10:11 GMT

> After a SELECT query via JDBC, is there a fast way to find out the
> number of
[quoted text clipped - 14 lines]
> ResultSet.getMetaData() method, but it seems to provide only the number of
> columns, not the number of rows...

If you need to know the value before processing you can do

select count(*)
from ( <your query here> ) qr

Depending on the volume of data and RDBMS this can impose quite some
overhead on the server. But then again, a DB server usually has more
resources and can do this more efficiently than any client - partly
because you do not have to transfer the whole result set over the network.

Regards

robert

Reply to this Message

JDBC: Fast way to find the number of rows in a ResultSet?

Free Magazines