title: Java Oracle connection pooling - Closed Connection exception
body: <p>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.</p>
  
  <p>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)</p>
  
  <p>Previously I had the following configuration:</p>
  
  <pre><code>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&lt;Object&gt; getValues(String query)
  {
      Connection connection = null;
      PreparedStatement preparedStatement = null;
  
      try
      {
          connection = poolDataSource.getConnection();
          preparedStatement = connection.prepareStatement(query);
  
          ...
      }
      catch( SQLException e )
      {
          ...
      }
      finally
      {
          //close connections
      }
  }
  </code></pre>
  
  <p>However, sometimes the <code>preparedStatement = connection.prepareStatement(query);</code> threw an SQLException with a "Closed Exception" message.</p>
  
  <p>It's important to note that the MainDAOImpl's constructor gets called only once per server restart (it's dependency injected via Spring).</p>
  
  <p>I've recently changed my setup like so:</p>
  
  <pre><code>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 )
      {
          ...
      }
  }
  </code></pre>
  
  <p>and <code>poolDataSource.getConnection()</code> to <code>dataSource.getConnection()</code>.</p>
  
  <p>I've also added the following Resource to my Context in Tomcat:</p>
  
  <pre><code>&lt;Resource name="jdbc/myOracleConn" auth="Container"
            type="javax.sql.DataSource"
            driverClassName="oracle.jdbc.OracleDriver"
            url="&lt;myURL&gt;"
            username="&lt;myUsername&gt;" password="&lt;myPassword&gt;"
            maxActive="20" maxIdle="10" maxWaith="-1" /&gt;
  </code></pre>
  
  <p>This basically follows <a href="http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html" rel="nofollow noreferrer">http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html</a> word-for-word.</p>
  
  <p>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?</p>
  
  <p>Thanks,</p>
  
  <p>B.J.</p>