Hi guys,

I started to work on a legacy systems that doesn't use any connection pool.
Actually its work like this.

I got a ProjectServlet, this ProjectServlet ask to DAOProject a list of projects.
This DAOProject is-a Conn - Conn is a class that handle database connections (code on pastie or below)

http://pastie.org/385643

public class Conn implements Serializable
{

    protected transient Connection conn;
    public void connect()
    {
            // <skip> some code here
            conn = DriverManager.getConnection("jdbc:mysql://host/db?user=...&pwd=...");
            // <skip> some code here
    }
    public void disconnect()
    {
            // <skip> some code here
            conn.close();
            // <skip> some code here
    }
}

public class DAOProject extends Conn {

    public List<Project> getProjects() {
        String sql = "SELECT * FROM PROJECT";
        List<Project> projects = new ArrayList<Project>();
        try {
            // this call Conn.connect()
            this.connect();
            // <skip> some code here
        } catch (Exception e) {
            // <skip> some code here
        } finally {
            // this call Conn.disconnect()
            this.disconnect();
        }
        return projects;
    }

}

So every method on all DAO's connect to database, execute the method and disconnects.
I'm trying to add c3p0 to this project, currently to make this more transparent, I'm only changing
the Conn class (again, code on pastie and below)

http://pastie.org/385650

public class Conn implements Serializable {
 
    private static ComboPooledDataSource cpds;
    protected transient Connection conn;
 
    public void connect() {
        conn = Conn.getConnection();
    }
 
    public static Connection getConnection() {
        Connection ret = null;
        if (cpds == null) {
          createPool();
        }
        try {
            ret = cpds.getConnection();
        } catch (SQLException e) {
            // <skip> some code
        }
        return retorno;
    }
 
    public void disconnect() {
        try {
            conn.close();
        } catch (SQLException e) {
            // <skip> some code
        }
    }
 
    private static void createPool() {
        cpds = new ComboPooledDataSource();
      cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://host/db?user=..?password=...");
        cpds.setUser("user");
        cpds.setPassword("pwd");
        cpds.setAcquireIncrement(2);
        cpds.setMaxConnectionAge(60 * 60 * 3);
        cpds.setMaxIdleTimeExcessConnections(60 * 10);
        cpds.setPreferredTestQuery("SELECT 1");
        cpds.setIdleConnectionTestPeriod(60 * 10);
        cpds.setMinPoolSize(3);
        cpds.setMaxPoolSize(30);
    }
 
}

So, doing a static ComboPooledDataSource, and returning new connections like this, is a good way?
Can someone tell me if its ok?

Regards,
--
Rafael Mueller