melbourne chapter

Connection pools are a smart way of maintaining connections so that they can be reused when the database receives future requests for data. Reading through the Hibernate documentation, Hibernate explains that the connection pooling algorithm used as default by Hibernate is not adequate for production, and recommends the c3p0 open source database connection pool’er.

Hibernate’s own connection pooling algorithm is however quite rudimentary. It is intended to help you get started and is not intended for use in a production system or even for performance testing. You should use a third party pool for best performance and stability. Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate’s internal pool. For example, you might like to use C3P0.

You can find the c3p0 project on Sourceforge from this URL: http://sourceforge.net/projects/c3p0

C3P0 is an open source JDBC connection pool distributed along with Hibernate in the lib directory. Hibernate will use its C3P0ConnectionProvider for connection pooling if you set hibernate.c3p0.* properties. If you’d like to use Proxool refer to the packaged hibernate.properties and the Hibernate web site for more information.

c3p0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers with JNDI-bindable DataSources, including DataSources that implement Connection and Statement Pooling, as described by the jdbc3 spec and jdbc2 std extension.

You can read about Connection Pooling on wikipedia, or read this documentation on how to configure hibernate to use c3p0.

As a note to PHP users, PHP creator Rasmus Lerdorf discusses about the lack of connection pooling in PHP (as well as all the other hard questions of PHP). People can check out SQLRelay as a way of helping implement connection pooling, but requires a server install.

SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, SQLite and MS Access (minimally) with APIs for C, C++, Perl, Perl-DBI, Python, Python-DB, Zope, PHP, Ruby, Ruby-DBI, Java and TCL, drop-in replacement libraries for MySQL and PostgreSQL clients, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client-side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access.