java.sql.SQLException: Closed Connection

19/02/2016

2

Pessoal,

Tenho um web service rodando em um servidor de aplicação GlassFish Server Open Source Edition 3.0.1 (build 22) / JDK 1.7.0_21 / Driver ojdbc6.jar / S.O: Windows Server 2003 - SP2. O problema é o seguinte, tenho um pool de conexão JDBC configurado neste mesmo servidor de aplicação, e estou seguidamente recebendo o seguinte erro:

2016-02-19T15:24:24.903-0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=61;_ThreadName=Thread-1;|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:240)
at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.createStatement(ConnectionWrapper40.java:71)
at br.com.todo.ws.AuthorizationCodeWs.consultarUsuario(AuthorizationCodeWs.java:35)
at sun.reflect.GeneratedMethodAccessor491.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.glassfish.webservices.InstanceResolverImpl$1.invoke(InstanceResolverImpl.java:137)
at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:146)
at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:257)
at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:95)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:629)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:588)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:573)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:470)
at com.sun.xml.ws.api.pipe.helper.AbstractTubeImpl.process(AbstractTubeImpl.java:112)
at org.glassfish.webservices.MonitoringPipe.process(MonitoringPipe.java:138)
at com.sun.xml.ws.api.pipe.helper.PipeAdapter.processRequest(PipeAdapter.java:115)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:629)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:588)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:573)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:470)
at com.sun.xml.ws.api.pipe.helper.AbstractTubeImpl.process(AbstractTubeImpl.java:112)
at com.sun.enterprise.security.webservices.CommonServerSecurityPipe.processRequest(CommonServerSecurityPipe.java:195)
at com.sun.enterprise.security.webservices.CommonServerSecurityPipe.process(CommonServerSecurityPipe.java:127)
at com.sun.xml.ws.api.pipe.helper.PipeAdapter.processRequest(PipeAdapter.java:115)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:629)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:588)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:573)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:470)
at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:295)
at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:519)
at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:288)
at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:143)
at org.glassfish.webservices.JAXWSServlet.doPost(JAXWSServlet.java:149)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
at java.lang.Thread.run(Thread.java:722)


Segue abaixo meu codigo:

@WebMethod(operationName = "consultarUsuario")
    public String consultarUsuario(@WebParam(name = "matricula") String matricula) {

       

        String selectStatement = "select STATUS, funcCriptografa(SENHA) SENHA, PRIMEIRO_ACESSO from USUARIO where UPPER(MATRICULA) = '" + matricula + "'";
        Connection conn = null;
        ResultSet rs = null;
        String resposta = "NAO_CADASTRADO";
        
        try {
            Conexao connJndi = new Conexao();
            conn = connJndi.getConexao();
            rs = conn.createStatement().executeQuery(selectStatement);
            if (rs.next()) {
                if (rs.getString("STATUS").equalsIgnoreCase("1")) {
                    resposta = "LIBERADO";
                } else if (rs.getString("STATUS").equalsIgnoreCase("2")) {
                    resposta = "BLOQUEADO";
                } else if (rs.getString("STATUS").equalsIgnoreCase("3")) {
                    resposta = "EXCLUIDO";
                } else {
                    resposta = "NAO_CADASTRADO";
                }
            } else {
                resposta = "NAO_CADASTRADO";
            }
            GeraLog.logInfo("[CONSULTAR_USUARIO] Consulta executada com sucesso para o usuario " + matricula);
            return resposta;
        } catch (SQLException ex) {
            GeraLog.logError("[CONSULTAR_USUARIO] Erro ao consultar o usuario " + matricula + " SQLException Mensagem: " + ex.getMessage());
            ex.printStackTrace();
            return "ERRO_DB";
        } catch (Exception ex) {
            GeraLog.logError("[CONSULTAR_USUARIO] Erro ao consultar o usuario " + matricula + " Mensagem: " + ex.getMessage());
            ex.printStackTrace();
            return "ERRO_DB";
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
             } catch (SQLException ex) {
                GeraLog.logError("[CONSULTAR_USUARIO] Erro ao fechar o ResultSet " + ex.getMessage());
            }
                        
            
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                GeraLog.logError("[CONSULTAR_USUARIO] Erro ao fechar a conexao " + ex.getMessage());
            }


        }

    }


public class Conexao {

    public Connection getConexao() throws Exception {
      
        InitialContext context = new InitialContext();
        DataSource ds = (DataSource) context.lookup("jdbc/AuthSystem");
                
        try {
            return ds.getConnection();
        } catch (Exception e) {
            GeraLog.logInfo("[GET_CONEXAO] Erro ao obter conexão com o banco de dados " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }
}


Configuraçãoes de 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

String de conexão para acesso ao banco de dados 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))

Monitoria do servidor de aplicação do pool de conexões:

[img]http://arquivo.devmedia.com.br/forum/imagem/216219-20160219-163505.png[/img]
Responder
×
+1 DevUP
Acesso diário, +1 DevUP
Parabéns, você está investindo na sua carreira