How to establish a connection pool in JDBC?

java jdbc connection-pooling
llm picture llm · May 14, 2010 · Viewed 190.7k times · Source

Can anybody provide examples or links on how to establish a JDBC connection pool?

From searching google I see many different ways of doing this and it is rather confusing.

Ultimately I need the code to return a java.sql.Connection object, but I am having trouble getting started..any suggestions welcome.

Update: Doesn't javax.sql or java.sql have pooled connection implementations? Why wouldn't it be best to use these?

Answer

Pascal Thivent picture Pascal Thivent · May 14, 2010

If you need a standalone connection pool, my preference goes to C3P0 over DBCP (that I've mentioned in this previous answer), I just had too much problems with DBCP under heavy load. Using C3P0 is dead simple. From the documentation:

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("swaldman");
cpds.setPassword("test-password");

// the settings below are optional -- c3p0 can work with defaults
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);

// The DataSource cpds is now a fully configured and usable pooled DataSource

But if you are running inside an application server, I would recommend to use the built-in connection pool it provides. In that case, you'll need to configure it (refer to the documentation of your application server) and to retrieve a DataSource via JNDI:

DataSource ds = (DataSource) new InitialContext().lookup("jdbc/myDS");

Related questions

Connection pooling options with JDBC: DBCP vs C3P0

What is the best connection pooling library available for Java/JDBC? I'm considering the 2 main candidates (free / open-source): Apache DBCP - http://commons.apache.org/dbcp/ C3P0 - http://sourceforge.net/projects/c3p0 I've read a lot about …

Read More
Closing JDBC Connections in Pool

Our standard code section for using JDBC is... Connection conn = getConnection(...); Statement stmt = conn.conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rset = stmt.executeQuery (sqlQuery); // do stuff with rset rset.close(); stmt.close(); conn.close(); Question 1: …

Read More
How do I implement a DAO manager using JDBC and connection pools?

My problem is as follows. I need a class that works as a single point to a database connection in a web system, so to avoid having one user with two open connections. I need it to be as optimal …

Read More