java.sql.SQLException: Closed Connection

19/02/2016

0

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]
Felipe Donatti

Felipe Donatti

Responder

Que tal ter acesso a um e-book gratuito que vai te ajudar muito nesse momento decisivo?

Ver ebook

Recomendado pra quem ainda não iniciou o estudos.

Eu quero
Ver ebook

Recomendado para quem está passando por dificuldades nessa etapa inicial

Eu quero

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar