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:
<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?
This question is related to
spring
jdbc
spring-jdbc
jdbctemplate
hikaricp
you need to write this structure on your bean configuration (this is your datasource):
<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:
<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
Properties file (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=
May this also can help using configuration file like java class way.
@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;
}
I found it in http://www.baeldung.com/hikaricp and it works.
Your pom.xml
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.6.3</version>
</dependency>
Your data.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.driverClassName=org.postgresql.Driver
jdbc.dialect=org.hibernate.dialect.PostgreSQL94Dialect
jdbc.databaseurl=jdbc:postgresql://localhost:5432/dev_db
jdbc.username=dev
jdbc.password=dev
Using XML configuration, your data source should look something like this:
<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 HikariConfig
bean altogether and use an approach like the one mentioned here
I have recently migrated from C3P0
to HikariCP
in a Spring and Hibernate based project and it was not as easy as I had imagined and here I am sharing my findings.
For Spring Boot
see my answer here
I have the following setup
Some of the below configs are similar to some of the answers above but, there are differences.
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'
}
In order to get Spring & Hibernate to make use of Hikari Connection pool, you need to define the HikariDataSource
and feed it into sessionFactory
bean as shown below.
<!-- 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 logback
and set the level
to DEBUG
to see Hikari
Connection Pool start up.
<!-- Keep additivity=false to avoid duplicate lines -->
<logger additivity="false" name="com.zaxxer.hikari">
<level value="debug"/>
<!-- Your appenders goes here -->
</logger>
Via application.properties
in Spring Boot
debug=true
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
Using 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 HikariCP
pool configs in order to get the performance that it promises.
my test java config (for 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;
}
for DB2, please try below configuration.
<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>
This last error is caused by the library SLF4J not being found. HikariCP has two dependencies: slf4j and javassist. BTW, HikariDataSource does have a default constructor and does not need HikariConfig, see this link. So that was never the problem.
You can create a datasource bean in servlet context as:
<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>
Source: Stackoverflow.com