Configure connection Pool Hibernate

Configure connection Pool Hibernate

By definition connection pool is cache of database maintained connections where connections could be reused whenever future requests to database will be required. It is used to enhance performance of executing commands to the database. Every time on each request opening and maintain database connection for every single uses especially when requests made as dynamic database driven website is very costly and wastes of resources.

In connection pool after connection is created it is placed in pool so that it can be used over and over again in a way that new connection doesn’t have to be created every time. If at some point of time all connections are in use then new connection will be made and added to pool. It also cuts down amount of time user wait to establish connection to the database.

When hibernate connects to the database in your application it could connect using variety of mechanism which is below:

• javax.sql.DataSource
• Stand-alone built-in connection pool
• Connection pools, including support for two different third-party opensource JDBC connection pools:
  • c3p0
  • proxool
• Application-supplied JDBC connections. This is not a recommended approach and exists for legacy reasons

Note: According to hibernate documentation it suggests built-in connection pool is not good to use in production environment.

This demo will discuss about how to Configure connection Pool Hibernate using third party library c3p0 which is an easy use compare to augmenting traditional i.e. (DriverManager based) JDBC drivers with JNDI bind DataSources:

• To use c3p0 you will have to add below dependency in your pom.xml file

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>4.3.5.Final</version>
</dependency>

• For eclipse project please add hibernate-c3p0.xxx.jar in your class path.

• Configure connection pooling in hibernate.cfg.xml file as below:

<?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 name="sessionFactory">
        <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">admin</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/javahonk</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.search.autoregister_listeners">false</property>

        <property name="hibernate.c3p0.min_size">10</property>
        <property name="hibernate.c3p0.max_size">25</property>
        <property name="hibernate.c3p0.timeout">600</property>
        <property name="hibernate.c3p0.max_statements">40</property>        

        <property name="show_sql">true</property>
        <mapping class="com.javahonk.bean.Mother" />
        <mapping class="com.javahonk.bean.Child" />
    </session-factory>
    
</hibernate-configuration>

• To run and check connection status you could use below command in MySQL database:

mysql> show processlist

mysql> show status like ‘Conn%’;

mysql> show status like ‘%onn%’

For more information please read this official hibernate tutorial