Does Hikari CP support PreparedStatements cache?

Question

I saw on this stackoverflow answer(https://stackoverflow.com/a/56336447/9098559) that hikari doesn't support PreparedStatement cache.

I also saw it in the Hikari CP docs.

Many connection pools, including Apache DBCP, Vibur, c3p0 and others offer PreparedStatement caching. HikariCP does not. Why?

But, prepared statement cache is set in the sample code of the Hikari cp document. (https://github.com/brettwooldridge/HikariCP#rocket-initialization)

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/simpsons");
config.setUsername("bart");
config.setPassword("51mp50n");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

HikariDataSource ds = new HikariDataSource(config);

Hikari said it doesn't support cache, but why can I configure cache? What does cachePrepStmts mean above?

Answer 1

Many connection pools, including Apache DBCP, Vibur, c3p0 and others offer PreparedStatement caching. HikariCP does not. Why?

Hikari itself doesn't support/provide the cache, it delegates that to the underlying datasource in use, in this case the MySQL one. Those properties are directly being set on the underlying MySQL datasource to do the caching. Those settings won't work on the Postgres one for instance (they have different names there). Hikari is "just" a connection pool, nothing more, nothing less everything else it delegates to the underlying used datasource/jdbc driver.

Which, if you read the documentation is also what is explained in there.

At the connection pool layer PreparedStatements can only be cached per connection. If your application has 250 commonly executed queries and a pool of 20 connections you are asking your database to hold on to 5000 query execution plans -- and similarly the pool must cache this many PreparedStatements and their related graph of objects.

Most major database JDBC drivers already have a Statement cache that can be configured, including PostgreSQL, Oracle, Derby, MySQL, DB2, and many others. JDBC drivers are in a unique position to exploit database specific features, and nearly all of the caching implementations are capable of sharing execution plans across connections. This means that instead of 5000 statements in memory and associated execution plans, your 250 commonly executed queries result in exactly 250 execution plans in the database. Clever implementations do not even retain PreparedStatement objects in memory at the driver-level but instead merely attach new instances to existing plan IDs.

This is the section from the documentation, the highlighted section explains exactly, why Hikari itself doesn't provide the caching but rather delegates it to the driver in use.