This is what I do and would like to have feedbacks: import java.sql.Connection; import java.sql.SQLException; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource;
public class DBConnection { public static Connection getDBConnection() throws SQLException { Connection conn = null; try { InitialContext ctx = new InitialContext(); DataSource ds = ( DataSource ) ctx.lookup( "java:comp/env/jdbc/MySQLDB" ); try { conn = ds.getConnection(); } catch( SQLException e ) { System.out.println( "Open connection failure: " + e.getMessage() ); } } catch( NamingException nEx ) { nEx.printStackTrace(); } return conn; } } --- "Luke (Terry) Vanderfluit" <[EMAIL PROTECTED]> wrote: > Hi Yoav and all, > > Thanks for your reply, > > > But you went a bit too far: the DataSource lookup > is potentially > > expensive. That you can do in the init() method > and keep a reference to > > the DataSource, because keeping that reference > doesn't use a connection > > resource. > > Then in your servlet methods, get a connection > from the DataSource, use > > it, and release it. > > In your servlet destroy method, null out your > DataSource reference. > > So the DataSource lookup is done once, the > DataSource reference is kept > > as a private non-static member variable of the > servlet class, and the > > Connenctions are used only within methods, they're > not class member > > variables. > > So now I have changed my code to: > 1. Declaration of private global variables: > <code> > private Context ctx = null; > private DataSource ds = null; > private Connection conn; > </code> > > 2. an init() method: > <code> > // "init" does DataSource lookup > public void init(ServletConfig config) throws > ServletException { > super.init(config); > try { > ctx = new InitialContext(); > if(ctx == null) { > throw new Exception("No Context"); > } > ds = > (DataSource)ctx.lookup("java:comp/env/jdbc/mb"); > } // end try block > catch(Exception e) { > e.printStackTrace(); > } > } // end init() > </code> > > 3. an openConnection() method: > <code> > private void openConnection() { > try { > if(ds != null) { > conn = ds.getConnection(); > if(conn != null) { > message = "Got Connection to DB " + > conn.toString(); > } > } > } // end try block > catch(Exception e) { > e.printStackTrace(); > } > } //end method openConnection() > </code> > > 4. a destroy() method that nulls the DataSource: > <code> > public void destroy() { > ds = null; > } > </code> > > <remarks> > -the conn.close() is called in the methods that call > openConnection(). > -I'm thinking of doing an 'include' for the > openConnection method, so I > don't have the code for the same method sitting in > multiple classes. > Would that be a good idea? (maintainability, yes but > in terms of > overhead?) > </remarks> > > Would this be the 'leanest' scenario for a database > connection? > thanks again, > Luke > > -- > ======================== > Luke (Terry) Vanderfluit > Mobile: 0421 276 282 > ======================== > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]