Java – Oracle jdbc driver statement cache and bonecp statement cache?

I am using the Oracle jdbc driver and evaluating bonecp

I asked myself if I should use one or another for statement caching What's your opinion? What are the advantages or disadvantages of each approach?

Solution

As the author of bonecp, I can fill in my section:

>If you forget to close the statement correctly, using pool caching can provide you with a stack trace If you are using a hibernate / spring JDBC template or some other managed connection, this is irrelevant because it will always be closed for you. > The statement cache is associated with each connection, so if you have 100 connections and continue to execute the same statement each time, it will take some time until each connection fills its cache If the database supports it, the driver may make some special adjustments to prepare this statement only once, but this is not in the JDBC specification. Therefore, if possible, the connection pool will not have any such tools to optimize it On the other hand, you can tell the pool to provide you with connections in LIFO mode, which will greatly improve your chances of accessing the hot cache. > There should not be much difference in performance, because eventually they all try to reuse a statement However, there are several drivers that use blind methods to synchronize at the method level, and in bonecp, I always try to use as fine locking as possible, so theoretically this should provide greater scalability

Conclusion: the two should be roughly the same - if not, it may be a design error somewhere