Home > Archive > Java Databases > September 2005 > How to count rows using metadata features of JDBC
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to count rows using metadata features of JDBC
|
|
| surfer dude 2005-09-26, 6:56 pm |
| I am relatively new to Java/JDBC programming.
I know that you can count columns in a resultset row using the
getColumnsCount() method. However, given a resultset, is there one
simple method that can be called to count the number of rows in the
resultset without looping over each row in the resultset ?
TIA for all your help.
| |
| Joe Weinstein 2005-09-27, 6:57 pm |
|
surfer dude wrote:
> I am relatively new to Java/JDBC programming.
>
> I know that you can count columns in a resultset row using the
> getColumnsCount() method. However, given a resultset, is there one
> simple method that can be called to count the number of rows in the
> resultset without looping over each row in the resultset ?
>
> TIA for all your help.
Hi. There is no way, via metadata, nor any way via any regular query
either. You could do a prior query, eg:
"select count(*) from mytable where ....."
and then do the real query "select what I want from mytable where ..."
But unless you have locked the table, some other process might
alter the contents of the table between the count and the
query. For some DBMSes and query types, the table and results
could change *while* the query was half-processed, so even the
DBMS doesn't know how many rows are going to be returned till
the last row is sent...
The only recourse is to know your data as well as you can,
make as selective a query as you can, and program the client
to handle whatever might come back. There is clever SQL that
will return 50 rows, and then the next 50 rows sucessively
till the data you wanted is done, but the syntax is enough
different for each DBMS that you should ask an expert in your
given DBMS to help write it.
Joe Weinstein at BEA Systems
| |
| Bryan E. Boone 2005-09-27, 6:57 pm |
| Joe Weinstein wrote:
>
>
> surfer dude wrote:
>
> Hi. There is no way, via metadata, nor any way via any regular query
> either. You could do a prior query, eg:
>
> "select count(*) from mytable where ....."
> and then do the real query "select what I want from mytable where ..."
>
> But unless you have locked the table, some other process might
> alter the contents of the table between the count and the
> query. For some DBMSes and query types, the table and results
> could change *while* the query was half-processed, so even the
> DBMS doesn't know how many rows are going to be returned till
> the last row is sent...
>
> The only recourse is to know your data as well as you can,
> make as selective a query as you can, and program the client
> to handle whatever might come back. There is clever SQL that
> will return 50 rows, and then the next 50 rows sucessively
> till the data you wanted is done, but the syntax is enough
> different for each DBMS that you should ask an expert in your
> given DBMS to help write it.
>
> Joe Weinstein at BEA Systems
>
If you have a jdbc driver that has cursor level access you can do:
resultset.last();
int rowCount = resultset.getRow();
be sure to reposition the cursor back to where it was (if it's relevant)
-Bryan
| |
| Alfred 2005-09-29, 6:56 pm |
| Bryan E. Boone wrote:
> If you have a jdbc driver that has cursor level access you can do:
>
> resultset.last();
> int rowCount = resultset.getRow();
Think that depends on your fetch size. And i'd say it's not a
good idea inside large ResultSet's.
Good luck
|
|
|
|
|