• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is rs.getCount the best option?

 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65229
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's your beef with zero?
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic