determine resultset size? 1

[zeero]

is there a method to get the size of a result set after executing a query? I have the following code after the query is executed:

...
int rsSize = rs.length();  //<-- length(), size()?
int counter = 0;
while(rs.next()){

String name = rs.getString("first");

if (counter == rsSize -1){ 
 out.print(name+" and ");
 }
 else{ 
 out.print(name+", ");
 counter++; 
 }
}

con.close();
%>

this is relating to my question to attempt to print a set of names with commas in between them and an "and" between the last two.
e.g.
"bob, same, george, and sam"

or

"bob and sam

 

[siberian]

The ability to get the size of the resultset will vary from database to database. Check the javadocs on the JDBC driver you are using for more information.

For example, mysql lets you do this but Firebird does not. Not sure about Oracle.

 

[sedj]

Generally speaking, the number of rows returned in a ResultSet (I take it this is what you mean) cannot be determined beforehand. This usually because it makes no sense (db side). A ResultSet or cursor is extracted at runtime - so if a call does not request a next recored, then the db will not serve it. Its not like executing a query which returns a full number of results in an array or something ... it works differently to that - kind of like "on demand data". Hence most db engines cannot determine the size of a cursor until the query is fully exausted.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[siberian]

You'd probably do best by running the resultset and sticking your data on a list. Then running the list and doing what you propose.

Its not optimal but its the best your going to get and stay compatible across databases.

 

[zeero]

what do you mean by a list?

 

[siberian]

http://java.sun.com/j2se/1.4.2/docs/api/java/util/AbstractList.html

 

[zeero]

if a list could be used, why can't the data be thrown in an array, then sorted and printed out with commas and an "and"

(sorry im a newbie with this)

 

[siberian]

No, you would read the resultset, stick it in a list and then do your 'comma or and' operation on the list.

You know where the end of a list is because its fully constructed. You do not know the end of the resultset since its by nature open ended.

So iterate through the result set, stick each item on a list. Once that is done iterate the list and on the list element print an 'and' instead of a comma.

 

[zeero]

Oh, I understand thanks guys.

 

[zeero]

not that i'm against a list iterator, but what about doing a "select count" query beforehand, then handling it from there?

 

[sedj]

That is one way of doing it ... but what happens if before your "select count()" and the real "select" if a record is inserted in between ?

I would dump the data from the result set into an ArrayList or some collection object and have done with it. This is normal practice, and what most people do. I would stop trying to fight against it and just do it !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[siberian]

You could do that.

The efficiency trade-off is expected size of the data-set. if its not a huge data set (less then 1000 rows lets say) I'd choose in-memory manipulation over double SQL but its really your preference.

Unless of course your data set changes between queries. I'd recommend using a transaction in this scenario to maintain data integrity.