The original question was cross-posted to JSP-INTEREST as well. Craig "Craig R. McClanahan" wrote: > A couple of comments on the first two subjects are embedded below. > > Dave Ford wrote: > > > I have just spent the last day researching the topic of Database Connection > > Management and connection pooling, and now realize, that there are too many > > choices. I would like to summarize what I have learned and perhaps someone > > can correct me where I'm wrong or add a comment. > > > > Here are your choices: > > > > 1. Non-pooled: > > > > a. One connection per request: In the service method you open a connection, > > do your work, then close the connection. > > > > Once you benchmark this, you'll quickly decide it is not a good idea :-). > > > > > b. One connection per servlet: Store the connection as an instance variable > > of the servlet. Open it in the init method. Close it in the destroy method. > > > > Not a viable choice unless you only have a single user, because the connection > would be shared across all users. > > > > > c. One connection per session: Open the connection when you create the > > session. Close the connection in the session time out event: > > HttpSessionBindingListener.valueUnbound(..) > > > > This does not scale very well, because a user who just went to lunch is tying up > database resources (that could have been used to help someone else) until their > session times out. > > When using a connection pool, you need enough connections for the number of > simultaneous requests, not the number of simultaneously logged on users. > > > > > d. One connection per web application: Store the connection in the > > ServletContext. > > > > Also not a viable choice for the same reason. > > > > > e. One connection per web application: Store the connection as a public > > global static variable. > > > > Same as 2d. > > The issue in all of these cases is that your single connection would be shared by > multiple "logical" sessions that are happening at the same time for multiple > users. While some databases will let you get away with this in a read-only > situation, consider a transaction that updates the database. A COMMIT or ROLLBACK > done by one user affects *all* of the users currently using that connection. > > > > > 2. Pooled: > > > > a. One pool per servlet (Pool as static variable of servlet) > > b. One pool per application (Store pool in ServletContext) > > c. One pool per application (Store pool as singleton) > > d. One pool per application (Store pool in jndi server) > > > > It seems to me that 2d, is what Sun is recommending. > > > > My personal preference for non-EJB applications is 2b. Among other things, this > makes the pool available to JSP pages as an application-scope bean so that it can > be used in the page (either directly, or indirectly through a set of custom tags > for SQL support). For 2a and 2c, you'd need to put some scriptlets in your code to > gain access to the pool. > > If you're using a J2EE server the default choice will be 2d because the server > provides it for you. > > Craig McClanahan > > =========================================================================== > To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". > Some relevant FAQs on JSP/Servlets can be found at: > > http://java.sun.com/products/jsp/faq.html > http://www.esperanto.org.nz/jsp/jspfaq.html > http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP > http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
