java DB connection question

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/