If you're already using pooling then I imagine you already have a resource defined for the pool in either server.xml or in the context xml file. To pool prepared statements, which is desireable, you should configure it to look something like this in Tomcat 5.5:
<Resource type="javax.sql.DataSource" auth="container" name="jdbc/cPool" username="user" password="pwd" driverClassName="com.ibm.as400.access.AS400JDBCDriver" url="jdbc:as400://serverName;extended dynamic=true;package library=pkgLib;package=pkgName;package add=true;package cache=true" initialSize="5" removeAbandoned="true" removeAbandonedTimeout="600" maxActive="0" maxIdle="10" minIdle="2" maxWait="10" poolPreparedStatements="true" logAbandoned="false" /> Note that in this example I'm using an IBM iSeries DB2 database driver, not my sql, but the rest of the config should be close to the same with the exception that on a production machine you'll want to have your initialSize, maxIdle and minIdle set much higher depending on the number of concurrent connections that you expect to make. Also note the url parameter - DB2's jdbc driver and database support dynamic package support with prepared statements, which can sometimes offer huge sql performance gains due to the system's ability to offer better optimization for prepared statements and cache the optimization so that the SQL optimizer need not be initialized for every subsequent statement. I don't know if my sql supports this or not, but you would definitely want to look into it. Lastly, note that this configuration is only applicable to Tomcat 5.5 and possibly later versions. Tomcat 5.0 and previous require a different syntax, using a more structured xml format, which can be reviewed here: http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html The best possible database configuration is as described above - using connection pooling with prepared statements and SQL package support - in cases where stored procedures are more difficult to manage. Stored procedures work great when you have several queries that are required to run in sequence (and usually offer better performance in such cases), but may end up a tad overkill from a maintenance perspective when you have a few isolated queries here and there. -marc --- George Sexton <[EMAIL PROTECTED]> wrote: > Generally speaking, if you were using most SQL > databases the approach would > be to use a stored procedure. The stored procedure > compilation saves the > various query plans as part of the data of the > stored procedure. I doubt > MySQL does it, but you could ask on their > development list. > > George Sexton > MH Software, Inc. > http://www.mhsoftware.com/ > Voice: 303 438 9585 > > > > -----Original Message----- > > From: Khawaja Shams [mailto:[EMAIL PROTECTED] > > Sent: Thursday, January 12, 2006 2:19 PM > > To: Tomcat Users List > > Subject: PreparedStatement w/ Connection Pooling > > > > Hello all, > > I am working on a web application that will > manage > > assignments/students/classes for the teachers at a > school. > > Most of my pages > > in the application need to access our mysql > database very > > frequently. For > > this and for other security reason, I would like > to use > > preparedStatements. > > However, from what I understand, preparedStatement > are > > prepared on top of a > > connection. If I am using connection pooling, > this seems to > > be useless for > > performance as I would be preparing a statement > each time I get a > > connection. Is there any way to get around this? > Is there > > such thing as a > > preparedStatement pool? Would you recommend > implementing this > > on my own? > > Thanks for your time and help. > > > > > > Best Regards, > > Khawaja Shams > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]