Hi Dave,
About six months back I was thinking about using PreparedStatement in my
servlets since they are pre-compiled and faster (apart from the other advantages
mentioned by Craig). I did some preliminary testing and found out that
PreparedStatements were anywhere from 20% to 60% faster than regular statements.
Although my testing was not exhaustive and rigorous, it still made sense to use
PreparedStatement in servlets as most of the database operations use the same
SQL statements (with different parameters of course) repetitively.
I was working on creating a PreparedStatement pool similar to Hans
DBConnectionManager. I wrote a bare bones version of it and stopped it in the
middle as I was working on other project and did not get time. What I have tried
to do is to create a pool of Connection-PreparedStatement pair and use this pair
rather than use pool of Connections. You can download this program at
www.virtualwave.net and modify it to suit your needs.
I hope someone out will take this code and improve it to make it a full fledged
PreparedStatementManager like the DBConnectionManager.
Sanjesh Pathak
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.
>
> 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
___________________________________________________________________________
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
