title: "Lazy initialization" of jdbc connections from jndi datasource/connection pool: feasibility
body: <p>I have a main controller servlet in which i instantiate a <b>datasource</b>. 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:</p>
  
  <pre><code>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()
   }
  
  }
  </code></pre>
  
  <p>Now , this has a <b>problem</b> for the first case, ie <b>connection=null</b>, as it does never close the connection in the <b>"finally"</b> part (explained why in <b>Update</b> below). <br></p>
  
  <p><b>"connection=null"</b> is for cases where commands might not need to open a db connection because the data it is seeking for are cached in an <a href="http://martinfowler.com/eaaCatalog/identityMap.html" rel="nofollow">identity map</a>. 
  <br></p>
  
  <p>I tried to pass the  <b>"Connection"</b> obj as a "null" parameter in the <b>.execute(tsk,connection);</b> and then in the corresponding java class to open a connection <b>if</b> needed <br></p>
  
  <p>--> it did open the connection inside the command, however when process goes back to servlet : <b>"Connection" is null as thus not closed.</b> <br>
  What can i do to make the <b>"Connection"</b> obj's value get updated so that when back in servlet it is not "Null" anymore and i'd be able to close it?<br></p>
  
  <p>I generally prefer to use a controller servlet that <b>opens/close</b> 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 <b>opens/close</b> of db connection assigned to the servlet?
  <br></p>
  
  <p><b>Update (to explain further):</b></p>
  
  <ul>
  <li>say i have a command : <b>X.java</b></li>
  <li>this command might/might not need a db connection (depends if the data searched for are in the identity map or not)</li>
  </ul>
  
  <p>The system that i would like to have is:</p>
  
  <p>(1)<b>"client request"</b> <br></p>
  
  <p>(2)---> <b>"Servlet":</b> command.execute(connection)//where connection = null for now <br></p>
  
  <p>(3) ---> <b>"Command X"</b>: Do i need to go to database or record is in identity map?<br>
  (3.a) Case where it is needed to go to the database: <br>
  (3.a.1)connection = datasource.getconnection<br>
  (3.a.2) go get the data<br></p>
  
  <p>(4)---><b>back</b> to servlet: <b>close "connection" in "Servlet"</b></p>
  
  <p>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:</p>
  
  <pre><code>finally { 
   if(connection!=null)
    connection.close()
   }
  </code></pre>
  
  <p>Does <b>not work (doesn't close the connection)</b> 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"?</p>
  
  <p><b>SOLUTION(S)</b></p>
  
  <p>In case you are encountering the same scenario, you can chose of these <b>2 solutions:</b><br></p>
  
  <ul>
  <li><p>You can Either use <a href="http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/datasource/LazyConnectionDataSourceProxy.html" rel="nofollow">LazyConnectionDataSourceProxy</a>, as mentionned by @Ryan Stewart for a "clean abstraction" and more professional solution<br></p></li>
  <li><p>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")</p></li>
  </ul>
  
  <p><b>My personal solution, Details:</b>
  <br></p>
  
  <ul>
  <li>I created a "Helper" class, which constructor takes the "datasource" as parameter</li>
  <li>This helper class has methods to: "Lazy get" connection from pool,"close" connection</li>
  <li>This class is instantiated in the servlet, and it gets a connection from the pool <b>Only</b> if needed throughout the application.</li>
  </ul>
  
  <p>This is the code i added/modified in the servlet:</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p>Then say in a command "X" , a db connection is needed i do:</p>
  
  <pre><code>Connection connection = hp.LazyGet();//Now got a connection from the pool
  </code></pre>
  
  <p>And this way, when proccess flow is back to the servlet level, i can :</p>
  
  <ul>
  <li>Close</li>
  <li>rollback</li>
  <li>commit</li>
  <li>etc..</li>
  </ul>
  
  <p>All on this hp object of the helper class.<br></p>
  
  <p><b>What benefits do i get from this:</b></p>
  
  <ul>
  <li>I limit all database <i>open / close / commit / rollback</i> in <b>one</b> place, ie the Servlet, which is responsible of executing commands.</li>
  <li>Having <b>3</b> cases: <i>never needs db / always needs db / might need db </i>thus now i decreased calls to the database by <b>1/3</b> , which is quite a lot knowing that database call grows exponentially with new features and new users registrations.</li>
  </ul>
  
  <p>It might not be the <b>Cleanest </b> workaround, but between this way and having an additional "unnecessary" 1/3 database calls, it surely is better. Or just use <a href="http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/datasource/LazyConnectionDataSourceProxy.html" rel="nofollow">LazyConnectionDataSourceProxy</a> if you want a <i>Tested, abstract and clean method.</i></p>