Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Select query row count (without explicit select query)

 
man pat
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 148
Hibernate Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic