• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC framework that caches(not close) ResultSets?

 
florin marcus
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The normal behaviour I've seen across JDBC implementations is to acquire a connection, create and execute a statement, iterate result set, then close result set, statement, connection.

Does anyone know about any framework (something like JDBCTemplate, IBatis etc) designed to hold connections between calls, keeping PreparedStatements and ResultSets objects opened?

Oracle has a framework (Oracle ADF) which by default is using the described approach. Based on my tests, this causes lot of memory consumption, affecting scalability.



 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch.

I think you are referring to a database connection pool. Such pool can be used/set up in any Java app server to cache idle connections.

In code, you should still close result set/statement/connection after each transaction or at the end of each method as best practice.
 
Paul Clapham
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's generally considered not the best design to leave ResultSets open for a long period of time, because it makes the database server do extra work. And if you have a lot of users doing that you can really overload the server. That's why the examples you see generally don't do that, especially since frameworks are aimed at larger installations rather than smaller ones. And in web applications (you haven't described your context so I don't know if you're asking about that) leaving ResultSets open can easily result in orphaned ResultSets which don't ever get closed.

In your example by keeping ResultSets open you reduce memory utilization in your application at the cost of increasing the load on the server. The latter part wasn't part of your testing, right? But perhaps if you have a small application it doesn't matter.
 
florin marcus
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul,

About the memory consumption:
I was testing against Oracle JDBC driver ( version 10) who is built to trade memory for performance. Internally, it allocates large amounts of byte and char arrays in a static way, based on JDBC metadata, allocating the largest amount possible for the result set immediately after execution. So, even if the query return nulls, the space is consumed. By not closing the statement, the memory won't be released.

On the other hand, it makes sense to keep result sets opened for scrolling across excel-like tables, like this one:
http://jdevadf.oracle.com/adf-richclient-demo/faces/components/table.jspx
Otherwise, every scrolling operation involves re-executing the statement and reiterating over the ResultSet from the start.


Is why I am very interested in hearing opinions about detached result set solutions ( something based on javax.sql.rowset.WebRowSet, for example).
What's surprising for me is that I can't find any framework using this approach and I wonder why...


Just as a note, this is a web application scenario, running on Weblogic, using Oracle datasource.


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic