Servlet's destroy method isn't closing the DB connection even thought I told it too

Question

So I have a servlet that queries the database every minute, so I figure I'll just have one connection open forever (well, until the webapp is stopped). Therefore I'm opening a connection in init() and closing it in destroy(). Thing is, if I look at the DB after stopping the app in tomcat the connection is still open. What's happening?

Here's my code:

public void init() throws ServletException
{
    try
    {
        // Prepare the DB connection
        DriverManager.registerDriver(new com.informix.jdbc.IfxDriver());
        informixConnection = DriverManager.getConnection(DBURL, DBUsername, DBPassword);
    }
    catch(SQLException e)
    {
        throw new UnavailableException("Error connecting to the database");
    }
}

public void destroy()
{
    try
    {
        informixConnection.close();     
    }
    catch(Exception e)
    {
    }
}

There's also going to be a method that actually does a query, and a doGet so a user can get the most recent response, but I haven't finished those yet (although I have tested them and the database connection works).

I've no idea why this wasn't working. I added some logging into the destroy method to confirm it was being called when the app was shut down and suddenly it started working. Weird.

OK, now to go and write it in a not-a-servlet way...

Unrelated to the concrete question, you're making **a major design mistake**. A single DB connection should not be kept open for webapplication's entire lifetime and shared among all requests. This makes your code threadunsafe and the DB will also reclaim it when it's been open for too long. MySQL for example will force a conneciton close when it's been open for 8 hours. — BalusC, Apr 30 '12 at 20:16
It's going to run one query every minute, that's all, so I don't think it needs to be thread safe. Wouldn't opening and closing a connection every minute be an unnessessery overhead and delay? Edit: and surely the DB won't close the connection if it keeps running queries? — mazz0, Apr 30 '12 at 21:35
Servlets are not created/initialized/destroyed on a per-request basis, but on a per-application basis. See also http://stackoverflow.com/questions/3106452/how-do-servlets-work-instantiation-session-variables-and-multithreading As you seem to abuse it with the intention to have a background job, read this as well: http://stackoverflow.com/questions/4691132/how-to-keep-the-servlet-continously-running — BalusC, Apr 30 '12 at 22:52
Thanks @BalusC - yeah, does look like a servlet's the wrong way to go here, reckon I should use ScheduledExecutorService for the background job then? — mazz0, May 01 '12 at 16:12
@BalusC OK, so I've moved my run loop to a separate listener, but that's kicked off by the container, not by my servlet. The listener runs a query against a DB every minute, and remembers the value - how do I get that value in my Servlet? the Listener was started by the container, not by my Servlet, so I don't have a reference to it in the Servlet. (Apologies foe the newbie questions, haven't done any proper dev in a while). — mazz0, May 01 '12 at 17:29

score 2 · Answer 1 · answered Apr 30 '12 at 17:35

According to the documentation, destroy() will be called only after all the threads within the servlet have exited or a timeout occurs. So in theory destroy should be always called unless the container doesn't stop gracefully.

Verify that the container is stopping gracefully.
There are not exceptions inside the destroy() method
Try logging some string at the begging of the method or set up a break point in the debugger.

Also, if you are using an old version of Tomcat be be aware of this bug

score 1 · Answer 2 · answered Apr 30 '12 at 17:59

If you're using Tomcat you could create a resource

  <Resource name="jdbc/AutoOracle"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.driver.OracleDriver"
        username="usrname"
        password="pswd"
        url="jdbc:oracle:thin:@yourdb:1521:yourdb"
        maxActive="1500"
        maxIdle="30"
        maxWait="5000"
        removeAbandoned="true"
        removeAbandonedTimeout="900"
        timeBetweenEvictionRunsMillis="300000"
        minEvictableIdleTimeMillis="1800000"
        testOnBorrow="true"
        testWhileIdle="true"
        validationQuery="select 'test' from Dual"
        validationQueryTimeout="3"
        />

In your servlet create a Context

Context dataSourceContext = new InitialContext();

Then use the context to create your db connection inside of a method that needs the connection.

  Connection conn = null;
  try{
    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/AutoOracle");
    conn = ds.getConnection();
    if(conn == null){
      log.error("Connection is null");
    }else{
      // do some work
    }
  }catch(Exception e){
    // handle exceptions
  }finally{
    try{
      conn.close();
    }catch(Exception e){
      // handle exception
    }
  }

score 0 · Answer 3 · answered Apr 30 '12 at 17:27

The init() and destroy() methods are tied to the lifecycle of the Servlet.

The lifecycle of a typical servlet is the life time of application in the container.

In a normal case, for a generic servlet, the application is deployed to the container, and the application is started. At this point, the servlet does not exist.

Once a request is directed to a URL mapped to the Servlet, the container will check if it has not started an instance of that Servlet yet. If not, it will call the init() method on the Servlet and then start routing requests to it. There is typically only ONE instance of a Servlet within an application at a time.

As an alternative, a Servlet can be configured with a load-on-startup parameter in the web.xml, and if this exists, the Servlet will be initialized during application startup rather than waiting for a request to initialize the Servlet.

Later, when the container is shut down, or the application is undeployed, the container will call the destroy() method on any Servlets that have been initialized during the life time of the application.

So, in summary, if you are expecting the init() and destroy() methods to tied to individual requests, then your expectation are misplaced. For that you would look to a ServletRequestListener (Servlet 3.0), or ad hoc something using a Servlet Filter (pre 3.0).

"I figure I'll just have one connection open forever" for this part I think mazz0 understands that the init/destroy methods won't be tied to individual requests. — user454322, Apr 30 '12 at 17:38

Servlet's destroy method isn't closing the DB connection even thought I told it too

3 Answers3