How create an PostgreSQL connection pool using Java?

Question

I'm trying to use a Connection Pool, but I don't understand it right. Who implements it? The software, the driver or the database?

How I do to have my program running using a Connection Pool? I'm using the native PostgreSQL driver.

I need an code example. I'm doing an webcrawler and it has many connections to the database.

Answer 1

There are several possibilities:

• application server/servlet container may provide you with connection pool, see e.g. Tomcat 7 JNDI Datasource for PostgresQL.

• You might create connection pool manually using open source libraries like DBCP or C3P0.

• Finally your database JDBC driver may provide some built-in connection pool implementation, see PostgresQL Connection Pools and Data Sources and PGConnectionPoolDataSource (I don't know how recent and up-to-date these classes are).

No matter which option you choose, in principle it always works the same way: the client maintains a pool of network connections to the database. Every time you request new connection using DataSource, connection pool will peek free connection and give to you. When you think you are closing the connection, it will actually be released and put back into the pool. Other thread may now use the same, already established connection.

Pooling has many advantages:

• there is no overhead of TCP/IP connection, authorization, etc. - it is only done once.

• pool will take care of broken connections, it may also test connection before giving it to you

• finally, the number of active database connections is more stable, connection pool should refuse returning connection if you have already opened too much

Answer 2

The pooling itself is done by code that sits between the application code and the database driver.

Who puts that code there? Could be anyone. It could be you - there are libraries like DBCP that your code could use to put a pool on top of the database. It could be a J2EE container, like Tomcat or JBoss. It could even be the database - as Tomasz points out, PostgreSQL comes with pooling code.

It sounds like you aren't using a J2EE container, in which case it's down to you or the database. Personally, i'd prefer a dedicated pooling implementation, like DBCP, over one supplied by the database. The database's programmers care most about the database; the pool's programmers care most about the pool.

So, get DBCP (IMHO, it's better than the alternatives), and use that.