如何使用 Spring 为 HikariCP 设置数据源？

How to set up datasource with Spring for HikariCP?

提问by Abhinab Kanrar

Hi I'm trying to use HikariCP with Spring for connection pool. I'm using jdbcTempLate and JdbcdaoSupport.
This is my spring configuration file for datasource:

嗨，我正在尝试将 HikariCP 与 Spring 一起用于连接池。我正在使用 jdbcTempLate 和 JdbcdaoSupport。
这是我的数据源的 spring 配置文件：

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
    <property name="dataSourceClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="dataSource.url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
    <property name="dataSource.user" value="username"/>
    <property name="dataSource.password" value="password"/>
</bean>

But unfortunately the following error message is generating:

但不幸的是，正在生成以下错误消息：

Cannot resolve reference to bean 'dataSource' while setting bean property 'dataSource'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ServletContext resource [/WEB-INF/dispatcher-servlet.xml]: Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.zaxxer.hikari.HikariDataSource]: No default constructor found; nested exception is java.lang.NoSuchMethodException: com.zaxxer.hikari.HikariDataSource.<init>()

Can anyone please tell me how to solve this issue?

谁能告诉我如何解决这个问题？

回答by bpedroso

you need to write this structure on your bean configuration (this is your datasource):

您需要在 bean 配置中编写此结构（这是您的数据源）：

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="poolName" value="springHikariCP" />
    <property name="connectionTestQuery" value="SELECT 1" />
    <property name="dataSourceClassName" value="${hibernate.dataSourceClassName}" />
    <property name="maximumPoolSize" value="${hibernate.hikari.maximumPoolSize}" />
    <property name="idleTimeout" value="${hibernate.hikari.idleTimeout}" />

    <property name="dataSourceProperties">
        <props>
            <prop key="url">${dataSource.url}</prop>
            <prop key="user">${dataSource.username}</prop>
            <prop key="password">${dataSource.password}</prop>
        </props>
    </property>
</bean>

<!-- HikariCP configuration -->
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

This is my example and it is working. You just need to put your properties on hibernate.properties and set it before:

这是我的例子，它正在工作。你只需要把你的属性放在 hibernate.properties 上并在之前设置它：

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="locations">
        <list>
            <value>classpath:hibernate.properties</value>
        </list>
    </property>
</bean>

Obs.: the versions are
log4j: 1.2.16
springframework: 3.1.4.RELEASE
HikariCP: 1.4.0

观察：版本是
log4j: 1.2.16
springframework: 3.1.4.RELEASE
HikariCP: 1.4.0

Properties file (hibernate.properties):

属性文件（hibernate.properties）：

hibernate.dataSourceClassName=oracle.jdbc.pool.OracleDataSource
hibernate.hikari.maximumPoolSize=10
hibernate.hikari.idleTimeout=30000
dataSource.url=jdbc:oracle:thin:@localhost:1521:xe
dataSource.username=admin
dataSource.password=

回答by Yura

my test java config (for MySql)

我的测试 java 配置（用于 MySql）

@Bean(destroyMethod = "close")
public DataSource dataSource(){
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setDriverClassName("com.mysql.jdbc.Driver");
    hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/spring-test"); 
    hikariConfig.setUsername("root");
    hikariConfig.setPassword("admin");

    hikariConfig.setMaximumPoolSize(5);
    hikariConfig.setConnectionTestQuery("SELECT 1");
    hikariConfig.setPoolName("springHikariCP");

    hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
    hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
    hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
    hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

    HikariDataSource dataSource = new HikariDataSource(hikariConfig);

    return dataSource;
}

回答by Shahid Yousuf

You can create a datasource bean in servlet context as:

您可以在 servlet 上下文中创建一个数据源 bean，如下所示：

<beans:bean id="dataSource"
    class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <beans:property name="dataSourceClassName"
        value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />
    <beans:property name="maximumPoolSize" value="5" />
    <beans:property name="maxLifetime" value="30000" />
    <beans:property name="idleTimeout" value="30000" />
    <beans:property name="dataSourceProperties">
        <beans:props>
            <beans:prop key="url">jdbc:mysql://localhost:3306/exampledb</beans:prop>
            <beans:prop key="user">root</beans:prop>
            <beans:prop key="password"></beans:prop>
            <beans:prop key="prepStmtCacheSize">250</beans:prop>
            <beans:prop key="prepStmtCacheSqlLimit">2048</beans:prop>
            <beans:prop key="cachePrepStmts">true</beans:prop>
            <beans:prop key="useServerPrepStmts">true</beans:prop>
        </beans:props>
    </beans:property>
</beans:bean>

回答by Raf

I have recently migrated from C3P0to HikariCPin a Spring and Hibernate based project and it was not as easy as I had imagined and here I am sharing my findings.

我最近在一个基于 Spring 和 Hibernate 的项目中从 迁移C3P0到HikariCP，这并不像我想象的那么容易，在这里我将分享我的发现。

For Spring Bootsee my answer here

在这里Spring Boot看到我的答案

I have the following setup

我有以下设置

• Spring 4.3.8+
• Hiberante 4.3.8+
• Gradle 2.x
• PostgreSQL 9.5

• 春天 4.3.8+
• 休眠 4.3.8+
• 摇篮 2.x
• PostgreSQL 9.5

Some of the below configs are similar to some of the answers above but, there are differences.

下面的一些配置与上面的一些答案相似，但存在差异。

Gradle stuff

摇篮的东西

In order to pull in the right jars, I needed to pull in the following jars

为了拉入正确的罐子，我需要拉入以下罐子

//latest driver because *brettw* see https://github.com/pgjdbc/pgjdbc/pull/849
compile 'org.postgresql:postgresql:42.2.0'
compile('com.zaxxer:HikariCP:2.7.6') {
    //they are pulled in separately elsewhere
    exclude group: 'org.hibernate', module: 'hibernate-core'
}

// Recommended to use HikariCPConnectionProvider by Hibernate in 4.3.6+    
compile('org.hibernate:hibernate-hikaricp:4.3.8.Final') {
        //they are pulled in separately elsewhere, to avoid version conflicts
        exclude group: 'org.hibernate', module: 'hibernate-core'
        exclude group: 'com.zaxxer', module: 'HikariCP'
}

// Needed for HikariCP logging if you use log4j
compile('org.slf4j:slf4j-simple:1.7.25')  
compile('org.slf4j:slf4j-log4j12:1.7.25') {
    //log4j pulled in separately, exclude to avoid version conflict
    exclude group: 'log4j', module: 'log4j'
}

Spring/Hibernate based configs

基于 Spring/Hibernate 的配置

In order to get Spring & Hibernate to make use of Hikari Connection pool, you need to define the HikariDataSourceand feed it into sessionFactorybean as shown below.

为了让 Spring & Hibernate 能够使用 Hikari 连接池，您需要定义HikariDataSource并将其输入到sessionFactorybean 中，如下所示。

<!-- HikariCP Database bean -->
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

<!-- HikariConfig config that is fed to above dataSource -->
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
        <property name="poolName" value="SpringHikariPool" />
        <property name="dataSourceClassName" value="org.postgresql.ds.PGSimpleDataSource" />
        <property name="maximumPoolSize" value="20" />
        <property name="idleTimeout" value="30000" />

        <property name="dataSourceProperties">
            <props>
                <prop key="serverName">localhost</prop>
                <prop key="portNumber">5432</prop>
                <prop key="databaseName">dbname</prop>
                <prop key="user">dbuser</prop>
                <prop key="password">dbpassword</prop>
            </props>
        </property>
</bean>

<bean class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" id="sessionFactory">
        <!-- Your Hikari dataSource below -->
        <property name="dataSource" ref="dataSource"/>
        <!-- your other configs go here -->
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</prop>
                <!-- Remaining props goes here -->
            </props>
        </property>
 </bean>

Once the above are setup then, you need to add an entry to your log4j or logbackand set the levelto DEBUGto see HikariConnection Pool start up.

一旦上述的设置，然后，你需要一个条目添加到您log4j or logback，并设置level到DEBUG看到Hikari连接池启动。

Log4j1.2

Log4j1.2

<!-- Keep additivity=false to avoid duplicate lines -->
<logger additivity="false" name="com.zaxxer.hikari">
    <level value="debug"/>
    <!-- Your appenders goes here -->
</logger>

Logback

登录

Via application.propertiesin Spring Boot

通过application.properties输入Spring Boot

debug=true
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG 

Using logback.xml

使用 logback.xml

<logger name="com.zaxxer.hikari" level="DEBUG" additivity="false">
    <appender-ref ref="STDOUT" />
</logger>

With the above you should be all good to go! Obviously you need to customize the HikariCPpool configs in order to get the performance that it promises.

有了以上内容，您应该一切顺利！显然，您需要自定义HikariCP池配置以获得它所承诺的性能。

回答by geoand

Using XML configuration, your data source should look something like this:

使用 XML 配置，您的数据源应如下所示：

    <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">  
      <property name="dataSourceProperties" >
        <props>
            <prop key="dataSource.url">jdbc:oracle:thin:@localhost:1521:XE</prop>
            <prop key="dataSource.user">username</prop>
            <prop key="dataSource.password">password</prop>
        </props>
      </property>  
      <property name="dataSourceClassName"   
                value="oracle.jdbc.driver.OracleDriver" />  
    </bean>  

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">  
          <constructor-arg ref="hikariConfig" />  
    </bean>  

Or you could skip the HikariConfigbean altogether and use an approach like the one mentioned here

或者你可以HikariConfig完全跳过bean 并使用一种像这里提到的方法

回答by brettw

This last error is caused by the library SLF4J not being found. HikariCP has two dependencies: slf4j and javassist. BTW, HikariDataSource doeshave a default constructor and does not need HikariConfig, see this link. So that was never the problem.

最后一个错误是由于找不到库 SLF4J 引起的。HikariCP 有两个依赖项：slf4j 和 javassist。顺便说一句，HikariDataSource确实有一个默认构造函数并且不需要 HikariConfig，请参阅此链接。所以这从来都不是问题。

回答by Николай Василецкий

I found it in http://www.baeldung.com/hikaricpand it works.

我在http://www.baeldung.com/hikaricp 中找到了它并且它有效。

Your pom.xml

你的 pom.xml

<dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.6.3</version>
        </dependency>

Your data.xml

你的数据.xml

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.databaseurl}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
      p:dataSource-ref="dataSource"
/>

Your jdbc.properties

你的 jdbc.properties

jdbc.driverClassName=org.postgresql.Driver
jdbc.dialect=org.hibernate.dialect.PostgreSQL94Dialect
jdbc.databaseurl=jdbc:postgresql://localhost:5432/dev_db
jdbc.username=dev
jdbc.password=dev

回答by stanicmail

for DB2, please try below configuration.

对于 DB2，请尝试以下配置。

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="poolName" value="springHikariCP" />
    <property name="dataSourceClassName" value="com.ibm.db2.jcc.DB2SimpleDataSource"/>

    <property name="maximumPoolSize" value="${db.maxTotal}" />
    <property name="dataSourceProperties">
        <props>
            <prop key="driverType">4</prop>
            <prop key="serverName">192.168.xxx.xxx</prop>
            <prop key="databaseName">dbname</prop>
            <prop key="portNumber">50000</prop>
            <prop key="user">db2inst1</prop>
            <prop key="password">password</prop>
        </props>
    </property>

    <property name="jdbcUrl" value="${db.url}" />
    <property name="username" value="${db.username}" />
    <property name="password" value="${db.password}" />
</bean>
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

回答by itro

May this also can help using configuration file like java class way.

可能这也可以帮助使用像 java 类方式的配置文件。

@Configuration
@PropertySource("classpath:application.properties")
public class DataSourceConfig {
    @Autowired
    JdbcConfigProperties jdbc;


    @Bean(name = "hikariDataSource")
    public DataSource hikariDataSource() {
        HikariConfig config = new HikariConfig();
        HikariDataSource dataSource;

        config.setJdbcUrl(jdbc.getUrl());
        config.setUsername(jdbc.getUser());
        config.setPassword(jdbc.getPassword());
        // optional: Property setting depends on database vendor
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        dataSource = new HikariDataSource(config);

        return dataSource;
    }
}

How to use it:

如何使用它：

@Component
public class Car implements Runnable {
    private static final Logger logger = LoggerFactory.getLogger(AptSommering.class);


    @Autowired
    @Qualifier("hikariDataSource")
    private DataSource hikariDataSource;


}