ResultSet Size?

[milage]

Hi,

I have looked around to see if anyone has asked this before but they don't so to have!

How do you find the number of rows in a resultset?

Cheers

Robert Miles

 

[milage]

also, another question.

If I want to return to the beginning of my ResultSet do I use the first() method as follows:

rs.first();

where rs is the name of my resultset?

However, when I do this I get a NullPointerException thrown, why could this be? I believe that my recordset is still open:

while(rs.next()) {
out.println(rs.getString(2));
}

rs.first();

while(rs.next()){
out.println(getInt(1);
}

Thanks in advance

Robert Miles

 

[Swamphen]

To answer your first question:

resultSet.getMetaData().getColumnCount()


I don't know the answer to your second question.
If your resultset was closed, you would still not get that NullPointerException. So...it beats me.

Swamphen

 

[milage]

Hi,

I may be wrong but doesn't the getColumnCount() return the number of attributes of each record in the set. I need to return the number of rows (records) in the set.

Cheers

Rob

 

[Swamphen]

Oeps, sorry ...
I was too fast.

And I'm sorry again, because I don't know that.

I only use the "while rs.next()"-method to go through the data. If you want to know the number of rows afterwards, you can obviously use a counter. If you want to know it in advance, then I must disappoint you.

What you can do as well is :
executeQuery(statement.("select count(*) from tablename&quot);

sorry again,
swamphen

 

[AdamRice32]

This is a very interesting problem, and unfortunately there are no effective solutions (by effective I mean reliable or fast).

In thinking about this problem a little while ago, I thought of a few potential solutions. One would be to run the same query with a count(*) instead of the actual columns - this would give you the number of rows. Then immediately following that query you would perform the actual data query, returning you the row details. However, this poses a big problem if many transactions are performed on the table(s) in question, as the data in the table(s) could conceivably change between the count(*) query and the second query. Thus, not "reliable".

Another solution would be to select rownum in your SQL statement (as part of the whole query). Then, you could do a last() call on the ResultSet (moving the cursor to the last row), pull the last rownum column, and then jump back to the beginning of the ResultSet by calling first(). However, this operation will iterate over the entire ResultSet, row by row, which will be extremely slow if the number of results being returned is large. Thus, not "fast".

If anyone else has solutions that they've seen work for this type of operation, please send them along.

Best,
Adam

 

[meadandale]

The reason your code is not working is probably related to your database or your JDBC driver. In your code you move through the result set and then try to scroll back to the beginning and do it again. JDBC 1.0 doesn't support scrollable cursors so you can only move forward through the result set. In order to call rs.first() after you've iterated through the entire result set, you will need to make sure that your database driver is JDBC 2.0 compliant.

As far as finding the number of rows that the result set returned, the previous advice is pretty good. You can either run another query to get the row count or use a counter. In general, you would use a Collection such as a List to store the results from the result set so you don't really need to know how big it is (for instance if you were using an array instead and needed to know how big to make it) since it will resize itself if necessary. You could always fill the Collection and when you are done find out its size() instead of keeping a counter.

Regards,

Charles

 

[sandyrao]

You could try getting the row count of your select statement on the database itself and include this value in your query. Then, you could retrieve this value from your resultset.
you query would look like:
"Select count(*),a,b,c from Table where ......"

 

[MultipartRequest]

Easy, just do:
....
rs.last();
int number = getRow();
rs.beforeFirst();
while(rs.next()) {
...
}

 

[MultipartRequest]

Easy, just do:
....
rs.last();
int number = rs.getRow();
rs.beforeFirst();
while(rs.next()) {
...
}