JDBC Connection pooling using C3P0

Question

Following is my helper class to get DB connection:

I've used the C3P0 connection pooling as described here.

public class DBConnection {      private static DataSource dataSource;     private static final String DRIVER_NAME;     private static final String URL;     private static final String UNAME;     private static final String PWD;      static {          final ResourceBundle config = ResourceBundle                 .getBundle("props.database");         DRIVER_NAME = config.getString("driverName");         URL = config.getString("url");         UNAME = config.getString("uname");         PWD = config.getString("pwd");          dataSource = setupDataSource();     }      public static Connection getOracleConnection() throws SQLException {         return dataSource.getConnection();     }      private static DataSource setupDataSource() {         ComboPooledDataSource cpds = new ComboPooledDataSource();         try {             cpds.setDriverClass(DRIVER_NAME);         } catch (PropertyVetoException e) {             e.printStackTrace();         }         cpds.setJdbcUrl(URL);         cpds.setUser(UNAME);         cpds.setPassword(PWD);         cpds.setMinPoolSize(5);         cpds.setAcquireIncrement(5);         cpds.setMaxPoolSize(20);         return cpds;     } } 

in the DAO i'll be writing something like this:

try {             conn = DBConnection.getOracleConnection();              ....   } finally {     try {         if (rs != null) {             rs.close();         }         if (ps != null) {             ps.close();         }         if (conn != null) {             conn.close();         }     } catch (SQLException e) {         logger                 .logError("Exception occured while closing cursors!", e);      } 

Now, my question is should I bother to do any other clean up other than closing the cursors(connection/statement/resultSet/preparedStatement) listed in the finally block.

What is this cleanup?? When and where should I do this?

Should you find anything wrong in the above code, please point out.

Answer 1

With a pooled data source, the connections in the pool are not actually closed, they just get returned to the pool. However, when the application is shut down, those connections to the database should be properly and actually closed, which is where the final cleanup comes in.

Incidentally, the c3p0 project is pretty much dead in the water, I recommend you use Apache Commons DBCP instead, it's still being maintained.

Answer 2

DAOs should not be responsible for acquiring a connection to the database. They have no way to know when they're being used as part of a larger transaction. You should be passing the data source or connection instance into the DAO.

If any of the calls to close in your finally block throw an exception, none of the ones that follow will be called. Each one needs to be in its own try/catch block. I put them into a utility class as static methods.