aspose file tools*
The moose likes JDBC and the fly likes Is rs.getCount the best option? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is rs.getCount the best option?" Watch "Is rs.getCount the best option?" New topic
Author

Is rs.getCount the best option?

Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
I wonder if my logic is correct? I feel like I should not be using count(*) because if I get no results it returns a 0, which is still a result correct?


String query = "select count(*) FROM GSP_AWB_SUMMARY WHERE TRACKING_NBR = " +
quote + masterTrackingNumber + quote;

if (debugging)
System.out.println(query);

logger.debug(query);

rs = statement.executeQuery(query);

if (rs.next()){
releaseResources(connection, statement, rs);
return true;
}
else{
releaseResources(connection, statement, rs);
return false;
}
Edwin Keeton
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

Maybe you could re-write your query to use EXISTS.
Something like this,

SELECT * FROM GSP_AWB_SUMMARY WHERE EXISTS
(SELECT * FROM GSP_AWB_SUMMARY WHERE TRACKING_NBR = masterTrackingNumber)


SCJP, SCWCD
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61606
    
  67

What's your beef with zero?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
I have no beef with 0, but I would just need to do a rs.getInt("COUNT") I guess. Just really wanted to know what most people do and what the standard was was if there is such a thing.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30913
    
158

Originally posted by Anthony Smith:
I have no beef with 0, but I would just need to do a rs.getInt("COUNT") I guess. Just really wanted to know what most people do and what the standard was was if there is such a thing.

This is the standard. You know the query will return one row and one column.

Note that the column is not called "COUNT" in all databases. It is more generic to use rs.getInt(1) or use an "select count(*) as COUNT ..." in your query to make the name explicit.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Phillip Koebbe
Greenhorn

Joined: Jun 22, 2005
Posts: 27
I'm not sure I understand exactly what it is you're trying to do. If you are wanting to know how many rows are in your ResultSet, you can do:



Using MySQL's Connector/J JDBC driver, that's the best I could come up with. Unless I've overlooked the obvious, I couldn't locate a rs.getCount() or rs.getRowCount().

That's one of the two things I'd like to see standard in all JDBC drivers. The other one being named parameters in PreparedStatements (not just Callable). So I could do something like:



I've got some insert statements with 30 and 40 columns and I have to be careful to make sure that my assignment statements stay in sync with the SQL statements. I've written a helper class to make my life easier, but it's not nearly as classy as native support.

Peace,
Phillip

[ June 22, 2005: Message edited by: Phillip Koebbe ]
[ June 23, 2005: Message edited by: Phillip Koebbe ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Anthony,
I'm assuming that you merely want to test for the existence of a particular "tracking number" (TRACKING_NBR) in your GSP_AWB_SUMMARY table.
If that is the case, then you're (obviously) not interested in the contents of your result set, but merely whether your query returns something or not.

Most databases allow you to use literals (either string or number) in your SQL queries. For example, in an Oracle database, I could do the following:

So in java, you would merely need to check whether the above query returned a row (or not), and I would do that like so:

Of-course, the only difference between my method and yours, is that perhaps my query is slightly more efficient than yours, but you would need to verify that with your database.

Hope this has helped you.

Good Luck,
Avi.
Alin Sinpalean
Greenhorn

Joined: Jun 02, 2005
Posts: 6
Originally posted by Phillip Koebbe:
[QB]I'm not sure I understand exactly what it is you're trying to do. If you are wanting to know how many rows are in your ResultSet, you can do:



Using MySQL's Connector/J JDBC driver, that's the best I could come up with. Unless I've overlooked the obvious, I couldn't locate a rs.getCount() or rs.getRowCount().


That's because it's just as difficult for a JDBC driver to retrieve the row count. The DBMS doesn't usually return a row count before returning the rows (except when using server side cursors) so the only solution for the driver to calculate the row count is to read and cache all rows into memory or, even worst, to disk.

Doing rs.last() and rs.getRow() isn't much better either. That's because you need a scrollable result set for that to work. And as most DBMSs/drivers do not support server side cursors you will end up with a client side cursor i.e. exactly the same as in the first case: all rows cached by the driver to memory or disk. And even in the best case of them all when the DBMS/driver combination does support server side cursors it's pretty much the same except that the DBMS is doing all the caching instead of the driver.

The point is that you usually don't need row counts, except in very rare cases e.g. when doing result paging. And even in these rare cases you can do without the actual row count: if you have 10 000 rows matching a query I don't think anyone is going to click next through 1 000 pages, so you could simply say "More than 100 results" and page through the first 100 results.

Alin,
The jTDS Project.
Phillip Koebbe
Greenhorn

Joined: Jun 22, 2005
Posts: 27
Alin,

Good information. Thanks for enlightening me.

I'm running through my code in my head trying to remember where I use rs.last() and rs.getRow(), and I think the only place that is such that it wouldn't matter if it were cached to memory (or disk for that matter). I wrote a DataBoundTableModel and DataBoundTable that ties a resultset to a JTable for display. In the table model's getRowCount() method, I do the last/getRow thing, but since all the rows are going to be put into the table anyway, caching isn't a concern (or at least it doesn't seem to be). This is also the way that Hortsmann and Cornell do it in Core Java2, which seem to be pretty good books.

But you're quite right that if all you need is a count of rows that will satisfy a particular query, a "SELECT COUNT(*) AS MyCount ..." would be much more efficient.

Peace,
Phillip
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Is rs.getCount the best option?