Normally, opening a connection to a database consumes more time than executing an SQL statement. Connection pools can help to reuse some connections without creating the connections frequently. In DAO layer, it will help to improve your system performance.
Hibernate can use an application server’s built-in connection pool via JNDI datasource binding, or use c3p0, DBCP, Proxool etc. It is easy to provide connection pool support when using Hibernate. However, if something is not configured in a right way, you might receive the error like below frequently (assuming you are using MySQL database and its default wait_timeout = 8 hours).
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 39,601,006 milliseconds ago. The last packet sent successfully to the server was 39,601,006 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:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
If you run into the issue, one possible reason is the connection pool configuration. When a long connection was not refreshed by a pool (long idle time), and next time the application may still use the old connection. If the connection time is greater than MySQL wait_timeout value, exception may happen.
Here we will talk about how to configure c3p0 connection pool to avoid such kind of issue.
1. Add the right jars to your project
Assuming we are using Hibernate 4.3.8, please make sure c3p0-0.9.2.1.jar added. Another mistake may happen. Many people may forget adding hibernate-c3p0-4.3.8.Final.jar and mchange-commons-java-0.2.3.4.jar into project. If those two jars are missed, Hibernate will use its internal connection pool implementation which will cause the issue. If you don’t enable Hibernate log, you will not know if c3p0 is used. (Try it, and set hibernate log level to info, and you can see what happens)
2. Hibernate Configuration
Here it is a sample configuration file hibernate.cfg.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<!--?xml version="1.0" encoding="UTF-8"?--> <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Settings for a remote MySQL database. --> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3306/p2_roos?autoReconnect=true</property> <property name="connection.username">roos</property> <property name="connection.password">xxxxxxxxx</property> <!-- C3P0 setting connection.provider_class is optional. If it is not there, Hibernate will guess the value based on property prefix c3p0 --> <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> <property name="c3p0.min_size">3</property> <property name="c3p0.max_size">10</property> <property name="c3p0.timeout">1000</property> <property name="c3p0.idle_test_period">2000</property> <property name="c3p0.preferredTestQuery">select 1;</property> <property name="hibernate.connection.release_mode">after_transaction</property> <!-- Use EHCache for cache. If you don't use --> <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property> <property name="cache.provider_configuration_file_resource_path">/ehcache.xml</property> <property name="cache.use_query_cache">true</property> <property name="c ache.use_minimal_puts">false</property> <property name="max_fetch_depth">3</property> <property name="show_sql">false</property> <property name="use_sql_comments">true</property> <!-- Mapping files --> <mapping resource="com/test/domain/Account.hbm.xml" /> <mapping resource="com/test/domain/User.hbm.xml" /> </session-factory> </hibernate-configuration> |
c3p0.min_size: Hibernate default value is 1
c3p0.max_size: Hibernate default value is 100
c3p0.timeout:The default value in Hibernate is 0. The unit is second. The seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire [JBoss].
c3p0.idle_test_period: The default value in Hibernate is 0. If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds [JBoss].
Other parameters not in the sample:
c3p0.max_statements: default value is 0. It will tell us the size of c3p0’s PreparedStatement cache.
c3p0.acquire_increment: default value is 1. It will tell c3p0 how many connections at a time to acquire when the pool is exhausted.
More information of c3p0 can be found at c3p0 site.
Sample connections:
Thanks! It helps to solve the issue which bothered me in those days.