File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Select query row count (without explicit select query) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select query row count (without explicit select query)" Watch "Select query row count (without explicit select query)" New topic
Author

Select query row count (without explicit select query)

man pat
Greenhorn

Joined: Aug 05, 2011
Posts: 12
Select firstName, lastName from emp;

(In Java)
Is there any way to know the row count for above select query rather than writing separate query(select count(*) from emp)?
One can calculate the count by creating count variable and incrementing it after entering resultset.next() -- true -- but I am looking for simple alternative rather than taking that route(some built in function which I am not aware).

Additional Info - If we run update/insert/delete queries then we can find rowcount of rows affected. So I was wondering, for select also there should be some easy way.

Thanks!!
- manish

Vijay Tidake
Ranch Hand

Joined: Nov 04, 2008
Posts: 146

Hi,

Regarding your first question,you can get the Row count if you use the ORM framework(Hibernate,iBatis)

and you can get Rows affected by means of int executeUpdate(String sql) .

Thanks


The important thing is not to stop questioning.Curiosity has its own reason for existing.
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
If you execute a SELECT statement, you will not be able to get the rowcount without explicitly selecting it via query or incrementing the counter. Thats by design. The resultset may or may not fetch all possible rows in one shot. So if you want the exact count, you need to either increment the count from the java code (this will be # of resultset.next() calls) or fire a SQL to get the count.

Why do you need the count of records?
man pat
Greenhorn

Joined: Aug 05, 2011
Posts: 12
Thanks all for your reply.

I "think" that if there are rows based on selection criteria(where condition) then only it makes sense that java stores the selected columns/do any type of processing for the selected columns.
(i.e. storing in memory). I believe that internally java must be executing following query by creating two queries:
main query: select fName,lName from emp where condition
java splits it into
query 1: select count(*) from emp where condition -- if count is zero then there is no need to prepare for storing fName and lName
but if count is not zero then executes main query(now query2) and stores data for fName and lName based on condition.

So if java internally is doing like this then it should have count for rows.

- manish m patel
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
I am pretty sure no JDBC implementation would first do a count(*) on the query to see if the data exists or not. count(*) on a table or a set of tables can be extremely expensive and will greatly reduce the response time. Also note that one SQL call is one network round trip from JDBC layer to DB. So if JDBC implementation splits each query into 2, then each will result in another network round trip.
So in short, JDBC implementation will not fire the count(*) query before executing the actual query.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Select query row count (without explicit select query)
 
Similar Threads
copy couple of rows from same table with hiberrnate sequence value
SQL question
count(*) vs count(id)
how to find all tablename and rowcount of each table from database?
How to delete duplicate rows