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

how to count rows

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
hi all,

i donot know hot to get count of rows which match a specific criteria
froim a database.i donot want to use a counter inside a while(next())
loop. is any efficient way there???

regards

srijan
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
select count(*) from tablename;
(or)
select count(*) from tablename
where col1 = something;
(or)
checkout the sqlca....
do your homework. one of the rows will be the number of rows
returned.
(or)
there maybe a way to find this out within the meta-data but i can not remeber off the top of my head right now.
hope this helps...
Milind Kulkarni
Ranch Hand

Joined: Jun 01, 2000
Posts: 146
Hi,
Monty's solution would definitely work but would it be efficient?? I wonder why you don't want to use the next() of ResultSet object. If you have any specific reason for not using next() of ResultSet object please let us know so that we can do further analysis.
Regards,
Milind
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Would select count(*) from tbname; be efficient?
Yes. because processing is done within the db engine where the data is. To answer a question with a question.
1. Would it be more efficient to transport 1 or 10,000,000+ rows over you network? I would choose the one row. If all I had to do is count the number of rows.
2. Would it be more efficient to process 1 or 10,000,000+ rows within you application/applet. I would choose the one row. If all I had to do is count the number of rows.
think about it, Monty6
Milind Kulkarni
Ranch Hand

Joined: Jun 01, 2000
Posts: 146
Hi Monty,
Your point is valid but I am under the impression that he is just interested in using COUNT(*) instead of next() of ResultSet object in the for loop and he is anyway going to process thousands of records.
Regards,
Milind

[This message has been edited by Milind Kulkarni (edited August 14, 2000).]
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Milind,
In that case, try using ResultSet last() and getRow() methods.
Fyi, Watch out for the Statement setmaxRow() method. This method
can limit your ResultSet without warning!
Reguards,
monty6

------------------
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
no my point was to count records from java not using oracle commands from rseultset.
if i use a counter and records are 10000 it is not efficient.
i mean when we have getcolumn count !!! do not we have getrow count()
or something .....
srijan
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
I hate to say RTFM but... I think it is time you should read
the JDK documentation provided by sun... It's FREE.
I think if you spend about an hours review the java.lang.sql package ... most if not all of your question will be answered.
I hate it when someone tell me to RTFM...
But, when they tell me where and what to read I appreciate there efforts.
We are all here to help on and other
Stay in touch, Monty6

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
I have RTFM'd myself...
In JDBC API Tutorial... JDBC 2.0 core page 581
Code Fragment:
Resultset rs = stmt.execQuery("some sql goes here);
rs.last(); //make current row last row in rs
int numberofrows = rs.getRow(); // gets current ros number
That's it....

sravan kumar
Greenhorn

Joined: Sep 13, 2000
Posts: 5
Here you have to take the help of the ResultSetMetaData interface
you have to first get the reference of the ResultSetMetaData
which will return the ResultSet from which you can invoke the
method of the column count
Regards
Shravan
[This message has been edited by sravan kumar (edited September 13, 2000).]
Mordred
Greenhorn

Joined: Apr 11, 2003
Posts: 1
I had the same problem and only the "count(*) from .." works.
I use the JDBC 2.0 driver but if I execute the following method on an EMPTY table :
public int getResultSetSize(ResultSet rs) throws SQLException {
rs.last();
return rs.getRow();
}
I get the value 1 instead of the value 0 !
I guess this is because a row filled with NULL-values is still accounted for so it seems.. :s
Weird if you ask me..
Wich obligates me to do it via the rowcount-technique...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to count rows