This week's book giveaway is in the OCMJEA forum. We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line! See this thread for details.
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.
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?
Joined: Aug 05, 2011
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
Joined: Feb 22, 2011
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.