How do I know if the prepared statements are cached?

Question

I am using Hikari with SQL Server 2016 and sqljdbc4-2.0.jar in the tomcat lib folder.

My configuration for db resource is as follows:

<Resource name="jdbc/SQLServerDS" auth="Container" type="javax.sql.DataSource"
              username="uname"
              password="pwd"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://server:port;DatabaseName=dbName"
              maxActive="20"
              maxIdle="10"
              validationQuery="select 1" />

My Datasource configuration is as follows:

@Bean(name = "dataSource")
    public DataSource getDataSource() throws NamingException {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setDataSourceJNDI("java:comp/env/jdbc/SQLServerDS");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");
        config.addDataSourceProperty("cacheResultSetMetadata", "true");
        config.addDataSourceProperty("useLocalSessionState", "true");
        config.addDataSourceProperty("cacheServerConfiguration", "true");
        config.addDataSourceProperty("elideSetAutoCommits", "true");
        config.addDataSourceProperty("maintainTimeStats", "false");
        return new TransactionAwareDataSourceProxy(
                new LazyConnectionDataSourceProxy(new HikariDataSource(config)));
    }

How do I know if the preparedstatement caching is working for different connections?

I am using spring container managed transactions with hibernate v4.3.10.Final.

Also, for the caching to work, do I need to have second-level cache enabled?

Answer 1

HikariCP actually doesn't support PreparedStatement caching

others offer PreparedStatement caching. HikariCP does not. Why?

It's considered wrong implementation

Using a statement cache at the pooling layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.

Explanation:

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.

If you accept it, you shouldn't try\expect to cache PreparedStatement

If you reject it, you can use C3P0 as connection pool

About Second level cache in hibernate, it's mostly not defined in connection pool, but use relevant connection provider:

HikariCP now has a ConnectionProvider for Hibernate 4.x called HikariConnectionProvider

In order to use the HikariConnectionProvider in Hibernate 4.x add the following property to your hibernate.properties configuration file:

hibernate.connection.provider_class=com.zaxxer.hikari.hibernate.HikariConnectionProvider

As of Hibernate 4.3.6 there is an official ConnectionProvider class from Hibernate, which should be used instead of the HikariCP implementation. The class is called org.hibernate.hikaricp.internal.HikariCPConnectionProvider