Managing pooling datasource in Java web app with singleton class, Tomcat, C3P0

Question

I have a java web app running in Blackboard learning software, which uses Tomcat 5.5. The app connects to an external database.

I don't have access to declare a datasource on the Blackboard server, so I am trying to package everything in the web app. I have had a problem with my commons-based pooling approach running out of connections, in spite of carefully closing all resultsets, statements and connections after use. I am now switching to a C3P0 pooling method, but I'm not sure that my general approach is correct.

I am using a singleton class to create a datasource, with the aim of minimizing the creation and destruction of datasources which implement connection pooling. The following classes should illustrate this clearly. Is this a reasonable approach, or is it likely to have contributed to my previous problem of running out of connections?

Thanks.

EDIT. Updated question to clarify purpose of singleton approach. I am trying to avoid creating a datasource everytime I need a db connection. This seems to nullify the benefits of having connection pooling.

Datasource class:

import java.beans.PropertyVetoException;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import com.mchange.v2.c3p0.*;

public class MyDataSource {

private static MyDataSource mds = new MyDataSource();
public static DataSource ds;

private MyDataSource() {
    try {
        ds = getDataSource();
    } catch (NamingException e) {
        e.printStackTrace();
    }
}

public static MyDataSource getInstance(){
    return mds;
}

public Connection getConnection() throws SQLException, NamingException {
    Connection myConnect = ds.getConnection();
    return myConnect;
}

private DataSource getDataSource() throws NamingException {

    ComboPooledDataSource cpds = new ComboPooledDataSource(); 
    try {
        cpds.setDriverClass( "com.mysql.jdbc.Driver" );
    } catch (PropertyVetoException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    cpds.setJdbcUrl( "jdbc:mysql://195.195.xx.xx:3306/dbName" ); 
    cpds.setUser("lemmy"); 
    cpds.setPassword("xxx");
    cpds.setMaxIdleTime(180);
    cpds.setMaxPoolSize(100);       
    return cpds;
}
 }

Connection class:

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class DbConnection {

public Connection c;

public DbConnection() throws NamingException, SQLException {
    c = getConnection();
}

public Connection getConnection() throws SQLException, NamingException {        
    Connection myConnect = MyDataSource.getInstance().getConnection();
    return myConnect;       
}

public void close(){
    JDBCUtils.close(this.c);
}
}

Closing connections etc.

import java.sql.*;

public class JDBCUtils {

static public void close (ResultSet rs) {
    try { if (rs!=null) rs.close(); } catch (Exception e) {}
    }

  //  Works for PreparedStatement also since it extends Statement.
  static public void close (Statement stmt) {
    try { if (stmt!=null) stmt.close(); } catch (Exception e)   {}
    }

  static public void close (java.sql.Connection conn) {
    try { if (conn!=null) conn.close(); } catch (Exception e) {}
    }   

}

Example usage:

    String myQuery = null;
    DbConnection myConnect = null;
    Statement myStatement = null;
    ResultSet rs = null;

    try {
       myConnect = new DbConnection();
        myStatement = myConnect.c.createStatement();

                // Do stuff here

        }catch (SQLException e) {
    out.println("SQL Error: "+e);
    } finally {
        JDBCUtils.close(rs);
        JDBCUtils.close(myStatement);
        myConnect.close();
    }

Answer 1

You might want to look at JdbcTemplate from the spring framework. You can use it standalone, as Spring has the claim to provide many helper classes. That provides a simplification for the connection/statement/resultset nesting problem. Also it handles the querying simpler.

I know for learning purposes you probably won't use JdbcTemplate, but the class design is interesting.

On pooling there already is an answer from @duffymo.

Answer 2

I think this is the wrong idea in every way.

You want multiple connections in a connection pool. Let the pool manage the lifecycle; it'll amortize the creation cost over every request.

A singleton will mean one request for every one. That makes no sense to me.

Your app should use connections this way: one per request. Acquire it, use it, return it to the pool in the scope of a single method. It'll scale better that way. Less code for you to write as well. Set it up as a JNDI data resource and you're good to go.