permaculture playing cards
The moose likes JDBC and Relational Databases and the fly likes select count(*) does not work Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "select count(*) does not work" Watch "select count(*) does not work" New topic

select count(*) does not work

Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
I am trying to execute query select count(*) from users thru jdbc, but get this exception:

Here is the code:

Does it mean that my driver does not support this?
Also, will I get a faster result if I do select count(*) or
rs.getRow(); ?
[ April 30, 2004: Message edited by: Alex Kravets ]

All right brain, you don't like me and I don't like you, but let's just do this one thing so I can get back to killing you with beer.<br /> <br />- Homer Simpson
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Generally, you want to use 'select count(*)' rather than rs.last(). Think about what would happen if your query could result in 1,000,000 rows and you called rs.last(): the database would have to step through all the rows to get to the one you ask for.
Your first set of code should work, assuming the query returns a row. It normally should, but you can test it by using this code:

If the query returns no rows, then it is possible that the database or driver does not support count(*), or that there is something else wrong with some other code of yours that you did not post.
Your second example will never work. You are asking the result set for a column named 'id,' however your SQL command does not retrieve this column. It retrieves 'count(id).' Use rs.getInt(1) or rs.getString(1).
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32815

Your second example won't work for two reasons. One is what Kevin pointed out. You can get around that by using an index or an explicit column name, like select count(id) as "id" from users
Also, you need to call rs.getNext() before you call rs.getString() or rs.getInt(). Otherwise the resultset is not pointing to a valid row and you get exceptions like you did.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
thanks guys!
I agree. Here's the link:
subject: select count(*) does not work
It's not a secret anymore!