commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Craig R. McClanahan" <>
Subject Re: [DBCP] Example init servlet to set up a connection pool?
Date Fri, 05 Mar 2004 22:34:39 GMT
Quoting Adrian Beech <>:

> G'day folks,
> Can anyone point me in the direction of where I might find examples of how
> to put together a init servlet which would set up a connection pool?  The
> server boffins alas do not allow us wee plebs to fiddle in Tomcat's
> server.xml file so we need to set up our resources from within the
> application itself.  I'm hoping to create and initialise the connection pool
> resource and toss the reference into the JNDI name space using a servlet
> that is fired up when the application is first kicked into life.  Parameters
> such as the JDBC driver, connection string, user ID and password for the
> data source will be derived from init params defined for the this servlet in
> the web.xml file.

The simplest way to do this is probably to use the same technique that Tomcat
itself uses ... the BasicDataSource class.  This is a pretty straightforward
JavaBean that you can instantiate, and then configure by setting its
properties, before calling getConnection() for the first time.

  BasicDataSource bds = new BasicDataSource();

One challenge you'll immediately run into, though, is that the JNDI context
provided by Tomcat (and, AFAIK by all other servers) is read only.  At best,
you'll be able to store the resulting DataSource into a servlet context

If you're using a recent Tomcat (4 or later), you should also consider using a
ServletContextListener, rather than a servlet's init() method, to configure
your  connection pool.  Simply declare a <listener> element in the web.xml
file, with a nested <listener-class> element containing the fully qualified
classname of your setup class.  Then, set up the connection pool in the
contextInitialized() method, and clean it up in the contextDestroyed() method.

> I've taken a glimpse at the manual pooling example from DBCP and noticed a
> lack of try/catch blocks.  I guess it is this which is basically what I'm
> trying to figure out... Where to catch and how to handle the caught
> exceptions, etc.  I also assume that the code in this example can be dropped
> into a servlet, try/catch blocks not withstanding, and that's that?

I'm not sure which example you are actually looking at, but in general you'll
definitely need try/catch blocks, because nearly all the methods you will be
calling throw SQLException.  The most common mistake when using a connection
pool is to fail to return the connection when you are done with it.  To solve
that problem, I tend to write my database access code like this:

  DataSource ds = ...; // Get from context attributes or wherever
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
    conn = ds.getConnection();
    ps = conn.prepareStatement("select ...");
    rs = ps.executeQuery();
    while ( {
      ... deal with this row ...
  } catch (SQLException e) {
    ... deal with exception ...
  } finally {
    if (rs != null) {
      try {
      } catch (SQLException e) {
      rs = null;
    if (ps != null) {
      try {
      } catch (SQLException e) {
      ps = null;

    if (conn != null) {
      try {
      } catch (SQLException e) {
      conn = null;

The "finally" block ensures that the Connection.close() call will always occur,
even if other exceptions are thrown during the actual processing.

> Thanks if you can help.
> AB


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message