Search My Site:

MySQL 5, Hibernate 3, Connection timeouts - Last Modified 2009-04-06 03:15 UTC - Created 2009-04-06 03:15 UTC

I was getting a lot of connection timeouts with MySQL and Hibernate. I played with my hibernate.cfg.xml until I finally got it to use C3P0 (c THREE p ZERO) for managing the database connections. It wasn't easy to find all the necessary documentation on how to get Hibernate to not use its own internal database pool connector versus an external one.

Add the following to your Maven dependencies:

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

I used the following as my hibernate.cfg.xml:

<?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>

        <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <!-- do NOT use autoReconnect, change your pool settings instead! -->
        <property name="hibernate.connection.url">jdbc:mysql://localhost/MyDatabaseName</property>
        <property name="hibernate.connection.username">MyUsername</property>
        <property name="hibernate.connection.password">good password</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>

        <!-- update means update schema if needed, preserve data -->
        <property name="hbm2ddl.auto">update</property>
        
        <!-- Connection pool manager (avoids timeouts) -->
        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">60</property>
        <property name="hibernate.c3p0.max_size">5</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.timeout">120</property>

        <mapping resource="com/some/package/hibernate/SomeMapping.xml" />

    </session-factory>

</hibernate-configuration>