• Home
  • Blog
  • Connection pooling with Hibernate 3.3.x and C3P0

Connection pooling with Hibernate 3.3.x and C3P0

December 26, 2010

Hibernate uses it’s own built-in connection pool out of the box. There are some pretty heavy duty sites running with the default connection pool enabled in production despite the obvious warning against doing this.

INFO DriverManagerConnectionProvider:64 - Using Hibernate built-in connection pool (not for production use!)
INFO DriverManagerConnectionProvider:65 - Hibernate connection pool size: 1

A few minor problems creep up with this default configuration left alone, such as the inability for Hibernate to reconnect closed connections. MySQL closes unused connections after 8 hours by default, which causes problems for low-volume applications that may not experience any database usage overnight.

One solution is to use C3P0, an incredibly simple library that augments the standard JDBC drivers essentially making them “enterprise-ready”. One of the main features of C3P0 is:

Transparent pooling of Connection and PreparedStatements behind DataSources which can “wrap” around traditional drivers or arbitrary unpooled DataSources.

In other words, you don’t have to change any of your code to use C3P0 connection pools if you’re using Hibernate. C3P0 takes care of maintaining the pool and testing connections, so your code stays in tact.

Configuring C3P0

Add the C3P0 jar to your classpath. If you’re using Maven, simply add the following to your pom.xml file:

<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-c3p0</artifactId>
	<version>3.3.2.GA</version>
</dependency>

Make the following changes to your hibernate.cfg.xml file. Pay special attention to the provider_class property. Previous versions did not require this property, so most of the tutorials on the net are missing this key piece of info.

<property name="connection.url">jdbc:mysql://127.0.0.1/your_db</property>
<property name="connection.username">your_username</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

<!-- JDBC connection pool (C3P0) -->
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>

In order to configure C3P0 for your specific needs, you’ll need a c3p0.properties file on the classpath. Take a look here for all the available settings you can tweak. Here’s an example that ensures any closed connection is tested and re-opened rather than MySQL throwing an exception:

c3p0.testConnectionOnCheckout=true

Verifying it worked

Configuring C3P0 is pretty simple! If everything works correctly, you’ll see the following in your logs:

INFO ConnectionProviderFactory:95 - Initializing connection provider: org.hibernate.connection.C3P0ConnectionProvider
INFO C3P0ConnectionProvider:103 - C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://127.0.0.1/your_db
INFO C3P0ConnectionProvider:104 - Connection properties: {user=****, password=****}
 

Interested in meeting?

I organize a few local groups for programmers in the Toronto area. If you'd like to meet in person, feel free to come out to one of these events.

Interested in chatting?

I'm located in Toronto, Canada. If you're interested in learning more about anything I've published and would like to meet via Skype or have a coffee in Toronto, feel free to contact me at one of the links below.

Email

LinkedIn

Twitter