Re: java DB connection question

Use a servlet filter. You can attach the checked out connection to a request
attribute for use in your struts actions, then the filter can make sure it's
closed properly after the chain is done.

On 6/20/07, Meichun Li <pcloudy2005-/[email protected]> wrote:
>
> Hi,
>
> What is the best practice in closing DB connection for
> Struts Application with SQL server back end?
>
> I use Tomcat (tomcat 5.5.17) connection pooling. I
> have an initial servlet  that does the JNDI look up
> and  is loaded when tomcat starts.
>
> The servlet class:
>
> public class InitServlet extends HttpServlet {
>
>         protected static DataSource dataSource;
>
>         public void init() {
>                 try {
>                         String jndiName = getInitParameter("jndi.name");
>                         DataSourceManager.getInstance(jndiName);
>                 } catch (Exception e) {
>                         e.printStackTrace();
>                 }
>
>         }
>
> }
>
> And the DataSourceManager is:
>
> public class DataSourceManager {
>
>         private static DataSource ds = null;
>
>         private static DataSourceManager instance = null;
>
>         private DataSourceManager(String jndiName) throws
> Exception {
>                 createDataSource(jndiName);
>         }
>
>         public static synchronized DataSourceManager
> getInstance(String jndiName) throws Exception {
>                 return (instance != null) ? instance : new
> DataSourceManager(jndiName);
>         }
>
>         private void createDataSource(String jndiName) throws
> Exception {
>                 Context initContext = new InitialContext();
>                 Context envContext = (Context)
> initContext.lookup("java:comp/env");
>                 ds = (DataSource) envContext.lookup(jndiName);
>
>         }
>
>         public static DataSource getDataSource() throws
> Exception {
>                 return ds;
>         }
>
>
> }
>
>
> I have two options:
>
> Option 1: In the struts Action execute method scope,
> get the connection, pass the connection to the
> business layer and DAO layer, and close the connection
> in the execute finally block for example:
>
>         public ActionForward
> executeAction(ActionMapping mapping, ActionForm form,
> HttpServletRequest request, HttpServletResponse
> response) throws Exception {
>
>                 DataSource
> dataSource=DataSourceManager.getDataSource();
>
>                 Connetion connection= null;
>
>                 Try{
>
>                         Connection=dataSource.getConnection();
>
>                         // pass the connection to the DAO layer
>                         …....
>
>                 } catch (SQLException e) {
>
>                 } finally {
>                      if (connection != null) {
>                                 try {
>                                     connection.close();
>                                 } catch (SQLException e) {
>                                         ;
>                                 }
>                                 connection = null;
>                         }
>                 }
>
>
>        I've tried this but sometimes I have connection
> was closed error.
>
>
> Option 2:   Get connection and close connection in
> each method of DAO classes. Each thread/request to the
> action invloves running several DAOs' method, so
> serverl connections are open and closed by turn.  I
> watched the SQL server process manager, it looks like
> the connection  can't be returned to the pool
> immediately even though the transaction is done, it
> takes a while after that.
>
> Before I do any change, I will like to figure out what
> is the best practise to handle the connection. Any
> help will be appreciated.
>
> Thanks!
> Maggie
>
>
>
>       ____________________________________________________________________________________
> Park yourself in front of a world of choices in alternative vehicles.
> Visit the Yahoo! Auto Green Center.
> http://autos.yahoo.com/green_center/
>
> _______________________________________________
> Juglist mailing list
> [email protected]
> http://trijug.org/mailman/listinfo/juglist_trijug.org
>

_______________________________________________
Juglist mailing list
[email protected]
http://trijug.org/mailman/listinfo/juglist_trijug.org