Boa tarde pessoal.
Estamos com um sistema entrando em homologação e estou tendo problemas sérios com o Poll de conexões.
A aplicação utiliza JDBC e meu padão de método que acessa banco, é mais ou menos assim
@Override
public boolean cadastrar(ClienteBean cliente) throws DaoException {
if(cliente == null)
return false;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rst = null ;
String comando =
"DECLARE @ClienteID INT " +
"EXEC dbo.SRSCadastroCliente " +
"@ProcVendedorID=? ," +
"@ProcCliente=? ," +
"@ProcNascimento=? ," +
"@ProcCPF=? ," +
"@ProcRG=? ," +
"@ProcRGData=? ," +
"@ProcSexo=? ," +
"@ProcEstadoCivilID=? ," +
"@ProcNomePai=? ," +
"@ProcNomeMae=? ," +
"@ProcNaturalidade=? ," +
"@ProcCep=? ," +
"@ProcTipoLogradouro=? ," +
"@ProcLogradouro=? ," +
"@ProcNumero=? ," +
"@ProcComplemento=? ," +
"@ProcBairro=? ," +
"@ProcCidade=? ," +
"@ProcUF=? ," +
"@ProcFone=? ," +
"@ProcCelular=? ," +
"@ProcFoneRecado=? ," +
"@ProcNomeRecado=? ," +
"@ProcBancoID=? ," +
"@ProcFormaPagamentoID=? ," +
"@ProcAgencia=? ," +
"@ProcTipoContaID=? ," +
"@ProcConta=? ," +
"@ClienteID=@ClienteID OUTPUT " +
"SELECT @ClienteID AS ClienteID";
try {
connection = daoFactory.getConnection();
preparedStatement = connection.prepareStatement(comando);
preparedStatement.setInt(1, new Integer(cliente.getPromotor().getId()));
preparedStatement.setString(2, cliente.getNome());
preparedStatement.setDate(3, new java.sql.Date(cliente.getDataNascimento().getTime()));
preparedStatement.setString(4, cliente.getCpf().replace(".", "").replace("-", ""));
preparedStatement.setString(5, cliente.getRg());
preparedStatement.setDate(6, new java.sql.Date(cliente.getDataEmissao().getTime()));
preparedStatement.setString(7, cliente.getSexo());
preparedStatement.setInt(8, cliente.getEstadoCivil());
preparedStatement.setString(9, cliente.getNomeDoPai());
preparedStatement.setString(10, cliente.getNomeDaMae());
preparedStatement.setString(11, cliente.getNaturalidade());
preparedStatement.setString(12, cliente.getEndereco().getCep());
preparedStatement.setString(13, cliente.getEndereco().getTipoLogradouro());
preparedStatement.setString(14, cliente.getEndereco().getLogradouro());
preparedStatement.setString(15, cliente.getEndereco().getNumero());
preparedStatement.setString(16, cliente.getEndereco().getComplemento());
preparedStatement.setString(17, cliente.getEndereco().getBairro());
preparedStatement.setString(18, cliente.getEndereco().getCidade());
preparedStatement.setString(19, cliente.getEndereco().getEstado());
preparedStatement.setString(20, cliente.getTelefoneFixo());
preparedStatement.setString(21, cliente.getTelefoneCelular());
preparedStatement.setString(22, cliente.getTelefoneRecado());
preparedStatement.setString(23, cliente.getNomeRecado());
preparedStatement.setInt(24, cliente.getBanco().getId());
preparedStatement.setInt(25, cliente.getFormaPgto());
preparedStatement.setString(26, cliente.getAgencia());
preparedStatement.setInt(27, cliente.getTpConta());
preparedStatement.setString(28, cliente.getConta());
rst = preparedStatement.executeQuery();
if(rst.next()) {
int clienteID = rst.getInt("ClienteID");
if(clienteID > 0)
return true;
}
return false;
} catch (DaoException e) {
throw new DaoException(e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
throw new DaoException("Ocorreu um erro com no banco de dados. ");
}finally {
try {
if(rst!=null && !rst.isClosed()){
rst.close();
}
if(preparedStatement!=null && !preparedStatement.isClosed()){
preparedStatement.close();
}
if(connection!=null && !connection.isClosed()){
connection.close();
}
daoFactory.closeContext();
} catch (SQLException e) {
throw new DaoException("Ocorreu um erro com no banco de dados: Erro ao fechar conexão. ");
} catch (NullPointerException e) {
throw new DaoException("Conexão era nula ao ser fechada.");
}catch (Exception e2) {
e2.printStackTrace();
throw new DaoException("Erro ao fechar conexão. ");
}
}
}
Após algum tempo de uso, a exceção abaixo acontece e o sistema trava.
Advertência: RAR5117 : Failed to obtain/create connection from connection pool [ new_ficsa ]. Reason : com.sun.appserv.connectors.internal.api.PoolingException: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.
Advertência: RAR5114 : Error allocating connection :
[Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.]
Grave: bd.exception.DaoException: Sem conexão com o banco de dados.
at bd.DaoFactory.getConnection(DaoFactory.java:50)
at controller.ClienteController.getPorId(ClienteController.java:289)
at controller.PropostaController.getPorId(PropostaController.java:594)
at bean.FormPropostaBean.carregaEdicao(FormPropostaBean.java:130)
at bean.FormPropostaBean.linhaSelecionada(FormPropostaBean.java:170)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at org.primefaces.component.behavior.ajax.AjaxBehaviorListenerImpl.processAjaxBehavior(AjaxBehaviorListenerImpl.java:42)
at org.primefaces.event.SelectEvent.processListener(SelectEvent.java:40)
at javax.faces.component.behavior.BehaviorBase.broadcast(BehaviorBase.java:102)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:760)
at javax.faces.component.UIData.broadcast(UIData.java:1071)
at javax.faces.component.UIData.broadcast(UIData.java:1093)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at com.ocpsoft.pretty.PrettyFilter.doFilter(PrettyFilter.java:126)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at org.apache.catalina.core.ApplicationDispatcher.doInvoke(ApplicationDispatcher.java:785)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:649)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:483)
at org.apache.catalina.core.ApplicationDispatcher.doDispatch(ApplicationDispatcher.java:454)
at org.apache.catalina.core.ApplicationDispatcher.dispatch(ApplicationDispatcher.java:350)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:300)
at com.ocpsoft.pretty.PrettyFilter.doFilter(PrettyFilter.java:118)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:330)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:174)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:828)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:725)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1019)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Thread.java:662)
Agradeço quem puder ajudar !