Home > Articles > Programming > Java

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Connection Pooling

Opening a connection to a database is generally much more expensive than executing an SQL statement. A connection pool is used to minimize the number of connections opened between application and database. It serves as a librarian, checking out connections to application code as needed. Much like a library, your application code needs to be strict about returning connections to the pool when complete, for if it does not do so, your application will run out of available connections.

Hibernate supports a variety of connection pooling mechanisms. If you are using an application server, you may wish to use the built-in pool (typically a connection is obtaining using JNDI). If you can't or don't wish to use your application server's built-in connection pool, Hibernate supports several other connection pools, as shown in Table 10.1.

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

The choice of a connection pool is up to you, but be sure to remember that a connection pool is necessary for every production use.

If you wish to use c3p0, the version distributed with Hibernate 2.1.2 (0.8.3) is out of date (and GPL is a problem if you wish to distribute a non-GPL application). If you wish to distribute an application that makes use of c3p0, make sure to download the latest (LGPL) release, c3p0-0.8.4-test1 or later.

Because Hibernate ships with c3p0, configuration is a simple matter of adding a few Hibernate configuration properties to your hibernate.properties (or hibernate.cfg.xml) file. Listing 10.2 shows an example of the configuration of c3p0.

Example 10.2. Sample Hibernate c3p0 Configuration

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost/hibernate
hibernate.connection.username=root
hibernate.connection.password=
hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect
hibernate.show_sql=false

hibernate.c3p0.max_size=1
hibernate.c3p0.min_size=0
hibernate.c3p0.timeout=5000
hibernate.c3p0.max_statements=100
hibernate.c3p0.idle_test_period=300
hibernate.c3p0.acquire_increment=2
hibernate.c3p0.validate=false

The properties shown in Listing 10.2 are as described in Table 10.2.

Table 10.2. c3p0 Configuration Options

Property Meaning

Property

Example

Maximum number
of database
connections to open

hibernate.c3p0.
max_size

15

Initial number
of database
connections

hibernate.c3p0.
min_size

3

Maximum idle time
for a connection
(in seconds)

hibernate.c3p0.
timeout

5000

Maximum size
of c3p0 statement
cache (0 to turn off)

hibernate.c3p0.
max_statements

0

Number of connections
in a clump acquired
when pool is exhausted

hibernate.c3p0.
acquire_increment

3

Idle time before a
c3p0 pooled
connection is
validated (in seconds)

hibernate.c3p0.
idle_test_period

300

Validate the
connection on
checkout.
Recommend setting
the hibernate.c3p0.
idle_test
_period
property
instead. Defaults to
false

hibernate.c3p0
.validate

true | false

If you prefer to use Apache DBCP, make sure that the Apache DBCP library is on your class path, and add the properties to your hibernate.properties file, as shown in Table 10.3.

Table 10.3. Apache DBCP Configuration Options

Property Meaning

Property

Example

Maximum number
of checked-out
database
connections

hibernate.
dbcp.maxActive

8

Maximum number
of idle database
connections for
connection pool

hibernate.
dbcp.maxIdle

8

Maximum idle
time for connections
in connection pool
(expressed in ms).

hibernate.
dbcp.maxWait

-1

Set to -1 to turn off

   

Action to take in case
of an exhausted DBCP
connection pool. Set to
0 to fail, 1 to block until
a connection is made
available, or 2 to grow)

hibernate.dbcp.
whenExhaustedAction

1

Validate connection
when borrowing
connection from pool
(defaults to true)

hibernate.dbcp.
testOnBorrow

true | false

Validate connection
when returning
connection to pool
(optional, true,
or false)

hibernate.dbcp.
testOnReturn

true | false

Query to execute
for connection
hibernate.
dbcp.testOn
Borrow
or
hibernate.dbcp
.testOnReturn
)

hibernate.dbcp.
validationQuery

Valid SQL validation
(optional, requires either
SELECT statement
(e.g., SELECT 1+1)

Maximum number
of checked-out
statements

hibernate.dbcp.
ps.maxActive

8

Maximum number
of idle statements

hibernate.dbcp.
ps.maxIdle

8

Maximum idle time
for statements (in ms)

hibernate.dbcp.
ps.maxWait

1000 * 60 * 30

Action to take in case
of an exhausted
statement pool. Set to
0 to fail, 1 to block
until a statement is
made available,
or 2 to grow)

hibernate.dbcp. ps.
whenExhaustedAction

1

Finally, if you wish to use Proxool as your connection pool provider, you will need to specify hibernate.properties values as shown in Table 10.4. Unlike c3p0 and DBCP, you will need to include additional configuration options as described at http://proxool.sourceforge.net/configure.html.

Table 10.4. Proxool Configuration Options

Property Meaning

Property

Example

Configure Proxool
provider using an
XML file

hibernate.
proxool.xml

/path/to/
file.xml

Configure the
Proxool provider
using a
properties file
.properties

hibernate.
proxool.
properties

/path/to/
proxool

Configure the
Proxool provider
from an
existing pool

hibernate.
proxool
.existing_pool

true |
false

Proxool pool alias
to use (required for
hibernate.proxool
.existing_pool, hibernate.proxool
.properties,
hibernate
.proxool.xml
)

hibernate.
proxool.
pool_alias

As set by
Proxool
configuration

  • + Share This
  • 🔖 Save To Your Account