It is okay if you are in development phase. When deploy project using hibernate and mysql to server, you may get an error throw:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
Detailed
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1193) at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1180) at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4137) at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4103) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547) at org.hibernate.loader.Loader.doQuery(Loader.java:673) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 36 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 34,247,052 milliseconds ago. The last packet sent successfully to the server was 34,247,052 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1787) at org.hibernate.loader.Loader.doQuery(Loader.java:674) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) at com.xyz.abc.DAO.GenericHibernateDAO.findByField(GenericHibernateDAO.java:119) at com.xyz.abc.DAO.JobDAO.getJobsByLdap(JobDAO.java:115) at com.xyz.abc.business.Jcr.getMyruns(Jcr.java:272) at com.xyz.abc.business.abcService.getMyruns(abcService.java:54) at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:194) at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102) at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40) at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114) at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173) at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:173) at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237) ... 4 more Caused by: java.net.SocketException: Software caused connection abort: socket write error
To resolve this problem, you just set property autoReconnect = true
MySQL Connection URL
You can set value in the connection URL in parameters:
jdbc.url=jdbc:mysql://ipaddress:3306/database?autoReconnect=true&autoReconnectForPools=true
Hibernate Configuration File
Add the following attributes to file hibernate.cfg.xml
<property name="connection.autoReconnect">true</property> <property name="connection.autoReconnectForPools">true</property> <property name="connection.is-connection-validation-required">true</property>
C3P0 Connection Pool
In our project, it is configured as follows
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.bytecode.use_reflection_optimizer">false</property> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.username">test</property> <property name="hibernate.connection.password">test</property> <property name="hibernate.connection.url">jdbc:mysql://xmpp.nms.ssdc:3306/joomla?autoReconnect=true&autoReconnectForPools=true</property> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <!--Connection Pooling--> <property name="c3p0.validate">true</property> <property name="c3p0.min_size">10</property> <property name="c3p0.max_size">600</property> <property name="c3p0.timeout">1800</property> <!-- seconds --> <property name="c3p0.max_statements">50</property> <property name="c3p0.preferredTestQuery">SELECT 1;</property> <property name="c3p0.testConnectionOnCheckout">true</property> <property name="c3p0.idle_test_period">3000</property> <!-- seconds --> <property name="connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property> <property name="connection.autoReconnect">true</property> <property name="connection.autoReconnectForPools">true</property> <property name="connection.is-connection-validation-required">true</property> <property name="show_sql">true</property> <mapping resource="vn/vnpttech/ssdc/nms/xmpp/model/Rosterusers.hbm.xml"/> <mapping resource="vn/vnpttech/ssdc/nms/xmpp/model/Users.hbm.xml"/> </session-factory> </hibernate-configuration>
When deploy application we must include the following libraries:
Or, with maven dependency:
<!-- Connection Pooling --> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.1</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>5.0.0.CR1</version> <exclusions> <exclusion> <artifactId>c3p0</artifactId> <groupId>com.mchange</groupId> </exclusion> </exclusions> </dependency>
Reference Link
- https://stackoverflow.com/questions/3842232/something-wrong-with-hibernate-db-connection-pooler-c3p0
- https://stackoverflow.com/questions/270800/why-does-hibernate-jdbc-mysql-drop-connections-after-a-day-or-so
- http://www.databaseskill.com/288603/
- http://hibernatedb.blogspot.com/2009/05/automatic-reconnect-from-hibernate-to.html
- https://stackoverflow.com/questions/10695028/hibernate-mysql-connection-timeout
- https://stackoverflow.com/questions/7565143/com-mysql-jdbc-exceptions-jdbc4-mysqlnontransientconnectionexception-no-operation