GeeCON Prague 2014*
The moose likes JDBC and the fly likes Understanding ResultSets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Understanding ResultSets" Watch "Understanding ResultSets" New topic
Author

Understanding ResultSets

Shridhar Raghavan
Ranch Hand

Joined: May 27, 2010
Posts: 71
Hi,

I have been looking around at result sets. Result Sets

I wanted to understand

1 - SENSITIVE Scrollable resultset and an INSENSITIVE one
2 - Cursor Holdability

i.e.
HOLD_CURSORS_OVER_COMMIT: ResultSet cursors are not closed; they are holdable: they are held open when the method commit is called. Holdable cursors might be ideal if your application uses mostly read-only ResultSet objects.
CLOSE_CURSORS_AT_COMMIT: ResultSet objects (cursors) are closed when the commit method is called. Closing cursors when this method is called can result in better performance for some applications.


Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

Moving to JDBC.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Shridhar ,

the ResultSet tutorial you linked is actually quite comprehensive. What don't you understand?

As for the cursor holdability, with HOLD_CURSORS_OVER_COMMIT you can read from a resultset even after a transaction in which it was opened is commited. CLOSE_CURSORS_AT_COMMIT - well, the resultset is closed when you commit and you cannot read from it anymore. There is not much more to say about that.

I would always go with the default holdability mode and refrain from using the resultset after commit. The implications of not doing so strongly depends on the database you are using, as is mentioned in the article - some databases might not support HOLD_CURSORS_OVER_COMMIT at all. I'd be surprised if there was a database that didn't support CLOSE_CURSORS_AT_COMMIT, though.
Shridhar Raghavan
Ranch Hand

Joined: May 27, 2010
Posts: 71
As far as Type goes

THe tutorial says - TYPE_SCROLL_INSENSITIVE cannot be scrolled. But the explanation is the same as TYPE_SCROLL_SENSITIVE. Is the difference between two types only on the basis of sensitivity to changes made to the data source.

This brings me to my second doubt. Does sensitivity imply that once i execute the statement by calling execute() and get the resultset, the SENSITIVE resultset would reflect changes made after invoking the execute() method?

And coming to cursor holdability, i took a look back again. Correct me iif i'm wrong. Cursor holdability comes into the picture only when you call commit() on a connection. Are there are other nuances of it's usage?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Shridhar Raghavan wrote:
This brings me to my second doubt. Does sensitivity imply that once i execute the statement by calling execute() and get the resultset, the SENSITIVE resultset would reflect changes made after invoking the execute() method?

Generally, yes. Sensitive resultsets will reflect changes to the database after they were opened. I assume the exact behaviour might differ depending on the database and/or driver, but I cannot help here, I use mainly (if not exclusively) TYPE_FORWARD_ONLY resultsets.

I work with Oracle database and I've therefore read their documentation for JDBC developers. I believe that sensitive resultsets were covered in that material. If in doubt, I'd suggest you to read documentation for your database and/or driver.

And coming to cursor holdability, i took a look back again. Correct me iif i'm wrong. Cursor holdability comes into the picture only when you call commit() on a connection. Are there are other nuances of it's usage?

That is not true.

If you don't change the connection's autocommit setting, which is true by default, there will be commit issued after every SQL command you execute. These implicit commits do affect holdability in the same way as a direct call to commit().

That said, you probably should not use autocommit settings in any but the most trivial applications. In my opinion, transaction handling is one of the most basic feature of any application. You should always plan transactions carefully and of course close them with proper commit() calls. All DB operations should be fully resolved in a transaction. Carrying resultsets between transactions would therefore seem a bit suspicious to me.
 
GeeCON Prague 2014
 
subject: Understanding ResultSets