aspose file tools*
The moose likes JDBC and the fly likes how to get num of rows in ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get num of rows in ResultSet" Watch "how to get num of rows in ResultSet" New topic
Author

how to get num of rows in ResultSet

kwame Iwegbue
Ranch Hand

Joined: Sep 02, 2000
Posts: 197
I know that getColumnCount() of ResultSetMetaData returns number of columns, but is there a way to find out the number of rows? I've searched API and come up with nothing!!


Who dares, wins! (SAS motto)
Masoud Kalali
Author
Ranch Hand

Joined: Jul 08, 2004
Posts: 531

I do not knwo the reason , but there is no method in resultset which can help you find total rows in a result set.
you should just use another query statement to count number of rows.

also you can use multiple statement at once which will return a result set that for example its first column contain the rows count and other columns are your real data.

also using an scrollable result set (in case you need total count of records before traversing the result set)
you can navigate to last record and then you can use



but it is not wise at all , inc canse you need a high performance method
[ March 20, 2006: Message edited by: Masoud Kalali ]

Masoud Kalali
Software Engineer - My Weblog - GlassFish Security
Karthik Krishnamurthy
Ranch Hand

Joined: Feb 04, 2005
Posts: 118
There is a resultset.size() method but not all drivers support it. I think JDBC 4.0 drivers support this call.

So better to either do a Count (*) or while(rs.next() row++ and get value of row outside the loop.
kwame Iwegbue
Ranch Hand

Joined: Sep 02, 2000
Posts: 197
thanks guys. but thats so odd that there's no clear method for doing this like getColumnCount() ?!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

J2SE 1.4's ResultSet does not have a size() method. And I would be surprised if Java 5 did, because the concept of the "size" of a ResultSet doesn't make sense. The naive view of a ResultSet containing a bunch of rows is not correct. To see why, notice that there's a TYPE_SCROLL_SENSITIVE constant that says that your ResultSet will be sensitive to changes made by other users. Those changes could include adds and deletes. So if you were doing a query that selected all rows from a table with 100 rows, you might think the ResultSet has 100 rows. But if, before you read those rows, somebody adds another 50 rows, then when you read through the ResultSet, you will read 150 rows.

So: the rows aren't "in" the ResultSet until you physically read them.
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Originally posted by kwame Iwegbue:
thanks guys. but thats so odd that there's no clear method for doing this like getColumnCount() ?!


It is not odd at all since without executing the query, even the database has no clue how many rows a query would bring - think about it. By the way, may I ask why you need to know the total number of rows?
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
As there is no method... that I know of in java to return the record count and looping through all recs to get a count is not very efficient.

You may create a method that executes a

and returns the result as a java long in java that way you can get counts for any table they will run in the db making them faster and improving the performance of your application.

Please note that in some databases it is advisable to put the primary key in the count function.

Hope this helps,
george
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Originally posted by George Stoianov:
As there is no method... that I know of in java to return the record count and looping through all recs to get a count is not very efficient.

You may create a method that executes a

and returns the result as a java long in java that way you can get counts for any table they will run in the db making them faster and improving the performance of your application.

Please note that in some databases it is advisable to put the primary key in the count function.

Hope this helps,
george


Why would the above improve performance?

In many cases, getting counts of records can be avoided altogether - That is why I asked why the questioner wanted to do it in the first place. Also, the above solution will give you incorrect resuls (at least in case of Oracle.)

The simplest way, IMHO, (if this is a real requirement) is to simply maintain a counter while looping through the result set - assuming you are looping through it anyways (which typically you do.)
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by R. M. Menon:
In many cases, getting counts of records can be avoided altogether...


Yes, agreed. However, all the JDBC forums I've ever looked at have people clamoring for the best way to do just that.

A very common problem is where they have a web system and want to display a "page" of results; maybe rows 31 through 40, and they have a business requirement to display "Page n of m" along with the data.

R. M. Menon, how would you do it, generally?
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Originally posted by stu derby:


Yes, agreed. However, all the JDBC forums I've ever looked at have people clamoring for the best way to do just that.

A very common problem is where they have a web system and want to display a "page" of results; maybe rows 31 through 40, and they have a business requirement to display "Page n of m" along with the data.

R. M. Menon, how would you do it, generally?


"R. M. Menon" sounds very formal - Menon is good enough I go by my last name everywhere. I just changed the display name in my profile - so hopefully for future posts, it would just be Menon.

It depends how costly it is to do a count(*) from the table. Consider the case where it is very costly to count the rows (since number of rows returned is higher). In this case, I would convince the business users that you can do with just a "Page n" information. In Oracle (which is what I am familiar with most) it is very easy to get the nth page rows efficiently by rexecuting a query. The users typically just need to sort the data and see chunks of it in pages - they dont go beyond even 5 to 10 pages. Even google shows you an approximation when it says pages n of m found, btw.

See a very good discussion on this at asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:2468032473603624973::NO::F4950_P8_DISPLAYID,F4950_P8_B:127412348064,Y

If that is not an alternative, you can get an approximation of the count of rows in Oracle (if you use CBO - cost based optimizer, you can look at the approximate count Oracle thinks for this query in v$sql_plan).


Lastly, - with the caveat that this would still give you approximate count - AND would be slow - at least the first time (i.e., I dont like this solution at all) - you can do a count(*) in the very first "page" and then cache that info. For each page you would always reexecute the query.
PS: I have a whole section on this topic in my book
PS 2: Before you think of scrollable result sets - I would say, a big NO. Not in Oracle, at least. Easist to reexecute the query and get the correct set of rows using the correct order by clause each time the user presses "Next" in Oracle. I cover scrollable result sets and why they are not much practical use when coding in Oracle, in general, in my book.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30546
    
150

Originally posted by Menon:
"R. M. Menon" sounds very formal - Menon is good enough I go by my last name everywhere. I just changed the display name in my profile - so hopefully for future posts, it would just be Menon.

Unfortunately, "Menon" doesn't follow our naming policy. A first name (or initials) and last name is required. Can you change it back?

It is ok to sign your posts with Menon as you have been doing, it's just the display name that needs it.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Menon, thanks for the answer... I've been around this track a dozen times and keep hoping for a better answer... It's just a classic "impedence mismatch", I think, where the tool really just isn't a good solution for that problem...

Getting an approximate count from the plan view was a good tip; I've tried mining NUMROWS from ALL_TABLES, but that's not very general, can be too stale, and certainly doesn't work for a lot of joins or filters...

Thanks again...
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
Hi Menon,

Can you please eloborate on why count(*) would not return the correct results in Oracle or point to a place to find some explanation.

Thinking about the record count I think it is kind of interesting how one has to approach this, are there examples of this in your book?

In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.

I will give an example so you can comment on that. Let us say I make a method that accepts a connection and a table name in the method I issue a count(*) and retrieve the number of rows which the method returns to my calling method. This in my mind is good OO and reusable functionality that fits well in any program. How would you rework that taking into account the imperfect results from count(*)?

I do a lot of web development and usually a record count is printed when displaying a table yes I can keep a counter and append the count to the string holding the table in a place where it appears on top of the output or bottom whatever, but that means I have to duplicate this code for every loop for every table, that is not good OO and leads to problems. If I later find a better way I then need to go and change my code in all of those places and possibly introduce new problems.

Thanks in advance,
George
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Originally posted by Jeanne Boyarsky:

Unfortunately, "Menon" doesn't follow our naming policy. A first name (or initials) and last name is required. Can you change it back?

It is ok to sign your posts with Menon as you have been doing, it's just the display name that needs it.


Done. Now I know why I did not have it as "Menon" in the first place. It is a strange requirement though, if you ask me..
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Originally posted by George Stoianov:
Hi Menon,

Can you please eloborate on why count(*) would not return the correct results in Oracle or point to a place to find some explanation.

Thinking about the record count I think it is kind of interesting how one has to approach this, are there examples of this in your book?

In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.

I will give an example so you can comment on that. Let us say I make a method that accepts a connection and a table name in the method I issue a count(*) and retrieve the number of rows which the method returns to my calling method. This in my mind is good OO and reusable functionality that fits well in any program. How would you rework that taking into account the imperfect results from count(*)?

I do a lot of web development and usually a record count is printed when displaying a table yes I can keep a counter and append the count to the string holding the table in a place where it appears on top of the output or bottom whatever, but that means I have to duplicate this code for every loop for every table, that is not good OO and leads to problems. If I later find a better way I then need to go and change my code in all of those places and possibly introduce new problems.

Thanks in advance,
George



My reply is relevant only for Oracle.

In Oracle, when you do a select - the results are fixed as of the point of time (roughly speaking) your select started(in the default transaction isolation level). Even if you insert and commit records during the select run, it would still show the same result as was true at the point it ran (in the default transaction isolation level). I cover these concepts in detail in my book in a separate chapter on Transactions.

So if you executed one query Q1 and then immediately executed another query, to get the count, it is possible that the count is wrong since other transactions may have happenned in between. But that is not even important. The fact is that count(*) entails going through all records in the query again and that work if done in the database is unnecessary = especially if you can do it easily outside using a counter.

And reuusabuluty of code is not OO specific. In procedural code also you can and should write reusable code - though OO is a more powerful paradigm in this respect.

If it is a genuine requirement, you could write a generic procedure to count from a given table but that does not solve the original problem which was for any given query results (remember you can have joins.)

I mentioned my suggested approaches in this case in the eariler response.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Originally posted by George Stoianov:

In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.


That is the ideal, but as anyone who has ever used the JDBC against multiple vendors' databases can tell you, "vendor independence" is a state of mind not a deliverable. An example of this is the SQL statements within the JDBC. Different vendors support different variants of SQL. Many don't even support all of, say ANSI-92 SQL. Even when when equivalent statements X and Y are both supported by Oracle and MySQL, for example, X may be the better choice for Oracle and Y for MySQL. For example, when updating a row, should you use a single prepared statement that mentions all the non-PK fields: "update t1 set f1=? , f2=? , ... fN=? where ..." or, if you know which fields have changed, only mention those fields: "update t1 set f17=? where ...". One solution to vendor independence is to use a higher-level framework like Hibernate where smart guys wearing thick glasses have sweated the details so you don't have to. You can avoid writing SQL in some cases, make use of configuration files where it's easier to centrally tweak settings, use HQL as an abstaction on top of SQL, etc...


There is no emoticon for what I am feeling!
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94

In Oracle, when you do a select - the results are fixed as of the point of time (roughly speaking) your select started(in the default transaction isolation level). Even if you insert and commit records during the select run, it would still show the same result as was true at the point it ran (in the default transaction isolation level). I cover these concepts in detail in my book in a separate chapter on Transactions.


Thank you that helps.


If it is a genuine requirement, you could write a generic procedure to count from a given table but that does not solve the original problem which was for any given query results (remember you can have joins.)


And this too, knowing the limitations will help me make the decision on case by case basis, as there seems to be no one way that works all the time and will never change.



Hibernate sounds interesting and I should have a look at it, even though I strongly agree with Menon on the flexibility and lack of control issues one has to live with when using a solution such as that, but I also believe that for some projects, smaller in scale with lower performance requirements, Hibernate will be ideal to get going quickly and not get bogged down by details that may not be of such great importance. Especially if the guys with thick glasses are trying to write a specific sql for each database out there .

Thank you,
George
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to get num of rows in ResultSet