File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes returning # of rows from sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "returning # of rows from sql query" Watch "returning # of rows from sql query" New topic

returning # of rows from sql query

Bill Brasskey
Ranch Hand

Joined: Jun 01, 2004
Posts: 47
does there exist a java function I can call that will return the number of rows returned from a sql select query, so i don't have to "select count(*)"?
...i guess i'm looking for something like the " mysql_num_rows" function in PHP.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33107

No. There are two standard ways of getting this information:

1) If you don't need the data, use select count(*)
2) If you do need the data, count up the rows as you process them

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
Depending on your needs I have a class as part of the FormattedDataSet api that converts a ResultSet into an array of Objects (Object[][]). It is straightforward to use.

The javadocs link:

See the links below to read more. - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
sarah Marsh
Ranch Hand

Joined: Mar 06, 2001
Posts: 282
where can I find FormattedDataSet api?

I heared for oracle 9i there's a new way for get the rowcount, what's that?
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
The basic problem is that the database itself doesn't know how many rows will be returned without executing the query, or at least a good part of the query.

You can get some an *estimate* of how may rows will be returned, since the optimizer computes that as part of its work to pick the best query plan. It's just a guess, though. For comparison, sometimes google searches will say "1-10 out of about 25,000 for..." but when you get to page 5 it says "50-60 out of 1267 for ...." What happened to the other 24,000 results? They never existed -- google's initial estimate was off.

For Oracle, you can get the estimate from EXPLAIN PLAN or from some of the v$ system views. See this discussion:
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
<<where can I find FormattedDataSet api?>>
I agree. Here's the link:
subject: returning # of rows from sql query
It's not a secret anymore!