Was exploring the HikariCP connection pooling library, currently in an application we use Apache DBCP2 for providing a connection pool,which allows setting the preparedstatement cache at connection pooling level by specifiying these properties:
<property name="poolPreparedStatements" value="true"/>
<property name="maxOpenPreparedStatements" value="20"/>
But HikariCP clearly mentions in the wiki, that such feature is not supported in the library and instead relies on the corresponding jdbc driver to setup the cache for preparedstatement.
Since connection pools will be shared across threads I think connection level cache for preparedstatements would be the way to go,I am not sure of the behavior of the cache at jdbcdriver level, if it does locking of some sort for the preparedstatement,causing some contention?
Any suggestions on which would be way to go if the application would need to handle high volumes of queries as part of a routine which will be executed daily?
Please note that PreparedStatement is cached at connection level, when using a connection pool(dbcp2 in this case), connections can be created and closed rapidly due to eviction,idle timeout operations based on the settings.
Thus in order to enable proper caching of preparedStatement I had to set:
<property name="poolPreparedStatements" value="true"/>
<property name="maxOpenPreparedStatements" value="20"/>
Before setting these, even though I tried to use a preparedStatement(via JDBCTemplate) the database would hardparse every query when tested under load of 8 threads with 2 queries over the same table with 10000 rows.
For HikariCP I didn't get a chance to check out the behavior.