File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes connection object reuse Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "connection object reuse" Watch "connection object reuse" New topic

connection object reuse

Lidia Cyc

Joined: Aug 05, 2003
Posts: 25
i have a question about the right architecture.
i have an application which uses connection pool. there are different methods at my "model" tier which has to work with db: f.e. a method which makes update and method which returns some values taken from resultset.
i know that in many cases i'll have to use these "update" and "select" methods one right after another, and in the future may be even a number of such methods together . the question is:
should each of these methods take a connection from the connection pool inside the method body (which will take 2 or more connections from the pool but keep the mvc architecture clean), or should they recieve a connection as a value passed from outside and just use different statements (which will use only one connection from the pool, but will move the call to the pool to the view tier) ?
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
There are a couple of situations you need to consider-
1. what is the autocommit settings on the connection?
if this is set to false
This will influence if u need to send the connection object as a parameter.
i.e of the 'select' needs to read what the 'update' as done, then they
should either have been done using the same connection or the 'select'
should be reading committed data.
2. from a puristic viewpoint
it is practise to set commit to true. therefore each transaction with the
database must be granular and complete in itself. from this viewpoint, it
is best to obtain a new connection for each 'sql' activity.
hope this helps.

Best Regards,<br />Nagendra Prasad.
Lidia Cyc

Joined: Aug 05, 2003
Posts: 25
thanks. well, autocommit settings can be changed. but as i understand you'd pick the second possibility.
Ryan Bailey
Ranch Hand

Joined: Feb 26, 2002
Posts: 134
Hi Cyc,
I would also go with the second option. It is the container's responsibility to manage the pool. I would leave it up to the container. I believe it would be just as efficient to use the container as it would be to pass the connection object to different SQL statements depending on how you had your container configured.
Just my $.02

Java: The power, elegance, and simplicity of a hand grenade
I agree. Here's the link:
subject: connection object reuse
jQuery in Action, 3rd edition