hi, Am wondering if there is a better way to get the recordCount from the ResultSet object instead of using the "select count(*) from <tablename>" query or by using a while loop...?? Is there any method available to get the record count..?? Thanx, adios, LearningCurve
As far as I am aware, there are 3 common solutions to counting the number of resiults from a query
make the query. as you iterate through the ResultSet, increament a counter.
make the query. if your driver supprts this functionality, jump to the last row and use getRow to find the number of the last row.
execute a count() query against the same query.
(From the Java site: 18. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set? I'm happy for someone to correct me as long as they aren't too rude about it, but I tend to favour the last option. It requires 2 database operations, but isn't too bad as long as the query isn't inefficient. and now that we've covered your question: "learning curve",Welcome to the Ranch! We're light on rules, but one rule that we take seriously is the naming convention. You should take the time to see it here. We require that names aren't obviously fictitious, and strongly encourage people to use their real names. Pleaseedit your profile and update your display name to be a valid one. Accounts with invalid display names get deleted. thanks, Dave.
While this is mostly a matter of personal preference, I prefer using #1 or #3 depending on the scenario. If I would be making the query anyway, I would use #1. If I'm not making the query anyway, I would use #3. I try to avoid doing the same query twice. I do agree with David that the overhead isn't so bad (because the data should be in memory from the previous query), but in some cases the extra overhead could add up.
You could also argue with regards to where the processing takes place. #1 and #2 tend to use a lot on the app server. #3 delegates this to the db server. One could also prefer going this route to decide which option is best suited. I vote for #3. Its clean and most rdbms's have some optimized method already to obtain a count of a table. Why not utilize that!
Best Regards,<br />Nagendra Prasad.
Joined: Jul 10, 2003
thanx all...for u'r views...and info... Even I feel based on the context...either #1 or #3 will be of appropriate use. ...and dave...as per u'r advice...here i am LearningCurve...following..naming conventions... ...I am very happy to be here...with u all at ranch.