jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes ResultSet caching? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet caching?" Watch "ResultSet caching?" New topic
Author

ResultSet caching?

Kelly Dolan
Ranch Hand

Joined: Jan 08, 2002
Posts: 109
Has anyone ever heard of JDBC (using a JDBC-ODBC driver) caching regular ResultSet results?

Here's my example...I have a DAO that gets called constantly by a servlet! Therefore, the servlet creates an instance of the DAO, the DAO acquires a database connection from the J2EE and retains it, prepares a PreparedStatement and then uses it when asked to return information. The PreparedStatement takes one parameter (e.g., SELECT * FROM mytbl WHERE id = ?). The chain of events are:

1. The servlet calls the DAO and requests data for id=3.
2. The DAO sets the parameter to 3 and executes the query. 1 record is returned and the result set is returned.
3. ANOTHER APPLICATION DELETES THE RECORD ASSOCIATED WITH ID=3 BEHIND THE SCENES.
4. The servlet calls the DAO and requests data for id=3.
5. The DAO sets the parameter to 3 and executes the query. 1 record is returned and the result set is returned.

At step 5, I am not expecting the result set to return 1 record. I am expecting 0 records.

Thanks!
Kelly
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
Are steps 2 and 4 done in the same transaction or in two different ones? If it's in the same transaction you might get what you're seeing (could depend on the isolation level of the transaction).
Kelly Dolan
Ranch Hand

Joined: Jan 08, 2002
Posts: 109
They are done in two separate transactions.

I've done more investigation and think the following is happening...

The other application is a stateful session EJB running in a J2EE container. I think the J2EE container is optimizing performance by caching database state and not necessarily physically committing the changes made within the EJB. I see what I expect if anyone at anytime within the EJB queries/updates the database. My assumption is, even if the J2EE container is caching stuff and has not yet truly committed the changes, this is okay because for anyone running within the scope of the J2EE container, it is maintaining database integrity. The servlet, however, is running in a servlet container and its database access may not be going through the J2EE environment to get to the database, resulting in it not seeing the changes made by the "other" application.

I have temporarily solved my problem by modifying the EJB to call the servlet to perform the database update when it is needed. Unfortunately, I cannot escape the requirement to enter the EJB...the EJB update to the database happens in conjunction with other things happening in the EJB. I also cannot escape the requirement for the servlet...redirecting these calls to an EJB would render performance in this situation unacceptable because of the number and frequency of calls to the servlet.

It's not pretty but it works. Time permitting, I'd love to find a better solution for this. Any ideas?

- Kelly
v Gupta
Greenhorn

Joined: May 26, 2004
Posts: 8
Hi,

First of all you should not return a result as it is a connected result set.
After executing the query, you should populate the data into a value object and close the result set, statement and the connection as every connection object has a timeout associated with it.

If you don't want stale data, you should go for Serializable isolation level.
After getting the connection object change the isolation level and then go for fetching data. This can be done in Session bean also but one thing you should also understand, it will decrease the performance.

Serializable should be used if data integrity is on high priority then perfromance.

Cheers
Joe Nguyen
Ranch Hand

Joined: Apr 20, 2001
Posts: 161
Could you set transaction to TRANSACTION_READ_COMMITTED then commit and reload the bean instance in the third step? This way your servlet has to wait for the session bean to commit the change.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet caching?
 
Similar Threads
How to store & retrieve a video file from database?
Allow single transaction only
how integrate MySQL and Tomcat
Displaying a table in JSP using JSTL tags
Help me get started