Hi, further to this thread I have now implemented the database connection as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To get a database connection via an external class see below the code that does this: 1. the init() method that gets the DataSource from an external connection. 2. a method that queries the database and gets the connection from an external class. 3. the DBConnection class itself 4. a destroy method that sets the DataSource to null within the calling class. I'm pretty sure that doing 'conn = DBConnection.getDBConnection()' is a good move so as not duplicate code. <question> However, I'm not sure about 'ds = DBConnection.getDataSource()'... that might just as well go as code in the init() method itself. What is more efficient? </question> <question> Alistair had some interesting comments, namely that 1. We should rename the DBConnection class to DBConnectionFactory. I have often wondered what a factory is in this sense, could you elaborate? 2. Adding a finalize() method in the external class --> wouldn't that nullify the DataSource before we are finished with it? --> isn't that doubling up with the destroy() method? --> how much better would it be to nullify the DataSource in a finalize() method, a destroy() method or both? 3. I'd like to find out more about 'adding a static reference to itself and adding a static method called getInstance()' --> how much more efficient would that be? --> how do I implement that? </question> This is very interesting and I'm sure it leads to a lot cleaner runnning of the application. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. The init() method withing the calling servlet: // "init" does DataSource lookup public void init(ServletConfig config) throws ServletException { super.init(config); try {ds = DBConnection.getDataSource();} catch (Exception e) {} } // end init() =============== 2. A method within the calling servlet that uses the external class to set up a Database connection: // this method populates a bean private void populateCLBean() { conn = DBConnection.getDBConnection(); try { Statement stmt = conn.createStatement(); ResultSet rst = stmt.executeQuery("select * from category;"); while(rst.next()) { Category c = new Category(); c.setCategoryName(rst.getString("categoryname")); clBean.addCategory(c); } // end while block rst.close(); stmt.close(); conn.close(); } // end try block catch(Exception e) { } } // end method populateCLBean() ================ 3. The external class: package mb; import java.sql.Connection; import java.sql.SQLException; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; public class DBConnection { private static String message; private static Connection conn = null; private static InitialContext ctx = null; private static DataSource ds = null; public static DataSource getDataSource() { try { ctx = new InitialContext(); ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mb"); } // end try block catch(Exception e) { } // end catch block return ds; } // end method getDataSource() public static Connection getDBConnection() { try { conn = ds.getConnection(); } // end try block catch(Exception e) { } return conn; } // end method getDBConnection() } // end class DBConnection ================== 4. The destroy method within the calling class. public void destroy() { ds = null; } //end method destroy() ================== -- ======================== Luke (Terry) Vanderfluit Mobile: 0421 276 282 ======================== --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]