Home > Articles > Programming

  • Print
  • + Share This
  • 💬 Discuss

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.

Starving a Pool

When using connection pooling, it is important to remember that a chunk of bad code that neglects to return connections can starve the rest of the application, causing it to eventually run out of connections and hang (potentially failing nowhere near the actual problem). To test for this, set the maximum connections in your pool to a small number (as low as 1), and use tools like p6spy and IronTrack SQL (described above) to look for statements that fail to close.

This problem can be avoided by always using a finally block to close your connection, as shown throughout this book.

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


Statement Cache

Certain connection pools, drivers, databases, and other portions of the system may provide an additional cache system, known as a statement cache. This cache stores a partially compiled version of a statement in order to increase performance. By reusing the parsed or precompiled statement, the application is able to trade an increase in memory usage for a boost in performance.

You should consider using a statement cache if one is available, but keep in mind that a statement cache is not the same thing as the other forms of caching described later in this chapter.

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.

Listing 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

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.max Wait

-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.test OnBorrow

true |false

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

hibernate.dbcp.test OnReturn

true |false

Query to execute for connection validation (optional, requires either hibernate.dbcp.testOn Borrow or hibernate.dbcp.testOnReturn)

hibernate.dbcp.validationQuery

Valid SQL 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

Discussions

comments powered by Disqus