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)
`@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 = "<status>NAO_CADASTRADO</status><senha></senha><primeiro_acesso></primeiro_acesso>";
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))