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

Number of rows in a table?

colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
Hi, I'm using a servlet to browse and modify entries
from a MS-Access database table.
Currently I have a ResultSet object, and I'm using the absolute()
method so that I can read any row.
I have 10 rows at the moment, so the servlet knows its range of
rows it can access. But suppose there were many rows, how would I
retrieve this number from the database?
I've just noticed the getFetchSize() method. Not sure if this is it.
I'll try it now.
[ September 19, 2005: Message edited by: Bear Bibeault ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Colin,
In my opinion, the best way to see how many rows a query will return is to do a "SELECT COUNT(*)" -- before you do your actual query.

Good Luck,
Avi.

P.S. By the way, maybe you should test your servlet when there are 10,000 rows (and not just 10).
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
Thanks, but I'm still unclear on how to do this.
Usually I would write
String query="SELECT column1, column2 FROM table"
but I don't understand how I can use SELCECT COUNT here.
I can't find any good examples either, and the sun API is
usually useless at explaining these things.
Any help is much appreciated.
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi
You will have to write a seperate query for getting the count.
1. First get the count [select count(*) from tablename]
2. Then go ahead with your existing logic.

Java api won't return you how many rows your query has fetched.

Let me know if u have any concern

Regards
Makarand Parab
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
I dont understand how to do it.
How do I get the count, something like
int c="SELECT COUNT tablename";

but that won't work obviously because its a string.
[ September 20, 2005: Message edited by: colin shuker ]
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Colin
Write it this way

lets say
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int count = 0 ;

conn = getConnection() - /*way to get connection object depends on you*/
stmt = conn.getStatement("Select count(*) from tablename")
rs = stmt.executeQuery();
while(rs.next())
{
count = rs.get(0);
}


This will give you the count of the rows you are looking for.


Regards
Makarand Parab
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Colin
Write it this way, More Updates

lets say
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int count = 0 ;

conn = getConnection() - /*way to get connection object depends on you*/
stmt = conn.getStatement("Select count(*) from tablename")
rs = stmt.executeQuery();
while(rs.next())
{
count = Integer.parseInt((String)rs.getString(0));
}


This will give you the count of the rows you are looking for.

Regards
Makarand Parab
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
OK, I think I see what your doing in the second message.
You are just iterating down through the ID column of
the table, then parsing the value to an int.
This seems like an unnecesary solution to my problem,
and I think I'm best off just using previous() and next(), so
that I don't end up searching rows that are outside the table.
I assumed there would be a direct way to get the number of rows.
But it seems wrong to do it this way, when I can probably do this
more elegantly using previous() and next().
But thanks for your help, much appreciated.
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
hey colin
You will get only row when you execute the query [select count(*) from tablename]. You don't have to iterate.
While(rs.next()) is written to check if the query is returning any value else count is zero
if rs.next() returns true means it is returning the value which we are assigning to the count variable

Lets says in the table u have 10 rows
U fire the query [select count(*) from tablename]
it will return 10
rs.next()will return true
count = Integer.parseInt((String)rs.getString("0")); will assign value 10 to the count variable

Hope u got it this time.

Regards
Makarand Parab
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Just to add my two cents. You should use COUNT(*). COUNT() is a SQL function that the database knows about so it is VERY efficient. On most DB's when you do a COUNT with no WHERE or HAVING then the database will return the number of rows that it knows about in it's collection of table meta-data and not even bother looking at the actual data at all.

So COUNT() is the way to go. I am not sure why in the above code there is a getString and a conversion to int. You should be able to call getInt or getLong on the value returned by COUNT without issues.

As far as the rs.next() goes. There were always be exactly 1 row in the result set returned by SELECT COUNT() FROM aTable; Even if there are no rows in the table the result set for the COUNT query will have one row with a 0. You do need to call rs.next once though as with all ResultSets because by convention the cursor for a ResultSet is positioned before the first row.

[Edit: I wanted to make clear that I am addressing the orginal poster (Colin). The other poster is correct but I just wanted to restate some of the points in the hope it may help to clarify them.]
[ September 20, 2005: Message edited by: Maximilian Stocker ]
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Maximilian Stocker
Thanks for your inputs. Learning new things and remembering old things is always fun. Thanks once again.

Regards
Makarand Parab
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Number of rows in a table?