Passing jdbc datasource v/s passing the Connection object - From servlet to java class

Question

I have a main Servlet that processes post/get requests.
I am using connection pooling (jdbc / mysql with glassfish v3) and my servlet code is:

public class Controller extends HttpServlet {
private DataSource datasource;
@Override
public void init() throws ServletException {
      super.init();
     try {
        //Database Connection pooling:
        InitialContext ctx = new InitialContext();
        datasource = (DataSource)ctx.lookup("jdbc/MySQLPool");
      }
      catch (Exception e) {
         e.printStackTrace();
       }
   }
private Connection getConnection() throws SQLException {
    return datasource.getConnection();
    }
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
    Connection connection=null;
    try {
            connection = datasource.getConnection();
            Object obj=  cmdFactory.getInstance().getCommand(Cmd).execute(connection);
        }

etc... and at the end of the servlet in a finally block i close the connection

So right now i am passing the "connection" object as a parameter in the last line, to be used by other (non servlet) java classes through lower layers of the application. Is this wrong? is it better rather to pass the datasource object (and then in the specific classes do datasource.getConnection())? or is there something similar to "getServletContext().getAttr(database)" that can be used in the other java classes to get this connection?

Answer 1

A DataSource allows getting a JDBC connection (from a pool of connections, most of the time). In a servlet environment, if you ask a connection to a DataSource twice, you'll get two different connections.

So passing the DataSource doesn't make sense: you want all the objects in the chain of calls to use the same connection, and commit at the end.

And the connection must be closed by the method which got it from the DataSource, in a finally block, else the pool will leak connections, and you'll quickly run out of available connections.