Singleton for datasource?

[joseph corner]

I have a web application which uses Tomcat's connection pooling. I am paranoid about opening and closing connections efficiently. Does this approach seem reasonable? I use a singleton class to create a single datasource, then use that for opening connections:

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
 
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 {
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
DataSource dataSource = (DataSource)envCtx.lookup("jdbc/myAppDB");
return dataSource;
}
}

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);
}
}

Thanks for any feedback / reassurance!

[Paul Sturrock]

What advantage do you think using a singleton gives you? Your code is stil open to the problem of developers forgetting to call DbConnection.close() is a safe way, so I'm not sure what it is you are trying to achieve by wrapping the DataSource and COnnection in your own classes.

[joseph corner]

I was thinking in terms of efficiency - that it would be better to just have a single datasource object. I'm not totally clear on how Tomcat's pooling works, so this also seemed likely to ensure that there would just be one pool of connections, as they all come from the same datasource.

Am I on the wrong track?

[Paul Sturrock]

The DataSource in Tomcat is JNDI bound is it not? In which case you probably are only using one instance anyway. So there is only going to be one connection pool. I think you might be worrying about something that is already taken care of.
[ January 23, 2007: Message edited by: Paul Sturrock ]