This chapter is from the book
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.
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 |
Distributed with Hibernate |
|
|
Apache DBCP |
Apache Pool |
|
|
Proxool |
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 |