I have used this pattern over and over with all manner of JDBC drivers and
have not found one that it does not work with. There were issues with
performance in the initial 1.1 JVM's since try/catch blocks had a fairly
severe overhead, but with the newer 1.2+ JVM's it's negligible. I do
recommend a Connection Pool for actually handling the Connections as shown:
try {
Connection con = DbConnectionPool.getConnection();
try {
PreparedStatement stm = con.prepareStatement("SELECT SOMETHING FROM
SOMEWHERE");
try {
stm.setString(1,"Someone");
ResultSet res = stm.executeQuery();
try {
while(res.hasNext()) {
// Do something with the Result Set values
}
} finally {
res.close();
}
} finally {
stm.close();
}
} finally {
DbConnectionPool.releaseConnection(con);
}
} catch(SQLException x) {
x.printStackTrace();
}
There is no way to strand any database resources with this model.
(*Chris*)
----- Original Message -----
From: "Manavendra Gupta" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 04, 2003 5:53 AM
Subject: Re: [SERVLET-INTEREST] odd oracle error [MAX cursors]
> I'd like to re-open this thread for discussion.
>
> IMO, following is what has been proposed so far:
>
> 1. Use connection pooling.
> 2. Use a singleton class as connection manager
> OR (if your app server supports it)
> 1. Configure JNDI datasource
> 2. Configure connection pool manager
>
> There have been repeated discussions on the best way to close connection
to ensure all resources are freed up properly. There is also that issue with
Oracle implicit cursors not being closed unless you explicitly close the
statement.
>
> Instead of 'making sure' to close connection in the finally block (which I
believe is a repitition of code anyway), can we not have a design strategy
that does this automatically for us? Here's what I suggest (apart from the
steps listed above):
> 1. Create a 'gateway' or 'adapter' class that interfaces with the
connection manager (read: calls the getConnection(), freeConnection() and
other DB interaction methods) with member variables for statement and
resultset.
> 2. This 'gateway' or 'adapter' exposes methods to perform generic database
methods (select, insert, update, delete) - you'd as it always use a standard
gateway for all your entities to use instead of all entities talking to the
database, thereby splattering around the persistence code all over the
application.
> 3. The destructor of this gateway closes the statement as well as the
resultset.
>
> So, you have a domain model (or table gateway, row gateway or whatever
object-relational mapping you chose), your database code is at one single
place, your connections are being managed and best, you don't have to write
a single additional line to ensure you don't exceed the MAX_CURSORS (unless
of course your methods take too long to complete and the load on your
application is extremely high - in which case you'd have to increase the
cursors anyway, no matter what you choose).
>
> One of the pitfalls I see of this design is higher object
creation/destruction, but your statements/resultsets had to be closed anyway
so you just have 4 additional bytes being used on the stack.
>
> Comments?
>
> Cheers,
> Manav.
>
>
___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
