Hi list,
I am having some some difficulty maintaining control over the db connections
in the pages of the app I'm working on and I would like some help.
We are using JRun 3.0 with connection pooling turned on. In most of the
docs that I see online, connections are opened on a page (get new connection
from the pool) and then closed at the end. If one works directly with the
connection obtained from the pool, 2 scenarios can happen that can lead to
all kinds of weird bugs:
1) A particular page does not close the connection it obtained. That
connection is never returned to the pool so it's pretty much wasted
(connection leak).
2) A particular page can call connection.close() multiple times (due to some
flaws in logic). This can lead to a different set of problems. The first
time close() is called, the connection is placed in the pool, ready to be
used again. When close() is called the second time, if that particular
connection is in use by some other page, that page will get killed. Really
hard to find these once they are in the open.
So, in an attempt to deal with these 2 issues, I wrote a connect bean (code
below) which is created with session scope. This bean has an internal
variable of type connection and a boolean flag. If a connection is open and
not closed, the next time open is called (by another page in the same
session), the same connection is returned and therefore connections are not
lost.
Also, only the first close() is executed. All the rest are ignored until
open() is called again.
In theory, this code should do the job and work fine. In practice, I still
see some weird things happening every now and then. I would like to know
how other people deal with these issues and what solutions they came up
with. Perfect jsp pages are still the goal but we know the world is not
perfect and as such, we have be proactive in dealing with the errors that
could come up.
Also, here's a request for the JRun team: in the pooling implementation, can
it be done in future releases so ALL connections with some big inactive
period be killed (as opposed to only the ones that were returned back to the
pool)? This way, a developer can keep connections in control even if some
pages 'forget' to close them and return them to the pool (this would be in
addition to the current timeout value that kills only the inactive
connections that are in the pool).
Thanks,
Cristian
=========================================================================
import javax.naming.*;
import java.sql.*;
import javax.sql.*;
import javax.servlet.http.*; // for HttpSessionBindingListener
/**
* The connectBean class is a java bean that opens and closes db
connections.
*/
public class connectBean implements HttpSessionBindingListener {
// this is the private variable which keeps a reference to the
// current active connection for this bean
private Connection dbCon;
private InitialContext context;
private boolean isOpen = false;
public connectBean() {}
/**
* Closes a connection for the instance of the connectBean
*/
public synchronized void close() throws Exception {
if (dbCon != null && isOpen) {
dbCon.close();
isOpen = false;
}
}
public Connection getConnection() {
return dbCon;
}
/**
* Opens a connection specified by the database driver
* <dbDriver> and the database url <dbUrl>.
* Returns a Connection <dbCon>. Only open a connection if the
* current one is either null or closed.
*/
public synchronized Connection open() throws Exception
{
if (dbCon == null || !isOpen ) {
// Get the JNDI context
context = new InitialContext();
// Lookup the data source
DataSource ds = (DataSource)
context.lookup("java:comp/env/jdbc/myname");
// Get the connection from the data source
dbCon = ds.getConnection();
isOpen = true;
// Close the context
context.close();
}
return(dbCon);
}
//**************************************************************************
********
//**************************************************************************
********
// Methods for the HttpSessionBindingListener interface
//**************************************************************************
********
//**************************************************************************
********
public void valueBound(HttpSessionBindingEvent e) {
// The object was added to the session - we do nothing here
}
public void valueUnbound(HttpSessionBindingEvent e) {
// The object was removed from the session - either the session was
invalidated or the
// session timed out. Close the open connection (if any).
try {
this.close();
} catch (Exception ex) {}
}
}
----------------------------------
Cristian Satnic
Sr. Consultant
Tigris Consulting
212.481.1174 x10
[EMAIL PROTECTED]
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
