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.
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>- hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
- hibernate.c3p0.max_size – Maximum number of JDBC connections in the pool. Hibernate default: 100
- hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.
- hibernate.c3p0.max_statements – Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.
- 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.
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
|
Distributed with Hibernate
| |
Apache DBCP
|
Apache Pool
| |
Proxool
|
JDBC Pooling Wrapper
|
