Search code examples
javapostgresqljdbcconnection-poolingc3p0

When using a connection-pool should I get the connection each query or once each batch?

I have been unable to find an exact answer to this question. I'm using C3P0's ComboPooledDataSource. Which of these methodologies is better practice:

dataSource = connectionClass.getDataSource();
conn = dataSource.getConnection;
executeQuery(query1, conn);
executeQuery(query2, conn);
...
executeQuery(finalQuery, conn);
conn.close();

OR

executeQuery(query1);
executeQuery(query2);
...
executeQuery(finalQuery);

where executeQuery:

conn = dataSource.getConnection;
st = conn.createStatement();
rs = executeQuery(query);
conn.closed();

In short, I have to do a decent amount of queries every so often. Is it better to go with the first design, which gets the connection once for each batch and passes it as an argument. Or is it better to go with the second approach and just get a connection each time I call my executeQuery method. If I was using DriverManager I would obviously choose the first (only get the connection once), but when using the C3P0 package I am not sure if doing that is the right way to go or not. Or does it not matter with such a package?

Solution

  • With a connection pool, the difference is neglectible, because even if you use the second approach, bringing back a pooled connection takes little time. Still, using the first approach is the better way to go, because

    • It avoids the additional (little) overhead of getting a connection from the pool.

    • If you later need to introduce transactions (do all of your changes or, in case of an error, conveniently and securely roll back your changes), then the first approach is your only option.