java.sql.SQLException: Closed Connection

0
votes

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):

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)]]

My 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<CondicoesGatilho> listCg = new ArrayList<>();
        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 "<RESULTADO>" + resultado + "</RESULTADO><ESTADO>" + ativaCallback + "</ESTADO><FAIXA_HORARIO_LIBERADO>" + faixaHorarioLiberado + "</FAIXA_HORARIO_LIBERADO><FAIXA_HORARIO_CONFIGURADA>" + faixaDeHorarioConfigurada + "</FAIXA_HORARIO_CONFIGURADA><CONDICOES_GATILHO_CONFIG>" + condicoesGatilhoConfig + "</CONDICOES_GATILHO_CONFIG><GATILHO>" + gatilho + "</GATILHO>";


        } 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 "<RESULTADO>" + resultado + "</RESULTADO><ESTADO>" + ativaCallback + "</ESTADO><FAIXA_HORARIO_LIBERADO>" + faixaHorarioLiberado + "</FAIXA_HORARIO_LIBERADO><FAIXA_HORARIO_CONFIGURADA>" + faixaDeHorarioConfigurada + "</FAIXA_HORARIO_CONFIGURADA><CONDICOES_GATILHO_CONFIG>" + condicoesGatilhoConfig + "</CONDICOES_GATILHO_CONFIG><GATILHO>" + gatilho + "</GATILHO>";
        } 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();
            } 
        }


    }

Configuration Pool:

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

Connection String - Data Base Oracle 10g :

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))

My connection pool statistics.

2
javaoraclejdbc
Sounds like either the database listener or something in your network is dropping the connection, possibly after either a defined idle period or total length of connection, or possibly just from a comms glitch if you can't find a pattern. – Alex Poole

2 Answers

0
votes

I'm guessing that somewhere in your codebase there is a method which gets a connection but does not close() it in a finally block. The code you have posted seems to behave correctly in this regard. You'll need to search through the rest of your code for the offending method(s).

Writing JDBC by hand like this means that it's possible to leak connections. Have you considered using a pattern like JdbcTemplate.execute(callback)?

I recently implemented my own JdbcTemplate in a few lines of code because I wanted to use the pattern without requireing a spring dependency. You could do the same.

0
votes

Could be that your connection gets forcefully closed.

Try checking connection state via conn.isClosed() before execution, and reopen when not.