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.
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.
c. One connection per session: Open the connection when you create the
session. Close the connection in the session time out event:
HttpSessionBindingListener.valueUnbound(..)
d. One connection per web application: Store the connection in the
ServletContext.
e. One connection per web application: Store the connection as a public
global static variable.
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.
Once you have decided to go with a connection pool, you have some more
decisions to make.
1. Write the connection pool yourself (Fun for me, but not practical for
most of my clients)
2. Use a third party, stand-alone connection pool like DbConnectionBroker
3. Find a JDBC driver, that implements connection pooling (is anyone using
this?)
4. Use an app server that provides connection pooling (like Web Logic or
Orion)
The next consideration is the API for working with the pool. You choices
are:
1. Use a Proprietary API:
For example, DbConnectionBroker uses:
conn = myBroker.getConnection();
// do work with connection
myBroker.freeConnection(conn);
2. Use a standard API. Sun, has defined the "JDBC 2.0 Optional Package
Specification". This specification basically states that the API for using a
pooled connection is indistinguishable from using a normal connection (at
least to application developers). In other words, the standard API is
essentially, "NO API". For example, here is how one might retrieve a
"non-pooled" database connection:
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/nonPooled/MyDb");
conn = ds.getConnection();
// do work
conn.close();
And here is how one would retrieve a "pooled" database connection:
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/pooled/MyDb");
conn = ds.getConnection();
// do work
conn.close();
The only code difference is in the jndi lookup string. In the first example
getConnection() means: "Open a new connection to the database server". But
in the second example, getConnection() means, pull a connection from the
pool. Likewise, the meaning of conn.close() is also different.
So here is what I am using:
For some clients, I am using Orion App server, a fully J2EE compliant
Servlet and EJB server. Orion provides connection pooling, via a JNDI lookup
as shown above. So for these apps, I am using the Sun standard API for
pooling:
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/pooled/MyDb");
conn = ds.getConnection();
// do work
conn.close();
I think this gives me one pool, per web application, but I'm not sure.
Anyone else know? Is this Sun defined or vendor specific?
For some clients, I am using Apache tomcat (with mySql). For these apps, I'm
currently using DbConnectionBroker, stored in the ServletContext. This gives
me one pool per web application. What I would like to do, is use the
standard Java API's for these tomcat apps, but I can't find any stand-alone
connection pools that support the standard, java-defined API for connection
pooling. Anyone know of any?
So there is my take on the whole connection pool thing. Please let me know
if this sounds correct and where I'm wrong.
Thanks,
Dave Ford
___________________________________________________________________________
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
