How create an PostgreSQL connection pool using Java?
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
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