Srs, pelo que vi o meu c3p0 não está configurado corretamente, pois ao abrir o meu sistema, jsf + richfaces + jpa + mysql eu acompanho os processos do mysql no administrador e a medida que eu logo novamente ele nunca encerra as conexões antiga ele sempre cria novas. O número de conexões novas é dado pelo min_size configurado no c3p0 do persistence no caso 2, ou seja, sempre que me logo ele adiciona 2 novas conexões e por ai nunca obedecendo o limite de 20 que eu coloquei. Ai sempre ao alcançar 100 conexões o mysql para.
Seguem os arquivo abaixo para verificação.
Valeu pela ajuda!
persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="crateus" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<mapping-file>META-INF/consultas.xml</mapping-file>
<properties>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
<property name="hibernate.connection.username" value="crateus" />
<property name="hibernate.connection.password" value="123456" />
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/crateus" />
<property name="hibernate.show_sql" value="false" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
<property name="hibernate.connection.autoReconnect" value="true" />
<property name="hibernate.connection.autoReconnectForPools" value="true"/>
<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
<property name="hibernate.c3p0.min_size" value="2"/>
<property name="hibernate.c3p0.min_size" value="2"/>
<property name="hibernate.c3p0.timeout" value="100"/>
<property name="hibernate.c3p0.idle_test_period" value="150"/>
</properties>
</persistence-unit>
</persistence>
arquivo: log4j.properties - esse log4j eu peguei da net, não sei como é o funcionamento dele!
log4j.logger.com.mchange=DEBUG, STDOUT
### direct log messages to stdout ###
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.Target=System.out
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=%t %d{dd/MMM/yyyy:H:mm:ssZ} %5p %c{1}:%L - %m%n
log do console:
08/02/2012 10:37:03 org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jre6\bin;.;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files (x86)\PC Connectivity Solution\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Dell\DW WLAN Card;C:\Program Files\WIDCOMM\Bluetooth Software\;C:\Program Files\WIDCOMM\Bluetooth Software\syswow64;
08/02/2012 10:37:03 org.apache.tomcat.util.digester.SetPropertiesRule begin
AVISO: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:prefeitura' did not find a matching property.
08/02/2012 10:37:03 org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
08/02/2012 10:37:03 org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 521 ms
08/02/2012 10:37:03 org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
08/02/2012 10:37:03 org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.26
log4j:WARN No appenders could be found for logger (org.apache.myfaces.webapp.StartupServletContextListener).
log4j:WARN Please initialize the log4j system properly.
08/02/2012 10:37:07 org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
08/02/2012 10:37:07 org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
08/02/2012 10:37:07 org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/30 config=null
08/02/2012 10:37:07 org.apache.catalina.startup.Catalina start
INFO: Server startup in 3403 ms
Classe usuarioDAO
package br.com.azurium.prefeitura.infra.banco;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import br.com.azurium.prefeitura.comum.entidade.Usuario;
import br.com.azurium.prefeitura.comum.excecao.InfraException;
public class UsuarioDAO extends DAO<Usuario> {
public UsuarioDAO() {
super(Usuario.class);
}
public Usuario autenticacao(Usuario usuario) throws InfraException {
EntityManager gerente = null;
Usuario u = new Usuario();
try {
gerente = fabrica.createEntityManager();
String jpql = "select u from Usuario u where u.login = '" + usuario.getLogin()
+ "' and u.senha = '" + usuario.getSenha().trim()
+ "' and u.status = 'ATIVO'";
Query consulta = gerente.createQuery(jpql);
u = (Usuario) consulta.getSingleResult();
return u;
} catch (Exception e) {
throw new InfraException(e);
} finally {
if (gerente != null && gerente.isOpen()) {
gerente.close();
}
}
}
public Usuario verificaLogin(String login) throws InfraException {
EntityManager gerente = null;
try {
gerente = fabrica.createEntityManager();
String jpql = "select u from Usuario u where u.login = '" + login
+ "'";
Query consulta = gerente.createQuery(jpql);
consulta.setMaxResults(1);
return (Usuario) consulta.getSingleResult();
} catch (Exception e) {
return null;
} finally {
if (gerente != null && gerente.isOpen()) {
gerente.close();
}
}
}
}
