Java Oracle connection pooling - Closed Connection exception

Question

Hello,

This post is intended to be less of a question and more a confirmation that I'm doing things correctly. I've seen many similar posts but I'm not sure I fully understand everything that's been said.

The problem is that, after a certain amount of time, I get an exception when trying to establish a connection to my oracle database. (I'm using Tomcat 6.0 and Spring)

Previously I had the following configuration:

private PoolDataSource poolDataSource = null;

public MainDAOImpl(String url, String username, String password)
        throws Exception
{
    poolDataSource = PoolDataSourceFactory.getPoolDataSource();

    try
    {
        poolDataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        poolDataSource.setURL(url);
        poolDataSource.setUser(username);
        poolDataSource.setPassword(password);
    }
    catch( SQLException e )
    {
        ...
    }
}

public List<Object> getValues(String query)
{
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try
    {
        connection = poolDataSource.getConnection();
        preparedStatement = connection.prepareStatement(query);

        ...
    }
    catch( SQLException e )
    {
        ...
    }
    finally
    {
        //close connections
    }
}

However, sometimes the preparedStatement = connection.prepareStatement(query); threw an SQLException with a "Closed Exception" message.

It's important to note that the MainDAOImpl's constructor gets called only once per server restart (it's dependency injected via Spring).

I've recently changed my setup like so:

private DataSource dataSource = null;

public MainDAOImpl()
        throws Exception
{
    try
    {
        Context initContext = new InitialContext();
        Context envContext = (Context)initContext.lookup("java:/comp/env");
        dataSource = (DataSource)envContext.lookup("jdbc/myOracleConn");
    }
    catch( NamingException e )
    {
        ...
    }
}

and poolDataSource.getConnection() to dataSource.getConnection().

I've also added the following Resource to my Context in Tomcat:

<Resource name="jdbc/myOracleConn" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="<myURL>"
          username="<myUsername>" password="<myPassword>"
          maxActive="20" maxIdle="10" maxWaith="-1" />

This basically follows http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html word-for-word.

Everything seems to be working. My question is, will these changes solve my closed connection problem or is there something different I need to do?

Thanks,

B.J.

Answer 1

First of all, if you are using Spring for dependency injection, I would recommend that you also use DI to inject the DAO's dependencies into it.

In other words, your DAO should have a DataSource injected into it, rather than the DAO implementation knowing either 1) what type of DataSource to construct or 2) how and where to look it up in JNDI. Spring can handle JNDI lookups for you.

I'd also recommend using Spring's JdbcTemplate, as it makes for a great wrapper over raw JDBC calls yourself.

Finally, the actual exception you are getting may just be because the database server is closing long-open connections. Not sure which connection pool implementation you are using, but in commons-dbcp there is an option for a "validationQuery" which the pool will execute before returning a connection to verify the connection is still valid. I'm sure most other pools supply similar features, which I would recommend here - this way your DAO is never receiving stale connections from the pool.