I need to write that would return a PoolingConnection with the ability to pool the prepared statements. I will have to the apache open source project commons-pool and common-dbcp for the same purpose. I would like to see some code samples from some one who has done something similar.
PreparedStatements are designed as an interface to pooled statements on the database - pooling them in Java code would be a pointless exercise!
When you pass a PreparedStatement to the database, it matches the PS to a previously run PS by the String value and reuses the precompiled and optimised database statement. One thing to be aware is that the String matching may be case sensitive, but otherwise 99% of the cost cutting is done by the database and all you'd be doing iis creating a memory overhead.
Dave [ October 28, 2004: Message edited by: David O'Meara ]
Pooling Statement is good, but Pooling PreparedStatement shouldn't be a good idea. Maybe your PreparedStatement object will hold some opening tables before call execute method. Those opening tables will result in sql failure from other connections, which requires exclusive table lock.
Originally posted by Saskia de Jong: Plans are bound to connections, and the pool might just give you a random connection back. I think DBCP has some special support for caching prepared statements.
Fair point, but if PreparedStatements are bound to a Connection (and I'm not convinced of this, and in any case it may be vendor specific) hpw many actual connections are active in your application?
Keeping in mind that the numbers below are specific to the app I work on and may not be transferable...
We use a Connection pool with default of two Connections for development and this works fine. In UAT we have ten. Ten Database connections in a pool can service a huge number of queries. So then a database intensive application will scroll through these connections fairly quickly, and eventually each Connection will have a cache of the statement.
Again I'll say you're not wrong, but unless it causes a problem I wouldn't worry about it
Originally posted by David O'Meara: When you pass a PreparedStatement to the database, it matches the PS to a previously run PS by the String value and reuses the precompiled and optimised database statement.
Er, um.. close. In databases that have execution plan optimizations the SQL string, prepared or raw dynamic, will be matched to an execution plan. The exact dynamic string or unbound prepared statement string (ignoring potential bind value probing) will determine the plan used. However, there are still client-side resources for the prepared statement needed to manage the bind item communication. That is what you actually cache in Java for pools that cache prepared statements (e.g. Weblogic). Performance issues discussed here.