Which is a better way to handle connection pooling?

Question

I'm trying to implement connection pooling for a JSF 2.1 application which has a H2 database and Jetty 9 Web server embedded in it. I have two options to implement connection pooling for the h2 database. The options being let Jetty implement connection pooling for me, or I define a application scoped managed bean which creates connection pool. I would like to know which would be a better approach in handling connection pooling?

Connection pooling using Application scoped managed bean:

JdbcConnectionPool cp = JdbcConnectionPool.create(
             "jdbc:h2:~/test", "sa", "sa");
         for (String sql : args) {
             Connection conn = cp.getConnection();
             conn.createStatement().execute(sql);
             conn.close();
         }
         cp.dispose();

Answer 1

Either approach of connection pooling is fine. There are many connection pool implementations (each one with advantages and disadvantages), use whatever you feel like using.

If you have a list of statements to execute, then I wouldn't open a new connection for each statement. Instead, execute all statements with the same connection (and statement):

JdbcConnectionPool cp = JdbcConnectionPool.create(
    "jdbc:h2:~/test", "sa", "sa");
...
Connection conn = cp.getConnection();
Statement stat = conn.createStatement();
for (String sql : args) {
    stat.execute(sql);
}
conn.close();
...
cp.dispose();

The connection pool can be started / stopped:

• Outside the web application, as a resource (that's a bit more complicated in my view), for example as described in the article "Database Connection Pooling with Tomcat". You will find similar documentation for Jetty.

• Using a ServletContextListener (also described in the H2 documentation). In my view, this is a bit simpler. The disadvantage is that the connection pool can not be used by multiple web applications.