Saturday, June 26, 2010

Hibernate Connection Pooling: why isn't the default one for production?

Hibernate unlike NHibernate comes with a variety of connection pooling options. The three primary ones of which I'm aware are Proxool, Apache DBCP, and c3p0 . I myself have only so far used c3p0 and it works quite well having saved me from a couple of jams so far where the default one was in use previously. First why not use the default one? Because the official Hibernate documentation says in a big yellow box marked caution the following words:

The built-in Hibernate connection pool is in no way intended for production use. It lacks several features found on any decent connection pool.

Yet on almost every single Java project I've worked on the default is in use. I wouldn't care so much but this has personally wasted a fair amount of time for me and I've had to approach this as an outsider every time getting into the argument "it hasn't cause any issues before". Yet usually within a couple of days of that discussion I or someone else has resolved a "well it just does that sometimes" problem just by switching the connection pool. However, in implementing an alternate connection pool I should share a couple of gotcha's

1) Turn on logging for Hibernate and read the logs and make sure your connection pool of choice is actually activated and not the default one.

2) Older examples of the documentation I found for c3p0 did not include the necessary line for the C3P0Connection provider. If this line is not included in version 3.x version of hibernate regardless of what else is configured hibernate will use the default connection pool.

3) If the class you specified for connection provider line is not found for some reason, also the default connection pool will be used. Depending on your version of hibernate you may have to reference a different jar.

Below is the config borrowed from the hibernate connection tutorial only with c3p0 configured, please borrow it instead of using the default one.

<div class="line" id="LC1"><span class="cp"><?xml version='1.0' encoding='utf-8'?></span></div><div class="line" id="LC2"><span class="cp"><!DOCTYPE hibernate-configuration PUBLIC</span></div><div class="line" id="LC3"><span class="cp">        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"</span></div><div class="line" id="LC4"><span class="cp">        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"></span></div><div class="line" id="LC5"><br></div><div class="line" id="LC6"><span class="nt"><hibernate-configuration></span></div><div class="line" id="LC7"><br></div><div class="line" id="LC8">    <span class="nt"><session-factory></span></div><div class="line" id="LC9"><br></div><div class="line" id="LC10">        <span class="c"><!-- Database connection settings --></span></div><div class="line" id="LC11">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"connection.driver_class"</span><span class="nt">></span>org.hsqldb.jdbcDriver<span class="nt"></property></span></div><div class="line" id="LC12">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"connection.url"</span><span class="nt">></span>jdbc:hsqldb:hsql://localhost<span class="nt"></property></span></div><div class="line" id="LC13">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"connection.username"</span><span class="nt">></span>sa<span class="nt"></property></span></div><div class="line" id="LC14">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"connection.password"</span><span class="nt">></property></span></div><div class="line" id="LC15">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"connection.provider_class"</span><span class="nt">></span>org.hibernate.connection.C3P0ConnectionProvider<span class="nt"></property></span> <span class="c"><!-- NECESSARY TO USE C3P0 --></span></div><div class="line" id="LC16">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.acquire_increment"</span><span class="nt">></span>1<span class="nt"></property></span> </div><div class="line" id="LC17">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.idle_test_period"</span><span class="nt">></span>100<span class="nt"></property></span> <span class="c"><!-- seconds --></span> </div><div class="line" id="LC18">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.max_size"</span><span class="nt">></span>100<span class="nt"></property></span> </div><div class="line" id="LC19">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.max_statements"</span><span class="nt">></span>0<span class="nt"></property></span> </div><div class="line" id="LC20">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.min_size"</span><span class="nt">></span>10<span class="nt"></property></span> </div><div class="line" id="LC21">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"c3p0.timeout"</span><span class="nt">></span>100<span class="nt"></property></span> <span class="c"><!-- seconds --></span> </div><div class="line" id="LC22">        <span class="c"><!-- SQL dialect --></span></div><div class="line" id="LC23">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"dialect"</span><span class="nt">></span>org.hibernate.dialect.HSQLDialect<span class="nt"></property></span></div><div class="line" id="LC24"><br></div><div class="line" id="LC25">        <span class="c"><!-- Enable Hibernate's automatic session context management --></span></div><div class="line" id="LC26">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"current_session_context_class"</span><span class="nt">></span>thread<span class="nt"></property></span></div><div class="line" id="LC27"><br></div><div class="line" id="LC28">        <span class="c"><!-- Disable the second-level cache  --></span></div><div class="line" id="LC29">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"cache.provider_class"</span><span class="nt">></span>org.hibernate.cache.NoCacheProvider<span class="nt"></property></span></div><div class="line" id="LC30"><br></div><div class="line" id="LC31">        <span class="c"><!-- Echo all executed SQL to stdout --></span></div><div class="line" id="LC32">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"show_sql"</span><span class="nt">></span>true<span class="nt"></property></span></div><div class="line" id="LC33"><br></div><div class="line" id="LC34">        <span class="c"><!-- Drop and re-create the database schema on startup --></span></div><div class="line" id="LC35">        <span class="nt"><property</span> <span class="na">name=</span><span class="s">"hbm2ddl.auto"</span><span class="nt">></span>update<span class="nt"></property></span></div><div class="line" id="LC36"><br></div><div class="line" id="LC37">        <span class="nt"><mapping</span> <span class="na">resource=</span><span class="s">"org/hibernate/tutorial/domain/Event.hbm.xml"</span><span class="nt">/></span></div><div class="line" id="LC38"><br></div><div class="line" id="LC39">    <span class="nt"></session-factory></span></div><div class="line" id="LC40"><br></div><div class="line" id="LC41"><span class="nt"></hibernate-configuration></span></div>

No comments: