I received a couple of questions about this off-list, so I thought others might like to see it as well.
Here's a simple tester/example for the database pooling stuff I submitted earlier this week (see http://marc.theaimsgroup.com/?l=struts-dev&m=97967619230491).
A nifty feature of the PoolingDriver is that it allows you to add Connection and PreparedStatement pooling to "legacy" code that is using the regular java.sql.* API without changing that code at all. The PoolingDriver can be accessed via a regular JDBC URL, so that it's trivial to switch between a "native" database driver, and a PoolingDriver wrapped around it.
(Of course, you can also use the PoolingDataSource in much the same way as the GenericDataSource currently in the code base.)
We built this pooling library to work around some problems we had discovered with the WebLogic connection pooling support, and took the opportunity to add PreparedStatement pooling as well, which dramatically reduces the parse rate in the database, and is awkward to do if it's not built into the Connection pooling mechanism. For our system, the net result was a much healthier database, and much better round-trip times. The live system experience traffic peaks around 30-40 hits per second (and each hit requires multiple database interactions), so the pooling mechanism seems to respond to high concurrency pretty well. (As I mentioned before, I made some changes as I repackaged this for struts, so there may be some minor problems here or there.)
To run the test class, use:
java org.apache.struts.sql.PoolingDriverTest <connect-string> <query> <bindvalue> [<numthreads>] [<delay>]
where
* <connect-string> is the JDBC URI such as "jdbc:apache:struts:pool:<pool-name>" or "jdbc:oracle:thin:scott/tiger@<host>:1521:<sid>"
* <query> is a SQL query parameterized by a single '?', such as "SELECT * FROM DUAL WHERE DUMMY = ?"
* <bindvalue> is the value to bind to the '?' as a String
* <numthreads> is the number of threads to run (defaults to 7)
* <delay> is the max number of milliseconds that each thread should wait before borrowing and returning a connection (defaults to 1000)
To test a native database driver, simply set the appropriate <connect-string> (and probably register your driver via the jdbc.drivers property). To test the PoolingDriver, you'll need to set some of the driver's properties as described in the javadoc comments for PoolingDriver.
Here's a fairly minimal example, which is using a PoolingDriver wrapped around an Oracle thin driver:
java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver:org.apache.struts.sql.PoolingDriver \
-Dorg.apache.struts.sql.pool.testpool.connect-uri=jdbc:oracle:thin:[EMAIL PROTECTED]:1521:mysid \
org.apache.struts.sql.PoolingDriverTest jdbc:apache:struts:pool:testpool "SELECT * FROM DUAL WHERE DUMMY = ?" X
Play around with the various properties to see the impact they have on the performance of the pool. (Note that if you're comparing a pooled versus non-pooled configuration that the non-pooled one doesn't have to wait for connections to become available, it simply creates a new one, so if the number of threads is much larger than the number of connections in the pool, the non-pooling configuration will generally work faster, although the stress on the database is much greater.)
- rlw
PoolingDriverTest.java