I am guessing the main attribute to change is the following: poolPreparedStatements="true"
Suppose that this is set to true, how do I access these pooled statement? My setup is exactly as you described, so this modification seems minor, but I would appreciate guidance on how to get a hold of these pooled prepared statement. Thanks for your help. Sincerely, Khawaja Shams On 1/12/06, Marc Richards <[EMAIL PROTECTED]> wrote: > > 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] > >
