"Lazy initialization" of jdbc connections from jndi datasource/connection pool: feasibility

Question

I have a main controller servlet in which i instantiate a datasource. The servlet opens and closes the connections. Mainly, the servlet instantiates a command from the application using the "factory pattern". here is some code to explain:

public void init() throws ServletException {
    super.init();
    try {
            datasource =(DataSource) getServletContext().getAttribute("DBCPool");
    }
    catch (Exception e) {

    }
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
  //some code...
  Connection connection = null;
  if(cmd.mightNeedLazyLoadingAConnection)
  {

       connection = null;
  } 
  else 
       connection = getConnection();//where getConnection is a method: datasource.getconnection();

      //now a command (a java class) is instantied, to which the "null" CONNECTION obj is passed as parameter
     cmdFactory.getInstance().getCommand(Cmd).execute(tsk,connection);


  //some code

//Then wherever there is catch exception i close() the connection
// and it is always closed in finally
finally { 
 if(connection!=null)
  connection.close()
 }

}

Now , this has a problem for the first case, ie connection=null, as it does never close the connection in the "finally" part (explained why in Update below).

"connection=null" is for cases where commands might not need to open a db connection because the data it is seeking for are cached in an identity map.

I tried to pass the "Connection" obj as a "null" parameter in the .execute(tsk,connection); and then in the corresponding java class to open a connection if needed

--> it did open the connection inside the command, however when process goes back to servlet : "Connection" is null as thus not closed.
What can i do to make the "Connection" obj's value get updated so that when back in servlet it is not "Null" anymore and i'd be able to close it?

I generally prefer to use a controller servlet that opens/close db connections, so what would be the best way to deal this kind of scenario where you have to do some sort of "lazy loading" a db connection from the pool and at the same time keep the opens/close of db connection assigned to the servlet?

Update (to explain further):

• say i have a command : X.java
• this command might/might not need a db connection (depends if the data searched for are in the identity map or not)

The system that i would like to have is:

(1)"client request"

(2)---> "Servlet": command.execute(connection)//where connection = null for now

(3) ---> "Command X": Do i need to go to database or record is in identity map?
(3.a) Case where it is needed to go to the database:
(3.a.1)connection = datasource.getconnection
(3.a.2) go get the data

(4)--->back to servlet: close "connection" in "Servlet"

Right now it is working until (3.a.2), but once back in (4) it appears that connection is still "null" and thus the code:

finally { 
 if(connection!=null)
  connection.close()
 }

Does not work (doesn't close the connection) and thus the db pool get drained like that. How could connection - which starts as "null" and changes inside command "X"- get "globaly" updated to the its new value, and not only "updated" inside the scope of command "X"?

SOLUTION(S)

In case you are encountering the same scenario, you can chose of these 2 solutions:

• You can Either use LazyConnectionDataSourceProxy, as mentionned by @Ryan Stewart for a "clean abstraction" and more professional solution
  

• Or if you'd like use my solution described below (Basically i implemented a class similar to "LazyConnectionDataSourceProxy" but it is not as clean, it has less abstraction of details than "LazyConnectionDataSourceProxy")

My personal solution, Details:

• I created a "Helper" class, which constructor takes the "datasource" as parameter
• This helper class has methods to: "Lazy get" connection from pool,"close" connection
• This class is instantiated in the servlet, and it gets a connection from the pool Only if needed throughout the application.

This is the code i added/modified in the servlet:

Connection connection = null;
if(cmd.mightNeedLazyLoadingAConnection)
{

     helper hp =  new helper(datasource);
     cmdFactory.getInstance().getCommand(Cmd).execute(tsk,hp);
} 
else 
{
     connection = getConnection(); 
     cmdFactory.getInstance().getCommand(Cmd).execute(tsk,connection);
}

Then say in a command "X" , a db connection is needed i do:

Connection connection = hp.LazyGet();//Now got a connection from the pool

And this way, when proccess flow is back to the servlet level, i can :

• Close
• rollback
• commit
• etc..

All on this hp object of the helper class.

What benefits do i get from this:

• I limit all database open / close / commit / rollback in one place, ie the Servlet, which is responsible of executing commands.
• Having 3 cases: never needs db / always needs db / might need db thus now i decreased calls to the database by 1/3 , which is quite a lot knowing that database call grows exponentially with new features and new users registrations.

It might not be the Cleanest workaround, but between this way and having an additional "unnecessary" 1/3 database calls, it surely is better. Or just use LazyConnectionDataSourceProxy if you want a Tested, abstract and clean method.

Answer 1

Use a LazyConnectionDataSourceProxy. Then just get a "connection" every time, but a real connection is opened only when you actually do something that requires one. Thus you obey the "create/destroy" wisdom which Hiro2k pointed out because the connection's lifecycle is completely managed by your servlet.