Hi, It's inefficient to do the DataSource lookup (an expensive operation) every time you need a connection. See Luke's approach in the other message with a similar subject.
And please don't put my name in subject lines -- there are a ton of other people here who can give great advice on this basic design issue ;) Yoav Shapira Millennium Research Informatics >-----Original Message----- >From: Caroline Jen [mailto:[EMAIL PROTECTED] >Sent: Saturday, September 11, 2004 12:43 AM >To: Tomcat Users List; [EMAIL PROTECTED] >Subject: RE: [OFF-TOPIC]Yoav --> RE: Some pretty basic Tomcat >ConnectionPooling Questions???? > >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] This e-mail, including any attachments, is a confidential business communication, and may contain information that is confidential, proprietary and/or privileged. This e-mail is intended only for the individual(s) to whom it is addressed, and may not be saved, copied, printed, disclosed or used by anyone else. If you are not the(an) intended recipient, please immediately delete this e-mail from your computer system and notify the sender. Thank you. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
