JavaDB Connection pool

From Wikipedia, the free encyclopedia

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.^{[citation needed]} Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

Applications

Web-based and enterprise applications use an application server to handle connection pooling. Dynamic web pages without connection pooling open connections to database services as required and close them when the page is done servicing a particular request. Pages that use connection pooling, on the other hand, maintain open connections in a pool. When the page requires access to the database, it simply uses an existing connection from the pool, and establishes a new connection only if no pooled connections are available. This reduces the overhead associated with connecting to the database to service individual requests.

Local applications that need frequent access to databases can also benefit from connection pooling. Open connections can be maintained in local applications that don't need to service separate remote requests like application servers, but implementations of connection pooling can become complicated. A number of available libraries implement connection pooling and related SQL query pooling, simplifying the implementation of connection pools in database-intensive applications.

Administrators can configure connection pools with restrictions on the numbers of minimum connections, maximum connections and idle connections to optimize the performance of pooling in specific problem contexts and in specific environments.

Database support

Connection pooling is supported by IBM DB2,^[1] Microsoft SQL Server,^[2] Oracle,^[3] MySQL,^[4] and PostgreSQL.^[5]

How to configure the C3P0 connection pool in Hibernate

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

Hibernate comes with internal connection pool, but not suitable for production use. In this tutorial, we show you how to integrate third party connection pool – C3P0, with Hibernate.

1. Get hibernate-c3p0.jar

To integrate c3p0 with Hibernate, you need hibernate-c3p0.jar, get it from JBoss repository.

File : pom.xml

<project ...>

 <repositories>
  <repository>
   <id>JBoss repository</id>
   <url>http://repository.jboss.org/nexus/content/groups/public/</url>
  </repository>
 </repositories>

 <dependencies>

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

  <!-- Hibernate c3p0 connection pool -->
  <dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-c3p0</artifactId>
   <version>3.6.3.Final</version>
  </dependency>

 </dependencies>
</project>

2. Configure c3p0 properties

To configure c3p0, puts the c3p0 configuration details in “hibernate.cfg.xml“, like this :

File : 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>
  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:MKYONG</property>
  <property name="hibernate.connection.username">mkyong</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
  <property name="hibernate.default_schema">MKYONG</property>
  <property name="show_sql">true</property>

  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.max_size">20</property>
  <property name="hibernate.c3p0.timeout">300</property>
  <property name="hibernate.c3p0.max_statements">50</property>
  <property name="hibernate.c3p0.idle_test_period">3000</property>

  <mapping class="com.mkyong.user.DBUser"></mapping>
</session-factory>
</hibernate-configuration>

1. hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
2. hibernate.c3p0.max_size – Maximum number of JDBC connections in the pool. Hibernate default: 100
3. hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.
4. hibernate.c3p0.max_statements – Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.
5. hibernate.c3p0.idle_test_period – idle time in seconds before a connection is automatically validated. Hibernate default: 0

Note
For detail about hibernate-c3p0 configuration settings, please read this article.

Understanding Java Database Connection Pooling Properties

Basic properties controlling pooling behaviour.

Property	Explanation
minpool	Minimum number of connections that should be held in the pool.
maxpool	Maximum number of connections that may be held in the pool.
maxsize	Maximum number of connections that can be created for use.
idleTimeout	The idle timeout for connections (seconds).

  
Bean properties supported by snaq.db.DBPoolDataSource (can also be specified via snaq.db.DBPoolDataSourceFactory).

Property	Description
name	Name of the DataSource, which is also used to assign a ConnectionPool name.
description	Description for the DataSource.
driverClassName	Fully-qualified class name of JDBC Driver to use.
url	JDBC URL to connect to the database.
user	Username for database connections.
password	Password for database connections.
passwordDecoderClassName	Fully-qualified class name of snaq.db.PasswordDecoder implementation to use. (It must have a public no-argument constructor).
minPool	Minimum number of pooled connections to maintain.
maxPool	Maximum number of pooled connections to maintain.
maxSize	Maximum number of connection that can be created.
idleTimeout	Idle timeout of pooled connections (seconds).
loginTimeout	Timeout for database connection attempts (seconds).
validatorClassName	Fully-qualified class name of snaq.db.ConnectionValidator implementation to use. (It must have a public no-argument constructor).
validatorQuery*	Query string to use for validation, if validatorClassName not specified. This is passed to a snaq.db.SimpleQueryValidator instance.

Hibernate-Supported Connection Pools

Table 10.1. Hibernate-Supported Connection Pools

c3p0	http://sourceforge.net/projects/c3p0	Distributed with Hibernate
Apache DBCP	http://jakarta.apache.org/commons/dbcp/	Apache Pool
Proxool	http://proxool.sourceforge.net/	JDBC Pooling Wrapper