Hibernate c3po Connection pooling

**What is the connection pool? **
Connection pooling is a technique to open or prepare or close connections. A connection pooling mechanism, which is managing connections. If our application requires a connection, then connection pooling providing the connection, once task is completed the connection return back to the pool. The responsible of connection pooling opening N number of connections and leave them ready for when your application required. If a connection is invalid because out of idle time, the pooling mechanism will close it and reopen a new one. This represents a better usage of connections.

** Benefits of Connection pooling **

The connection pool is good for performance, as it prevents Java application create a connection each time when interacting with the database and minimizes the cost of opening and closing connections.

Hibernate supports a variety of connection pooling mechanisms. Below are the Hibernate supported connection pools.

1. C3po.
2. ADBCP(Apache DBCP).
3. Proxool.

Here i am going to implement the connection pooling with c3po.

What is c3p0 ?

C3P0 is an open source connection pool which has a Hibernate package which you can add as a dependency to your project and you are ready to configure the pool.

Following is some sample code.

1) pom.xml
Below dependency isrequired to add in pom.xml

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

**2) Configure c3p0 propertise in hibernate.cfg.xml file **

<hibernate-configuration>
 <session-factory>

  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:analasi</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.connection.password">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle11gDialect</property>
  <property name="hibernate.default_schema">analasi</property>
  <property name="show_sql">true</property>

  <property name="hibernate.c3p0.min_size">10</property>
  <property name="hibernate.c3p0.max_size">50</property>
  <property name="hibernate.c3p0.timeout">500</property>
  <property name="hibernate.c3p0.max_statements">100</property>
  <property name="hibernate.c3p0.idle_test_period">3000</property>
  <property name="hibernate.c3p0.acquireRetryAttempts">3</property>
  <property name="hibernate.c3p0.acquireRetryDelay">200</property>

  <mapping class="com.main.Employee"></mapping>

</session-factory>
</hibernate-configuration>

1. hibernate.c3p0.min_size – The minimum number of connections in the pool. Hibernate default no of connections: 1
2. hibernate.c3p0.max_size – The maximum number of connections in the pool. Hibernate default no of connections: 100
3. hibernate.c3p0.timeout –Idle connection is removed from the pool (in second). Hibernate default: 0, it means the connection never expires.
4. hibernate.c3p0.max_statements – The number of prepared statements will be cached. Increase performance. Hibernate default: value is 0, it means caching is disabled.
5. hibernate.c3p0.idle_test_period – idle time in seconds before a connection is automatically validated. Hibernate default value is: 0
6. hibernate.c3p0.acquireRetryAttempts: Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to 0, c3p0 will keep trying to fetch a Connection indefinitely.
7. hibernate.c3p0.acquireRetryDelay: Milliseconds, time c3p0 will wait between acquiring attempts.

3) Employee.java

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "employee")

public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column
    private String name;
    @column
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name){
        this.name = name;
    }

      public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

4) ConnectionPoolTest.java

import org.hibernate.Transaction;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ConnectionPoolTest{

    public static void main (String args[]){

        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        Session session = sessionFactory .getSession();
        Transaction tx = session.beginTransaction();

        Employee  employee = new Employee();
        employee .setId(10);
        employee .setName("John");
        employee .getAge(25);
     
        session.save(employee);
        tx.commit();
        session.clear();
        session.close();

       
    }
}

** Note **
Hibernate comes with the internal connection pool, it is not suitable for production use, so we should implement hibernate supported connection pooling for production.