Hi, I'd say your new version is much better designed and significantly more scalable than the old, yeah.
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. Yoav Shapira Millennium Research Informatics >-----Original Message----- >From: Luke (Terry) Vanderfluit [mailto:[EMAIL PROTECTED] >Sent: Friday, September 10, 2004 3:20 AM >To: Tomcat Users List >Subject: [OFF-TOPIC]Yoav --> RE: Some pretty basic Tomcat ConnectionPooling >Questions???? > >Hi Yoav and all, >> - Hold the connection for as little a time as possible. That is, get it >> from the pool, use it, and return it to the pool as soon as possible. >> - That means you don't get the connection in a servlet init() method and >> return it in a destroy() method. That's too long a time to hold >> connections usually. >> - Always return connections to the pool. Typically, this is done in the >> finally clause of a try/catch block to ensure it is executed even if the >> JDBC operation fails. >> - You create your pool once, preferably when the app starts up, and you >> close the pool itself once, preferably when the app shuts down. A >> ServletContextListener is good for this purpose. Make sure you close >> the pool. >> - Servlets and other classes use the pool as needed. You don't have to >> use a one connection per servlet design (in fact those usually don't >> work in the real world). > >I've been using connection pooling via an init() method. >Above you say that one should avoid that. >So now I have put the connection code inside the method that actually >does the database work and dispensed with the init() method. > >Before I made this change I didn't have a conn.close() statement or a >finally clause anywhere either (I DID have resultset.close and >statement.close()). >Now I have added a conn.close() statement after each database >interaction has been completed. > >Both versions of the code work. >Would you say the it is optimised after having gotten rid of the init() >method? > >this is the code in it's latter version: >~~~~~~~~~~~~~~~~~~~ > try { //establish the connection > Context ctx = new InitialContext(); > if(ctx == null) { > throw new Exception("No Context"); > } > DataSource ds = >(DataSource)ctx.lookup("java:comp/env/jdbc/mb"); > if(ds != null) { > conn = ds.getConnection(); //conn is a global variable > if(conn != null) { > message = "Got Connection to DB " + conn.toString(); > } > } > } // end try block > catch(Exception e) { > e.printStackTrace(); > } > try { //carry out the database query > Statement stmt = conn.createStatement(); > ResultSet rst = stmt.executeQuery("select * from category where >categoryname not like '%Timetable%';"); > 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) { > } >~~~~~~~~~~~~~~~~~~~~~~~~~~ > >Appreciate your help, >kind regards, >Luke > >-- >======================== >Luke (Terry) Vanderfluit >Mobile: 0421 276 282 >======================== > > >--------------------------------------------------------------------- >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]
