我将 Hikari 与 SQL Server 2016 和 tomcat lib 文件夹中的 sqljdbc4-2.0.jar 一起使用。

我对数据库资源的配置如下:

<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" />

我的数据源配置如下:

@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)));
    }

我如何知道准备语句缓存是否适用于不同的连接？

我在 hibernate v4.3.10.Final 中使用 spring 容器管理的事务。

此外，要使缓存正常工作，我是否需要启用二级缓存？

最佳答案

HikariCP 实际上不支持PreparedStatement caching

others offer PreparedStatement caching. HikariCP does not. Why?

它被认为是错误的实现

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

解释:

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.

如果你接受它，你不应该尝试\期望缓存PreparedStatement

拒绝的话可以用C3P0作为连接池

关于Second level cache in hibernate ，它大多没有在连接池中定义，而是使用相关的连接提供者:

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

关于java - 我怎么知道准备好的语句是否被缓存？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56295549/