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
