java.sql.SQLException: Closed Connection
<p>I have a webservice running on an application server GlassFish Server Open Source Edition 4.0 (build 89) / JDK 1.7.0_79 / Driver ojdbc14.jar / S.O: Windows Server 2008 R2 Standard. The problem is this, I have a JDBC connection pool configured in the same application server, and then I'm getting the following error (this error occurs intermittently during the day):</p>

<blockquote>
  <p>java.sql.SQLException: Closed Connection
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
      at oracle.jdbc.driver.OracleConnection.privateCreateStatement(OracleConnection.java:845)
      at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.java:799)
      at com.sun.gjc.spi.base.ConnectionHolder.createStatement(ConnectionHolder.java:256)
      at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.createStatement(ConnectionWrapper40.java:75)
      at br.com.todo.ws.CallbackNaFilaService.consultarParametros(CallbackNaFilaService.java:654)
      at sun.reflect.GeneratedMethodAccessor113.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:606)
      at org.glassfish.webservices.InstanceResolverImpl$1.invoke(InstanceResolverImpl.java:143)
      at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:149)
      at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:88)
      at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:1136)
      at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:1050)
      at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:1019)
      at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:877)
      at com.sun.xml.ws.api.pipe.helper.AbstractTubeImpl.process(AbstractTubeImpl.java:136)
      at org.glassfish.webservices.MonitoringPipe.process(MonitoringPipe.java:142)
      at com.sun.xml.ws.api.pipe.helper.PipeAdapter.processRequest(PipeAdapter.java:119)
      at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:1136)
      at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:1050)
      at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:1019)
      at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:877)
      at com.sun.xml.ws.api.pipe.helper.AbstractTubeImpl.process(AbstractTubeImpl.java:136)
      at com.sun.enterprise.security.webservices.CommonServerSecurityPipe.processRequest(CommonServerSecurityPipe.java:210)
      at com.sun.enterprise.security.webservices.CommonServerSecurityPipe.process(CommonServerSecurityPipe.java:142)
      at com.sun.xml.ws.api.pipe.helper.PipeAdapter.processRequest(PipeAdapter.java:119)
      at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:1136)
      at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:1050)
      at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:1019)
      at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:877)
      at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:420)
      at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:687)
      at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:266)
      at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:169)
      at org.glassfish.webservices.JAXWSServlet.doPost(JAXWSServlet.java:169)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
      at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
      at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
      at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
      at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
      at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
      at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
      at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
      at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
      at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
      at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
      at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
      at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
      at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
      at java.lang.Thread.run(Thread.java:745)]]</p>
</blockquote>

<p>My Code:</p>

<pre><code>public Connection getPoolConexaoCallbackDataSource() throws Exception {

        InitialContext context = new InitialContext();
        DataSource ds = (DataSource) context.lookup("jdbc/CallbackNaFilaDb");

        try {

            return ds.getConnection();
        } catch (SQLException e) {
            GeraLog.logInfo("Erro ao obter conexão com o banco de dados CallbackNaFilaDb " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

 @WebMethod(operationName = "consultarParametros")
    public String consultarParametros(@WebParam(name = "CONNID") String connId, @WebParam(name = "SERVICO") String servico, @WebParam(name = "EWTCHAMADA") String ewtChamada, @WebParam(name = "TAMFILACHAMADA") String tamFilaChamada, @WebParam(name = "NIVELSERVICOCHAMADA") String nivelServicoChamada){
        String ativaCallback;
        List&lt;CondicoesGatilho&gt; listCg = new ArrayList&lt;&gt;();
        int servicoId = 0;
        char faixaHorarioLiberado;
        String faixaDeHorarioConfigurada;
        String tratamentoRetorno;
        String condicoesGatilhoConfig;
        String gatilho;
        String resultado;
        Connection conn = null;
        ResultSet rsProgramacao = null;
        ResultSet rsGatilhos = null;
        long nivelServico = 0;
        GeraLog.logInfo("[CONSULTAR_PARAMETROS] PARAMETROS PASSADOS PELA ESTRATEGIA: [CONNID] " + connId + " | [SERVICO] " + servico + " | [EWTCHAMADA] " + ewtChamada + " | [TAMFILACHAMADA] " + tamFilaChamada + " | [NIVELSERVICOCHAMADA] " + nivelServicoChamada);



        String SQL_CONFIGURACAO = "SELECT SERVICO.*, PROGRAMACAO.*\n" +
                                  "FROM SERVICO JOIN PROGRAMACAO\n" +
                                  "ON PROGRAMACAO.SERVICO = SERVICO.ID\n" +
                                  "WHERE SERVICO.NOME = '" + servico + "'\n" +
                                  "AND DIA_SEMANA = TO_NUMBER(TO_CHAR(SYSDATE, 'D'))\n" +
                                  "AND SYSDATE BETWEEN TO_DATE(CONCAT(CONCAT(TO_CHAR(SYSDATE, 'DD-MM-RRRR'), ' '), HORA_OFERTADA_INICIAL), 'DD-MM-RRRR HH24:MI:SS')\n" +
                                  "                AND TO_DATE(CONCAT(CONCAT(TO_CHAR(SYSDATE, 'DD-MM-RRRR'), ' '), HORA_OFERTADA_FINAL), 'DD-MM-RRRR HH24:MI:SS')\n" +
                                  "                AND DATA_EXCLUSAO IS NULL";

        try {
            ConnectDb connDb = new ConnectDb();
            conn = connDb.getPoolConexaoCallbackDataSource();
            rsProgramacao = conn.createStatement().executeQuery(SQL_CONFIGURACAO);

            if(rsProgramacao.next()){
                servicoId = rsProgramacao.getInt("ID");
                resultado = "OK";
                ativaCallback = rsProgramacao.getString("ATIVA_CALLBACK").equalsIgnoreCase("S") ? "1" : "0";
                faixaHorarioLiberado = 'S';
                faixaDeHorarioConfigurada = rsProgramacao.getString("HORA_OFERTADA_INICIAL") + "-" + rsProgramacao.getString("HORA_OFERTADA_FINAL");


                String SQL_GATILHO = "SELECT CG.CONDICAO, GS.VALOR\n" +
                                     "FROM GATILHOSXSERVICO GS JOIN CONDICOES_GATILHO CG\n" +
                                     "ON GS.CONDICOES_GATILHO = CG.ID\n" +
                                     "WHERE GS.SERVICO = " + servicoId;

                rsGatilhos = conn.createStatement().executeQuery(SQL_GATILHO);

                while(rsGatilhos.next()){
                    CondicoesGatilho cg = new CondicoesGatilho();
                    cg.setCondicao((rsGatilhos.getString("CONDICAO")));
                    cg.setValor(rsGatilhos.getInt("VALOR"));

                    if(cg.getCondicao().contains("EWT")){
                       cg.setNomeParametro("EWTCHAMADA");
                    }else if(cg.getCondicao().contains("TAMANHOFILA")){
                       cg.setNomeParametro("TAMANHOFILA");
                    }else if(cg.getCondicao().contains("NIVELSERVICO")){
                        cg.setNomeParametro("NIVELSERVICO");
                    }

                    listCg.add(cg);


                }
                try{
                    nivelServico = Math.round(Double.valueOf(nivelServicoChamada));
                }catch(Exception e){
                    nivelServico = 0;
                    GeraLog.logError("[CONSULTAR_PARAMETROS] EXCEPTION PARSE NIVELSERVICO");
                }
                tratamentoRetorno = expressaoRegular(listCg, Integer.parseInt(ewtChamada), Integer.parseInt(tamFilaChamada), nivelServico);
                GeraLog.logInfo("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | PARAMETROS RETORNADOS: " + tratamentoRetorno);
                String[] str = tratamentoRetorno.split("=");
                try{
                    condicoesGatilhoConfig = str[0];
                }catch(ArrayIndexOutOfBoundsException ex){
                    condicoesGatilhoConfig = "";
                }
                Evaluator eval = new Evaluator();
                 try{
                     if (eval.getBooleanResult(str[1])){
                         GeraLog.logInfo("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | SERVICO: " + servicoId + "-" + servico + " | FORMULA: " + str[1]);
                         gatilho = "1";
                     }else{
                         GeraLog.logInfo("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | SERVICO: " + servicoId + "-" + servico + " | FORMULA: " + str[1]);
                         gatilho = "0";
                     }
                }catch(Exception ex){
                    GeraLog.logInfo("[CONSULTAR_PARAMETROS] EXCEPTION EVALUATOR - CHAMADA: " + connId + " | SERVICO: " + servicoId + "-" + servico + " | EXCEPTION FORMULA: " + ex.getMessage());
                    gatilho = "0";
                }
            } else {
                resultado = "OK";
                ativaCallback = "0";
                faixaHorarioLiberado = 'N';
                faixaDeHorarioConfigurada = "";
                condicoesGatilhoConfig = "";
                gatilho = "0";
            }
            GeraLog.logInfo("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | SERVICO: " + servicoId + "-" + servico + " | ATIVA_CALLBACK: " + ativaCallback + " | FAIXA_HORARIO_LIBERADO: " + faixaHorarioLiberado + " | FAIXA_HORARIO_CONFIGURADA: " + faixaDeHorarioConfigurada);
            return "&lt;RESULTADO&gt;" + resultado + "&lt;/RESULTADO&gt;&lt;ESTADO&gt;" + ativaCallback + "&lt;/ESTADO&gt;&lt;FAIXA_HORARIO_LIBERADO&gt;" + faixaHorarioLiberado + "&lt;/FAIXA_HORARIO_LIBERADO&gt;&lt;FAIXA_HORARIO_CONFIGURADA&gt;" + faixaDeHorarioConfigurada + "&lt;/FAIXA_HORARIO_CONFIGURADA&gt;&lt;CONDICOES_GATILHO_CONFIG&gt;" + condicoesGatilhoConfig + "&lt;/CONDICOES_GATILHO_CONFIG&gt;&lt;GATILHO&gt;" + gatilho + "&lt;/GATILHO&gt;";


        } catch (Exception ex) {
           resultado = "NOK";
           ativaCallback = "0";
           faixaHorarioLiberado = 'N';
           faixaDeHorarioConfigurada = "";
           condicoesGatilhoConfig = "";
           gatilho = "0";  
           GeraLog.logInfo("[CONSULTAR_PARAMETROS] EXCEPTION - CHAMADA: " + connId + " | SERVICO: " + servicoId + "-" + servico + " | ATIVA_CALLBACK: " + ativaCallback + " | FAIXA_HORARIO_LIBERADO: " + faixaHorarioLiberado + " | FAIXA_HORARIO_CONFIGURADA: " + faixaDeHorarioConfigurada + " | ERRO: " + ex.getMessage());
           ex.printStackTrace();
           return "&lt;RESULTADO&gt;" + resultado + "&lt;/RESULTADO&gt;&lt;ESTADO&gt;" + ativaCallback + "&lt;/ESTADO&gt;&lt;FAIXA_HORARIO_LIBERADO&gt;" + faixaHorarioLiberado + "&lt;/FAIXA_HORARIO_LIBERADO&gt;&lt;FAIXA_HORARIO_CONFIGURADA&gt;" + faixaDeHorarioConfigurada + "&lt;/FAIXA_HORARIO_CONFIGURADA&gt;&lt;CONDICOES_GATILHO_CONFIG&gt;" + condicoesGatilhoConfig + "&lt;/CONDICOES_GATILHO_CONFIG&gt;&lt;GATILHO&gt;" + gatilho + "&lt;/GATILHO&gt;";
        } finally {
            try {
                if (rsGatilhos != null) {
                    rsGatilhos.getStatement().close();
                    rsGatilhos.close();
                    rsGatilhos = null;
                }
            } catch (Exception ex) {
                GeraLog.logError("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | ERRO AO FECHAR O rsGatilhos " + ex.getMessage());
                ex.printStackTrace();
            }



            try {
                if (rsProgramacao != null) {
                    rsProgramacao.getStatement().close();
                    rsProgramacao.close();
                    rsProgramacao = null;
                }
            } catch (Exception ex) {
                GeraLog.logError("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | ERRO AO FECHAR O rsProgramacao " + ex.getMessage());
                ex.printStackTrace();
            }


            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception ex) {
                GeraLog.logError("[CONSULTAR_PARAMETROS] CHAMADA: " + connId + " | ERRO AO FECHAR O CONN " + ex.getMessage());
                ex.printStackTrace();
            } 
        }


    }
</code></pre>

<p></p>

<p>Configuration Pool:</p>

<pre><code>Initial and Minimum Pool Size: 8  Connections
Maximum Pool Size: 32 Connections
Pool Resize Quantity: 2 Connections
Idle Timeout: 300 Seconds
Max Wait Time: 60000 Milliseconds
</code></pre>

<p>Connection String - Data Base Oracle 10g :</p>

<blockquote>
  <p>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(LOAD_BALANCE = YES)(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl10))</p>
</blockquote>

<p><a href="http://i.stack.imgur.com/VAaZ8.png" rel="nofollow">My connection pool statistics.</a></p>